question

David 2 1 avatar image
David 2 1 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
sql-server-2012group-bycount
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
mjharper avatar image
mjharper answered
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
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@mjharper thanks very much.
1 Like 1 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.