22,210
社区成员
发帖
与我相关
我的任务
分享
USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
CREATE TABLE t(asd INT)
INSERT INTO t VALUES (1)
INSERT INTO t VALUES (2)
INSERT INTO t VALUES (1)
INSERT INTO t VALUES (3)
INSERT INTO t VALUES (5)
INSERT INTO t VALUES (null)
INSERT INTO t VALUES (4)
INSERT INTO t VALUES (1)
INSERT INTO t VALUES (3)
INSERT INTO t VALUES (4)
;WITH cte AS (
SELECT asd,COUNT(1) AS cnt FROM t
WHERE asd IS NOT NULL
GROUP BY asd
)
SELECT [1] AS [一级]
,[2] AS [二级]
,[3] AS [三级]
,[4] AS [四级]
,[5] AS [五级]
FROM cte PIVOT(MAX(cnt)
FOR asd in ([1],[2],[3],[4],[5]) ) AS p
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([asd] INT)
Insert #T
select 1 union all
select 2 union all
select 1 union all
select 3 union all
select 5 union all
select null union all
select 4 union all
select 1 union all
select 3 union all
select 4
Go
--测试数据结束
declare @sql varchar(8000)=''
select @sql=@sql+',sum(case asd when '''+ RTRIM(asd) +''' then 1 else 0 end) ['+ RTRIM(asd)+'级' +']'
from (select distinct [asd] from #T WHERE asd IS NOT NULL) as a
set @sql = 'select '+STUFF(@sql,1,1,'')
set @sql=@sql+' from #T WHERE asd IS NOT NULL '
EXEC(@sql)
SELECT RTRIM(asd)+'级' AS col ,
COUNT(1) AS asd
FROM #T
WHERE asd IS NOT NULL
GROUP BY asd