22,209
社区成员
发帖
与我相关
我的任务
分享
with cte as
(select '城区' as 区域,1 as 耗时, '90%' as 达标率,1 as 数量 union all
select '东区' as 区域,2 as 耗时, '80%' as 达标率,2 as 数量 union all
select '南区' as 区域,3 as 耗时, '100%' as 达标率,6 as 数量 union all
select '西区' as 区域,4 as 耗时, '10%' as 达标率,22 as 数量 )
select '耗时' as 类型,* from
(select 区域,convert(varchar(5),耗时) as 耗时 from cte) as t
pivot (max(耗时) for 区域 in([城区],[东区],[南区],[西区])) as p
union all
select '达标率',* from
(select 区域,达标率 from cte) as t
pivot (max(达标率) for 区域 in([城区],[东区],[南区],[西区])) as p
union all
select '数量',* from
(select 区域,convert(varchar(5),数量) as 数量 from cte) as t
pivot (max(数量) for 区域 in([城区],[东区],[南区],[西区])) as p
--结果
类型 城区 东区 南区 西区
------ ----- ----- ----- -----
耗时 1 2 3 4
达标率 90% 80% 100% 10%
数量 1 2 6 22
SELECT '耗时' as [类型],[城区],[东区],[南区],[西区]
FROM [表名]
pivot
(
sum([耗时]) for [区域] in ([城区],[东区],[南区],[西区])
) as pvt
WITH test(区域,耗时,达标率,数量) AS (
-- 数据必须一致,统一为 char(4)
SELECT N'城区',' 1',' 90%',' 1' UNION ALL
SELECT N'东区',' 2',' 80%',' 2' UNION ALL
SELECT N'南区',' 3','100%',' 6' UNION ALL
SELECT N'西区',' 4',' 10%',' 22'
),
-- 先拆分单元
cells AS (
SELECT 区域,类型,value
FROM test
UNPIVOT (value FOR 类型 IN (耗时,达标率,数量)
) u
)
-- 再组合
SELECT *
FROM cells
PIVOT (MAX(value) FOR 区域 IN ([城区],[东区],[南区],[西区])
) p
ORDER BY CASE 类型 WHEN '耗时' THEN 1
WHEN '达标率' THEN 2
WHEN '数量' THEN 3
END
类型 城区 东区 南区 西区
----------- ---- ---- ---- ----
耗时 1 2 3 4
达标率 90% 80% 100% 10%
数量 1 2 6 22