Click here to Skip to main content
15,357,967 members
Please Sign up or sign in to vote.
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
Updated 6-Oct-21 23:10pm
v2
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"

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
   
v2
Comments
Ayush Pathak 2021 7-Oct-21 4:25am
   
Is there a way so that I need not to write the Month from Jan to Dec
CHill60 7-Oct-21 6:25am
   
Yes, copy the query from my solution.
For your other question - see my updated solution
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.
   
v2
Comments
Ayush Pathak 2021 7-Oct-21 6:01am
   
It's printing till march I want till December.
Is it possible to output till Dec?
Richard Deeming 7-Oct-21 6:02am
   
If there are months missing in the output, then there is no data for those months in the input.

If you require output for all months, then you cannot use a dynamic pivot.
Ayush Pathak 2021 7-Oct-21 6:05am
   
Okay, Okay thanks for your help

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900