Sql查询获取表中每列的不同条目的计数
问题描述:
假设我有一个数据库中有60列的表。有人可以告诉我查询来获取表格每列(60)中所有不同条目的计数。Sql查询获取表中每列的不同条目的计数
Cl1 Cl2 Cl3
68722 No No No No No Yes Male No No
68723 No Yes No No No Yes FeMale No No
68725 No No No No Yes Male No No
就像在上表假设我有10列(第1列至10列)
答案我想:
Cl2 No =2 , Blank=1
Cl3 No=2, Yes =1
等多达10
请给我一个Sql Server的查询。
答
UNPIVOT + GROUP BY和COUNT:
SELECT [Columns],
Case when [Values] = '' THEN 'Blank' ELSE [Values] END [Values],
COUNT(Id) as HowMany
FROM (
SELECT Id,
CAST(Cl1 as nvarchar(max)) as Cl1,
CAST(Cl2 as nvarchar(max)) as Cl2,
CAST(Cl3 as nvarchar(max)) as Cl3,
CAST(Cl4 as nvarchar(max)) as Cl4,
CAST(Cl5 as nvarchar(max)) as Cl5,
CAST(Cl6 as nvarchar(max)) as Cl6,
CAST(Cl7 as nvarchar(max)) as Cl7,
CAST(Cl8 as nvarchar(max)) as Cl8,
CAST(Cl9 as nvarchar(max)) as Cl9
FROM YourTable
) as t
UNPIVOT (
[Values] FOR [Columns] IN (Cl1, Cl2, Cl3, Cl4, Cl5, Cl6, Cl7, Cl8, Cl9)
) as unpvt
GROUP BY [Columns], [Values]
ORDER BY [Columns]
输出:
你可以把这个在CTE和:
;WITH cte as (
SELECT [Columns],
Case when [Values] = '' THEN 'Blank' ELSE [Values] END + '='+CAST(COUNT(Id) as nvarchar(max)) as HowMany
FROM (
SELECT Id,
CAST(Cl1 as nvarchar(max)) as Cl1,
CAST(Cl2 as nvarchar(max)) as Cl2,
CAST(Cl3 as nvarchar(max)) as Cl3,
CAST(Cl4 as nvarchar(max)) as Cl4,
CAST(Cl5 as nvarchar(max)) as Cl5,
CAST(Cl6 as nvarchar(max)) as Cl6,
CAST(Cl7 as nvarchar(max)) as Cl7,
CAST(Cl8 as nvarchar(max)) as Cl8,
CAST(Cl9 as nvarchar(max)) as Cl9
FROM YourTable
) as t
UNPIVOT (
[Values] FOR [Columns] IN (Cl1, Cl2, Cl3, Cl4, Cl5, Cl6, Cl7, Cl8, Cl9)
) as unpvt
GROUP BY [Columns], [Values]
)
SELECT DISTINCT
[Columns] +' '+
STUFF((
SELECT ';'+HowMany
FROM cte
WHERE c.[Columns] = [Columns]
FOR XML PATH('')
),1,1,'') as [Values]
FROM cte c
输出:
Values
Cl1 Blank=1;No=2
Cl2 No=2;Yes=1
Cl3 No=3
Cl4 No=3
Cl5 No=3
Cl6 Yes=3
Cl7 FeMale=1;Male=2
Cl8 No=3
Cl9 No=3