July 4, 2016 at 3:12 pm
Hi everyone, I have the following table:
SELECT
[Period],
[Debnr],
[Summary],
[Qty],
[Amount]
FROM [CustomReports].[dbo].[RI];
it returns the following:
Period Debnr Summary Qty Amount
[highlight="#A0A0EE"]01 1001 Article A 2 205
01 1001 Article B 1 100
01 1001 Article C 1 100[/highlight]
02 1002 Article A 3 3000
02 1002 Article D 1 100
[highlight="#9FFFFF"]03 1003 Article F 4 400
03 1003 Article A 1 100
03 1003 Article B 5 100
03 1003 Article C 1 100[/highlight]
I need to get the following output as displayed below.
The rows to columns and do a sum on the amount, so I get the period and all the articles and their quantity with the total amount on 1 row. The Summary could go up to about 14.
Period Debnr Summary1 Qty1 Summary2 Qty2 Summary3 Qty3 Summary4 Qty4 Total
[highlight="#A0A0EE"]01 1001 Article A 2 Article B 1 Article C 1 405[/highlight]
02 1002 Article A 3 Article D 1 3100
[highlight="#9FFFFF"]03 1003 Article F 4 Article A 1 Article B 5 Article C 1 700[/highlight]
I tried about everything but the closest I got is this, getting all the data in the output just fails or get skipped:
Debnr Summary1 Summary2 Summary3 Summary4
[highlight="#A0A0EE"]1001 Article A Article B Article C[/highlight]
1002 Article A Article D
[highlight="#9FFFFF"]1003 Article F Article A Article B Article C[/highlight]
Edit: I added the code snippet below to show the last code I got stuck with: I started with Pivot but that didn't get me really far.
Then I tried the method below, but when adding Qty and Period it always failed or gave the wrong result.
WITH CTE
AS ( SELECT Debnr,
Summary, Qty, Amount,
ROW_NUMBER() OVER ( PARTITION BY Debnr ORDER BY Period ) AS rowno
FROM [CustomReports].[dbo].[RI];
)
SELECT Debnr,
MAX(CASE WHEN rowno = 1 THEN Summary
END) AS Summary1,
MAX(CASE WHEN rowno = 2 THEN Summary
END) AS Summary2,
MAX(CASE WHEN rowno = 3 THEN Summary
END) AS Summary3, ..... repeating till rowno=14/Summary14
SUM(CTE.Amount) AS Total
FROM CTE
GROUP BY Debnr
Any help is much appreciated.
July 5, 2016 at 1:00 am
For Period = 1 and Debnr = 1001, how do you decide that "Article A" should come under Summary1 and not Summary2 or something else?
Do you have any other column( say a date column or identity column ) in the table that decides the order?
If you have any such column you can replace the reference to column "Period" in ORDER BY Clause within ROW_NUMBER() with the appropriate column to get your desired results
SELECT Period, Debnr,
MAX( CASE WHEN RN = 1 THEN Summary ELSE NULL END ) AS Summary1,
SUM( CASE WHEN RN = 1 THEN Qty ELSE NULL END ) AS Qty1,
MAX( CASE WHEN RN = 2 THEN Summary ELSE NULL END ) AS Summary2,
SUM( CASE WHEN RN = 2 THEN Qty ELSE NULL END ) AS Qty2,
MAX( CASE WHEN RN = 3 THEN Summary ELSE NULL END ) AS Summary3,
SUM( CASE WHEN RN = 3 THEN Qty ELSE NULL END ) AS Qty3,
MAX( CASE WHEN RN = 4 THEN Summary ELSE NULL END ) AS Summary4,
SUM( CASE WHEN RN = 4 THEN Qty ELSE NULL END ) AS Qty4,
MAX( CASE WHEN RN = 5 THEN Summary ELSE NULL END ) AS Summary5,
SUM( CASE WHEN RN = 5 THEN Qty ELSE NULL END ) AS Qty5,
MAX( CASE WHEN RN = 6 THEN Summary ELSE NULL END ) AS Summary6,
SUM( CASE WHEN RN = 6 THEN Qty ELSE NULL END ) AS Qty6,
MAX( CASE WHEN RN = 7 THEN Summary ELSE NULL END ) AS Summary7,
SUM( CASE WHEN RN = 7 THEN Qty ELSE NULL END ) AS Qty7,
MAX( CASE WHEN RN = 8 THEN Summary ELSE NULL END ) AS Summary8,
SUM( CASE WHEN RN = 8 THEN Qty ELSE NULL END ) AS Qty8,
MAX( CASE WHEN RN = 9 THEN Summary ELSE NULL END ) AS Summary9,
SUM( CASE WHEN RN = 9 THEN Qty ELSE NULL END ) AS Qty9,
MAX( CASE WHEN RN = 10 THEN Summary ELSE NULL END ) AS Summary10,
SUM( CASE WHEN RN = 10 THEN Qty ELSE NULL END ) AS Qty10,
MAX( CASE WHEN RN = 11 THEN Summary ELSE NULL END ) AS Summary11,
SUM( CASE WHEN RN = 11 THEN Qty ELSE NULL END ) AS Qty11,
MAX( CASE WHEN RN = 12 THEN Summary ELSE NULL END ) AS Summary12,
SUM( CASE WHEN RN = 12 THEN Qty ELSE NULL END ) AS Qty12,
MAX( CASE WHEN RN = 13 THEN Summary ELSE NULL END ) AS Summary13,
SUM( CASE WHEN RN = 13 THEN Qty ELSE NULL END ) AS Qty13,
MAX( CASE WHEN RN = 14 THEN Summary ELSE NULL END ) AS Summary14,
SUM( CASE WHEN RN = 14 THEN Qty ELSE NULL END ) AS Qty14,
SUM( Amount ) AS Total
FROM (
SELECT ROW_NUMBER() OVER ( PARTITION BY Period, Debnr ORDER BY Period ) AS RN, *
FROM [CustomReports].[dbo].[RI]
) AS RI
GROUP BY Period, Debnr
For a detailed explanation on the technique used( CROSS TABS ), you can check the below mentioned links
http://www.sqlservercentral.com/articles/T-SQL/63681/
http://www.sqlservercentral.com/articles/Crosstab/65048/
Edit: Added links to articles on CROSS TABS
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
July 5, 2016 at 3:39 am
Excellent technique to learn from you.
July 5, 2016 at 4:30 pm
Thanks Kingston that works perfectly!
And it made me understand why I couldn't get the desired result.
How do you I decide that "Article A" should come under Summary1 and not Summary2 or something else?
Well that wasn't needed, also there isn't a column that decides that order. (but If the need arise I could make one and use your suggestion)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply