asked
Group By Multiple Columns Into Single Column?
Hi there, Is it possible to query a table to group by mulltiple columns with those multiple column names as the grouped values? For example, from the following data: CREATE TABLE T1( ID INT IDENTITY(1,1), BLUE BIT, GREEN BIT, RED BIT) INSERT INTO T1 SELECT 1,1,1 UNION ALL SELECT 0,1,1 UNION ALL SELECT 1,0,0 UNION ALL SELECT 1,1,0 UNION ALL SELECT 1,0,1 UNION ALL SELECT 0,0,1 UNION ALL SELECT 1,1,1 UNION ALL SELECT 0,0,1 UNION ALL SELECT 0,0,0 UNION ALL SELECT 1,1,0 UNION ALL SELECT 1,1,1 UNION ALL SELECT 0,0,0 I am looking for a count answer similar to this: COLOUR COUNT --------------- BLUE 7 GREEN 6 RED 7 Any pointers greatly appreciated. TIA
I think UNPIVOT should do what you want. SELECT Colours, SUM(CAST(Num AS INT)) AS Count FROM (SELECT Id, Red, Green, Blue FROM t1) p UNPIVOT(Num FOR Colours IN(Red, Green, Blue)) AS unpvt GROUP BY Colours More info can be seen in the example at the bottom of this page:
https://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx
18 People are following this question.
