October 21, 2016 at 3:13 am
hi lets say i have a results set like the following:
12-56-1011-20>21
829523763105622617316
the first line are categories, which were created with case statements eg
count(case when [order line] = 1 then 1 end) [1]
but i want it to be displayed like:
OrderLines Items
1 8295
2-5 23763
6-10 10562
11-20 2617
>21 316
thanks in advanced
October 21, 2016 at 3:19 am
You can use the PIVOT funtion to do this. The PIVOT is clearly explained here: http://www.databasejournal.com/features/mssql/converting-rows-to-columns-pivot-and-columns-to-rows-unpivot-in-sql-server.html
Over here https://www.simple-talk.com/sql/t-sql-programming/questions-about-pivoting-data-in-sql-server-you-were-too-shy-to-ask/ can also some nice explanation be found.
If you still have doubts/questions about how to solve your issue, please post DDL with a sample of the base-data. That makes it more easy for us to provide a complete solution to you.
October 21, 2016 at 8:02 am
Talvin Singh (10/21/2016)
the first line are categories, which were created with case statements egcount(case when [order line] = 1 then 1 end) [1]
Maybe I'm missing something, I'd have to see the entire query, but if you're currently creating columns using this method and want them to be rows, don't try to unpivot the pivoted data, wouldn't you just need a table that groups the lines together, something like:
DECLARE @linegroup TABLE (group_name varchar(5), min_line int, max_line int, sort tinyint);
INSERT INTO @linegroup
VALUES ('1',1,1,1), ('2-5',2,5,2), ('6-10',6,10,3), ('11-20',11,20,4), ('> 21', 21, 999999999,5);
SELECT lg.group_name AS OrderLines, COUNT(*)
FROM orders o
INNER JOIN @linegroup lg ON o.[order line] BETWEEN lg.min_line AND lg.max_line
GROUP BY lg.group_name, lg.sort
ORDER BY lg.sort
October 21, 2016 at 11:36 am
Chris Harshman (10/21/2016)
Talvin Singh (10/21/2016)
the first line are categories, which were created with case statements egcount(case when [order line] = 1 then 1 end) [1]
Maybe I'm missing something, I'd have to see the entire query, but if you're currently creating columns using this method and want them to be rows, don't try to unpivot the pivoted data, wouldn't you just need a table that groups the lines together, something like:
+1
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply