用于生成列值的SQL语句

Please see the DDL below:

请参阅下面的DDL:

 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)

2 个解决方案

#1

Use CASE to SUM different Type's

使用CASE来SUM不同的Type

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

#2

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.

如果你希望info_message不那么通用,就像切换到它只有1个计数一样,我可以这样做,但这需要我认为不必要的案例逻辑。这取决于你。如果您想让我改变它,请告诉我。

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)