SQL Server:如何统计不同的记录并作为列进行分隔

Question

我目前正在使用SQL Server 2016从表中进行选择，以输出不同的布局。下表item_stats:

code_no | code_status | code_category
--------+-------------+--------------
a123    |      x      | error1
a123    |      y      | error1
a123    |      z      | error1
a123    |      x      | error1
a123    |      y      | error2
b123    |      x      | error2
b123    |      y      | error2
c123    |      y      | error1
c123    |      z      | error1

我正在尝试使用groupby，甚至partitionby，但没有得到结果。我想得到以下输出：

code_no | x | y | z | code_category
--------+---+---+---+---------------
a123    | 2 | 1 | 1 | error1
a123    | 0 | 1 | 0 | error2
b123    | 1 | 1 | 0 | error2
c123    | 0 | 1 | 2 | error1

基本上，结果将由code_no和code_category分组，并将x、y和z的每个code_status显示为一列，并显示这些列中的出现次数。

感谢在获得输出方面提供的任何帮助或指导。我尝试过使用groupby，但布局没有将代码状态拆分为单独的列。

谢谢A.Fai

Answer 1

尝试以下查询-：

select code_no,
SUM(case when code_status='x' then 1 else 0 end) as [X],
SUM(case when code_status='y' then 1 else 0 end) as [Y],
SUM(case when code_status='z' then 1 else 0 end) as [Z],
code_category 
from 
[YOUR_TABLE_NAME]
group by code_no,code_category 
order by code_no 

带静态数据透视-：

select code_no,[X],[Y],[Z],code_category
from [YOUR_TABLE_NAME] 
PIVOT
(
COUNT(code_status) for code_status in ([x],[y],[z])
)a
order by code_no

带动态数据透视-：

DECLARE @cols AS NVARCHAR(MAX),
@query  AS NVARCHAR(MAX)
SELECT @cols = STUFF((SELECT distinct  ',' + QUOTENAME(code_status) 
FROM [YOUR_TABLE_NAME]
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)') 
,1,1,'')
SELECT @query = 
'SELECT code_no,'+@cols+',code_category FROM [YOUR_TABLE_NAME]
PIVOT 
(
count(code_status)
for [code_status] in (' + @cols + ')
) P order by code_no'
EXEC SP_EXECUTESQL @query

Answer 2

您可以尝试使用聚合函数条件来制作Pivot表。

select code_no,
SUM(CASE WHEN code_status = 'x' THEN 1 ELSE 0 END) x,
SUM(CASE WHEN code_status = 'y' THEN 1 ELSE 0 END) y,
SUM(CASE WHEN code_status = 'z' THEN 1 ELSE 0 END) z,
code_category 
from t
group by code_no,code_category 

SQLFIDDLE

[结果]:

| code_no | x | y | z | code_category |
|---------|---|---|---|---------------|
|    a123 | 2 | 1 | 1 |        error1 |
|    c123 | 0 | 1 | 1 |        error1 |
|    a123 | 0 | 1 | 0 |        error2 |
|    b123 | 1 | 1 | 0 |        error2 |

Answer 3

使用CASE表达式。

查询

select [code_no],
sum(case [code_status] when 'x' then 1 end) as [x],
sum(case [code_status] when 'y' then 1 end) as [y],
sum(case [code_status] when 'z' then 1 end) as [z],
[code_category]
from [your_table_name]
group by [code_no], [code_category];

Answer 4

select 
code_no, 
(select count(*) from item_stats where code_no=itst.code_no and code_status='x' and code_category=itst.code_category) as x,
(select count(*) from item_stats where code_no=itst.code_no and code_status='y' and code_category=itst.code_category) as y,
(select count(*) from item_stats where code_no=itst.code_no and code_status='z' and code_category=itst.code_category) as z,
code_category
from item_stats itst
group by code_no, code_category
order by code_no, code_category