SQL Server Conditional Group

Question

Squirting my stackoverflow.com cherries!

I have a SQL Server 2008 table containing ten nchar (1) columns. I tried to write a stored procedure to query a table to get the counts grouped by any combination of ten columns. For example, let's say that I have these rows in a table:

| COL1 | COL2 | COL3 | COL4 | COL5 | COL6 | COL7 | COL8 | COL9 | COL10 +======+======+======+======+======+======+======+======+======+====== | T | P | 9 | C | ) | N | N | S | X | X | T | P | 9 | 7 | 0 | * | N | Q | X | X | T | P | I | B | ( | H | N | S | X | X | T | P | A | A | G | S | N | 6 | X | X 

I want to be able to group columns 1 and 3 and get:

 COUNT | COL1 | COL2 | COL3 | COL4 | COL5 | COL6 | COL7 | COL8 | COL9 | COL10 +=====+======+======+======+======+======+======+======+======+============= | 2 | T | - | 9 | - | - | - | - | - | - | - | 1 | T | - | A | - | - | - | - | - | - | - | 1 | T | - | I | - | - | - | - | - | - | - 

Or I want to be able to group columns 1, 2, and 8 and get:

 COUNT | COL1 | COL2 | COL3 | COL4 | COL5 | COL6 | COL7 | COL8 | COL9 | COL10 +=====+======+======+======+======+======+======+======+======+============= | 2 | T | P | - | - | - | - | - | S | - | - | 1 | T | P | - | - | - | - | - | 6 | - | - | 1 | T | P | - | - | - | - | - | Q | - | - 

I think I can dynamically create a request on the client side and do with it. I think that would be the easiest solution right now. But for future reference, is there an easy way to do this server-side without using dynamic SQL? Well, I always read that dynamic SQL is usually a bad idea. Would this be the preferred method in this case?

(Try not to cry too badly if I don’t follow the labels of stackoverflow.com. Please just indicate this and I will try to do better next time.)

Answer 1

 CREATE PROCEDURE GetGroups ( @Col1 bit, @Col2 bit, @Col3 bit, @Col4 bit, @Col5 bit, @Col6 bit, @Col7 bit, @Col8 bit, @Col9 bit, @Col10 bit ) AS SELECT COUNT = COUNT(*), CASE @col1 WHEN 1 THEN COL1 END COL1, CASE @col2 WHEN 1 THEN COL2 END COL2, CASE @col3 WHEN 1 THEN COL3 END COL3, CASE @col4 WHEN 1 THEN COL4 END COL4, CASE @col5 WHEN 1 THEN COL5 END COL5, CASE @col6 WHEN 1 THEN COL6 END COL6, CASE @col7 WHEN 1 THEN COL7 END COL7, CASE @col8 WHEN 1 THEN COL8 END COL8, CASE @col9 WHEN 1 THEN COL9 END COL9, CASE @col10 WHEN 1 THEN COL10 END COL10 FROM YourTable GROUP BY CASE @col1 WHEN 1 THEN COL1 END, CASE @col2 WHEN 1 THEN COL2 END, CASE @col3 WHEN 1 THEN COL3 END, CASE @col4 WHEN 1 THEN COL4 END, CASE @col5 WHEN 1 THEN COL5 END, CASE @col6 WHEN 1 THEN COL6 END, CASE @col7 WHEN 1 THEN COL7 END, CASE @col8 WHEN 1 THEN COL8 END, CASE @col9 WHEN 1 THEN COL9 END, CASE @col10 WHEN 1 THEN COL10 END 

---

UPDATE

By the way, since the resulting query is not built dynamically, it can be implemented as a table function:

 CREATE FUNCTION fnGetGroups ( @Col1 bit, @Col2 bit, @Col3 bit, @Col4 bit, @Col5 bit, @Col6 bit, @Col7 bit, @Col8 bit, @Col9 bit, @Col10 bit ) RETURNS TABLE AS RETURN ( SELECT … ) 

and call it instead of SP:

 CREATE PROCEDURE GetGroups ( @Col1 bit, @Col2 bit, @Col3 bit, @Col4 bit, @Col5 bit, @Col6 bit, @Col7 bit, @Col8 bit, @Col9 bit, @Col10 bit ) AS SELECT * FROM fnGetGroups(@Col1, @Col2, @Col3, @Col4, @Col5, @Col6, @Col7, @Col8, @Col9, @Col10) 

The fact is that TVF may be more convenient for use in various SQL scripts, while SP may be preferable for direct calling from an application.