SQL statement to generate column values

Question

Please see the DDL below:

 create table Person (ID int, [Type] int)
 insert into Person values (1,1)
 insert into Person values (2,1)
 insert into Person values (3,2)
 insert into Person values (4,3)
 insert into Person values (5,4)
 insert into Person values (6,5)

I am looking for a result like this:

2 1 1 1 1

The following criteria generates this result:

There are 2 persons with a type of 1 (The first column value is: 2)
There is 1 person with a type of 2 (The second column value is: 1)
There is 1 person with a type of 3 (The third column value is: 1)
There is 1 person with a type of 4 (The forth column value is: 1)
There is 1 person with a type of 5 (The fifth column value is: 1)

Is type limited to 5 options or could it be n? if n then http://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query if limited then you don't have to use dynamic SQL and either a case below would work or a standard pivot. — xQbert, May 07 '15 at 13:50

score 6 · Answer 1 · answered May 07 '15 at 13:36

Use CASE to SUM different Type's

select sum(case when [Type] = 1 then 1 else 0 end),
       sum(case when [Type] = 2 then 1 else 0 end),
       sum(case when [Type] = 3 then 1 else 0 end),
       sum(case when [Type] = 4 then 1 else 0 end),
       sum(case when [Type] = 5 then 1 else 0 end)
from tablename

score 1 · Answer 2 · answered May 07 '15 at 13:56

If you want the info_message less generic like for it to switch to is when it's only a 1 count, I CAN do that, but that will require case logic that I don't believe is necessary. It's up to you though. Just let me know if you want me to change it.

DECLARE @Cnt_list VARCHAR(MAX) =
                                    (
                                    SELECT CAST(COUNT(*) AS VARCHAR(10)) + ' '
                                    FROM Person
                                    GROUP BY [Type]
                                    ORDER BY [Type]
                                    FOR XML PATH('')
                                    )
SELECT @Cnt_list as cnt_list

Results:

cnt_list
----------
2 1 1 1 1

Then for the second part:

SELECT 'There are ' + CAST(COUNT(*) AS VARCHAR(10)) + ' person(s) with a type of ' + CAST([type] AS VARCHAR(10)) + '(The first column value is: ' + CAST(COUNT(*) AS VARCHAR(10)) + ')' info_message
FROM Person
GROUP BY [Type]

Results:

info_message
--------------------------------------------------------------------
There are 2 person(s) with a type of 1(The first column value is: 2)
There are 1 person(s) with a type of 2(The first column value is: 1)
There are 1 person(s) with a type of 3(The first column value is: 1)
There are 1 person(s) with a type of 4(The first column value is: 1)
There are 1 person(s) with a type of 5(The first column value is: 1)

You can use STUFF function instead of a varchar(max) to get rid of the xml tags, `SELECT STUFF((` **Your statement here** `), 1, 1, ''))` — Zohar Peled, May 07 '15 at 15:27
Yeah I've used stuff with xml before but I wasn't sure what the OP wanted so I figured a variable would be the most versatile. — Stephan, May 07 '15 at 15:36

SQL statement to generate column values

2 Answers2