Is there any SQL query to get this table using dynamic SQL in SQL server

Question

0.00/5 (No votes)

See more:

id    |revenue|  RevenueMonth

1     |8000   | Jan
2     |9000   | Jan
3     |10000  | Feb
1     |7000   | Feb
1     |6000   | Mar



 id| Jan     Feb     Mar     Apr     May     Jun     Jul     Aug     Sep
 1 | 8000    7000    6000    NULL    NULL    NULL    NULL    NULL    NULL

 2   9000    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL

 3   NULL    10000   NULL    NULL    NULL    NULL    NULL    NULL    NULL

What I have tried:

I want the query in MS SQL server but this code is in MySQL

SET @sql = NULL;

SELECT 
    GROUP_CONCAT
      (DISTINCT
        CONCAT('SUM(IF(MONTH =''',
                 month,
                  ''', REVENUE,NULL)) AS ',
                 month,
                  '_Revenue')
      ) INTO @sql
FROM Department;
  
SET @sql = CONCAT('SELECT id, ', 
                  @sql, ' 
                  FROM Department
                  GROUP BY id
                  ORDER BY id');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Posted 6-Oct-21 19:22pm

Ayush Pathak 2021

Updated 6-Oct-21 23:10pm

v2

Add a Solution

Comments

CHill60 7-Oct-21 4:15am

By the way, that code produces an error "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DISTINCT CONCAT('SUM(IF(MONTH =''', month, ' at line 3"

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Accepted Answer · 2021-10-06T22:12:00

There no need for dynamic SQL just use a simple pivot (see Simple Way To Use Pivot In SQL Query[^] ) e.g.

SQL

select * from 
(
	select id, revenue, RevenueMonth
	from department
) src
PIVOT
(
	sum(revenue) for RevenueMonth in (Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec) 
) pvt

Edit after OP comment to solution 2:

Quote:
It's printing till march I want till December.
Is it possible to output till Dec?

Try this:

SQL

select isnull(Jan,0) as Jan,	isnull(Feb,0) as Feb,	isnull(Mar,0) as Mar,	isnull(Apr,0) as Apr,	isnull(May,0) as May,	isnull(Jun,0) as Jun,	isnull(Jul,0) as Jul,	isnull(Aug,0) as Aug,	isnull(Sep,0) as Sep,	isnull(Oct,0) as Oct,	isnull(Nov,0) as Nov,	isnull(Dec,0) as Dec

from 
(
	select id, revenue, RevenueMonth
	from department
) src
PIVOT
(
	sum(revenue) for RevenueMonth in (Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec) 
) pvt

Richard Deeming · Accepted Answer · 2021-10-06T23:10:00

Quote:
Is there a way so that I need not to write the Month from Jan to Dec

The month names are 48 characters, and are fixed. That's not really a lot to type out - especially since CHill60 has already typed them out for you!

However, if you need dynamic pivot columns, it's easy enough to do:

SQL

DECLARE @columns nvarchar(max);
DECLARE @sql nvarchar(max);

SELECT @columns = STUFF((SELECT DISTINCT N', ' + QUOTENAME(RevenueMonth) FROM YourTable FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), 1, 2, N'');

SET @sql = N'SELECT id, ' + @columns + N' FROM YourTable As s PIVOT (SUM(Revenue) FOR RevenueMonth IN (' + @columns + N')) As p ORDER BY id;';

EXEC sp_executesql @sql;

NB: You need to use QUOTENAME to ensure that the dynamic pivot column name is a valid SQL identifier.

NB2: Although you could sort the dynamic pivot columns, they would be sorted alphabetically - Apr, Aug, Dec, Feb, Jan, Jul, Jun, Mar, May, Nov, Oct, Sep. If you need them sorted in the correct month order, then don't use a dynamic pivot.

Is there any SQL query to get this table using dynamic SQL in SQL server

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0