asked
Group by
I am trying to use the group by clause but I am not sure if this is the right approach, the field names I have are for each month ie select Jan,Feb,Mar from Monthly_Data Group by Jan,Feb,Mar. Each month has either a null value or has multiple department names so the group by doesn't do anything. I am trying to exclude the null values so that I only see the department names in each field. Any help would be great, thanks --edit-- How it looks Jan Feb Mar NULL NULL NULL NULL NULL WTI NULL NULL DM NULL NULL PR NULL CT NULL NULL PR NULL BRK NULL NULL How it should look Jan Feb Mar BRK CT WTI NULL PR DM NULL NULL PR Thanks
I think I know what you're getting at, but if you could supply a small sample of data and the expected / required output, that might help.
I was originally convinced there was an easy solution for this using window functions. Well, 30+ minutes later, I think I found a solution, but it wasn't easy. :) I used a combination of a Numbers (or Tally) table and ROW_NUMBER(). For my sample, I just made a quick CTE Numbers table with 625 rows. You could either shrink or expand that as needed. WITH Numbers (n) AS ( SELECT ROW_NUMBER() OVER (ORDER BY t01.n) FROM (VALUES (1),(1),(1),(1),(1)) t01(n) CROSS JOIN (VALUES (1),(1),(1),(1),(1)) t02(n) CROSS JOIN (VALUES (1),(1),(1),(1),(1)) t03(n) CROSS JOIN (VALUES (1),(1),(1),(1),(1)) t04(n) ) , Ordering AS ( SELECT Jan, JanOrder = ROW_NUMBER() OVER (ORDER BY d.Jan) , Feb, FebOrder = ROW_NUMBER() OVER (ORDER BY d.Feb) , Mar, MarOrder = ROW_NUMBER() OVER (ORDER BY d.Mar) FROM MyData d ) SELECT j1.Jan, f1.Feb, m1.Mar FROM Numbers N LEFT JOIN Ordering j1 ON j1.JanOrder = N.n LEFT JOIN Ordering f1 ON f1.FebOrder = N.n LEFT JOIN Ordering m1 ON m1.MarOrder = N.n WHERE j1.Jan is not null OR f1.Feb is not null OR m1.Mar is not null ;
You could treat each column separately, get rid of the nulls in the column and have a row number count. Then, put it all back together by doing an outer join on row number. SELECT A.JAN , B.FEB , C.MAR FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY (SELECT 100) ) [Col] , JAN FROM Monthly_Data WHERE JAN IS NOT NULL ) A FULL OUTER JOIN ( SELECT ROW_NUMBER() OVER ( ORDER BY (SELECT 100) ) [Col] , FEB FROM Monthly_Data WHERE FEB IS NOT NULL ) B ON A.col = B.col FULL OUTER JOIN ( SELECT ROW_NUMBER() OVER ( ORDER BY (SELECT 100) ) [Col] , FEB FROM Monthly_Data WHERE FEB IS NOT NULL ) C ON C.col = B.col
19 People are following this question.
