December 28, 2007 at 9:20 am
Hi,
I got stuck on this structure without a clue how to get it done:
I've got a result table:
IDNameMonthQuantityTotal
4AAAJan72055.5755
4AAAFeb4879.1744
4AAAMar185908.7851
5BBBJan1157.4884
6CCCFeb3926.216
7DDDJan162.5
7DDDMar92261.9469
8EEEMar93868.6958
Now I need to pivot this structure
to unique name/ID followed by Monthly break down:
IDNameJanQuantityJanTotalFebQuantityFebTotalMarQuantityMarTotal
4AAA………7………2055.5755………4………879.1744………18………5908.7851
5BBB………1………157.4884………0………0………………0………0
6CCC………0………0………………3………926.216………0………0
7DDD………1………62.5………………0………0………………9………2261.9469
8EEE………0………0………………0………0………………9………3868.6958
Any help appreciated
Kris
December 28, 2007 at 11:01 am
Kris, search Books Online for "cross-tab reports". The example is exactly what you need to do.
Greg
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
January 3, 2008 at 7:28 am
This works
Create table #pivot
(
[ID][INT],
[NAME][VARCHAR](10),
[MONTH][Varchar](10),
[QTY][INT],
[TOTAL][FLOAT],
)
INSERT INTO #Pivot
VALUES(4, 'AAA', 'Jan',7 ,2055.5755)
INSERT INTO #Pivot
VALUES(4, 'AAA' ,'Feb', 4, 879.1744)
INSERT INTO #Pivot
VALUES(4, 'AAA','Mar' ,18, 5908.7851)
INSERT INTO #Pivot
VALUES(5, 'BBB', 'Jan' ,1, 157.4884)
INSERT INTO #Pivot
VALUES(6, 'CCC', 'Feb', 3, 926.216)
INSERT INTO #Pivot
VALUES(7, 'DDD', 'Jan', 1, 62.5)
INSERT INTO #Pivot
VALUES(7, 'DDD', 'Mar', 9, 2261.9469)
INSERT INTO #Pivot
VALUES(8, 'EEE', 'Mar', 9 ,3868.6958)
SELECT [ID],
[NAME],
SUM( Qty * (1 - ABS(SIGN(CASE when [Month] ='Jan' then 1 else 0 end - 1)))) as [JAN QTY],
SUM( Total * (1 - ABS(SIGN(CASE when [Month] ='Jan' then 1 else 0 end - 1)))) as [JAN TOTAL],
SUM( Qty * (1 - ABS(SIGN(CASE when [Month] ='Feb' then 2 else 0 end - 2)))) as [FEB QTY],
SUM( Total * (1 - ABS(SIGN(CASE when [Month] ='Feb' then 2 else 0 end - 2)))) as [FEB TOTAL],
SUM( Qty * (1 - ABS(SIGN(CASE when [Month] ='Mar' then 3 else 0 end - 3)))) as [MAR QTY],
SUM( Total * (1 - ABS(SIGN(CASE when [Month] ='Mar' then 1 else 0 end - 1)))) as [MAR TOTAL]
--Etc etc
FROM #PIVOT
GROUP BY [ID],
[NAME]
DROP TABLE #PIVOT
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply