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 stackoverflow.com/questions/10404348/… if limited then you don't have to use dynamic SQL and either a case below would work or a standard pivot. — xQbert
– xQbert, Commented May 7, 2015 at 13:50

jarlh · Accepted Answer · 2015-05-07 13:36:46Z

6

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

answered May 7, 2015 at 13:36

jarlh

44.9k8 gold badges52 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Stephan · Accepted Answer · 2015-05-07 13:56:24Z

1

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)

answered May 7, 2015 at 13:56

Stephan

6,0581 gold badge19 silver badges26 bronze badges

2 Comments

Zohar Peled Over a year ago

You can use STUFF function instead of a varchar(max) to get rid of the xml tags, SELECT STUFF(( Your statement here ), 1, 1, ''))

Stephan Over a year ago

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.

Collectives™ on Stack Overflow

SQL statement to generate column values

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related