SQL2008 行转列 列头数量未知，列头数量查出
表detail:
表头 ID VAL NAME
数据 1 56 jack
数据 2 101 mary
数据 3 34 john
数据 4 99 jerry
数据 ... ... ...
——————————————————————————————————
难度一： 需要实现
表头 jack mary john jerry ...
ID 1 2 3 4 ...
VAL 56 101 34 99 ...
表头的jack、mary、john、jerry不止4个哦，是查出来的
——————————————————————————————————
难度二：如果detail中有两条或者更多ID=1，NAME=jack的数据
表头 ID VAL NAME
数据 1 56 jack
数据 1 10 jack
数据 1 10 jack
需要实现
表头 jack mary john jerry ...
ID 1 2 3 4 ...
VAL 76 101 34 99 ...
[解决办法]
--仅供参考
if OBJECT_ID('tempdb..#temp', 'u') is not null drop table #temp;
go
create table #temp( [表头] varchar(100), [ID] int, [VAL] int, [NAME] varchar(100));
insert #temp
select '数据','1','56','jack' union ALL
SELECT '数据','1','10','jack' UNION ALL
SELECT '数据','1','10','jack' UNION ALL
select '数据','2','101','mary' union all
select '数据','3','34','john' union all
select '数据','4','99','jerry'
--SQL:
;WITH cte AS
(
select id, name, val=SUM(val) from #temp GROUP BY id, name
)
SELECT *
FROM (SELECT id, name FROM cte) A
PIVOT (MAX(id) FOR name IN([jack],[jerry],[john],[mary])) B
UNION ALL
SELECT *
FROM (SELECT val, name FROM cte) A
PIVOT (MAX(val) FOR name IN([jack],[jerry],[john],[mary])) B
/*
jackjerryjohnmary
1432
769934101
*/
create table detail
(ID int, VAL int, name varchar(10))
insert into detail
select 1, 56, 'jack' union all
select 2, 101, 'mary' union all
select 3, 34, 'john' union all
select 4, 99, 'jerry'
-- 1
declare @tsql varchar(6000)
select @tsql=isnull(@tsql+',','')+'['+name+']'
from (select distinct ID,name from detail) t
order by ID
select @tsql='select '+@tsql
+' from (select ID,name from detail) a '
+' pivot(max(ID) for name in('+@tsql+')) p '
+' union all '
+'select '+@tsql
+' from (select VAL,name from detail) a '
+' pivot(max(VAL) for name in('+@tsql+')) p '
exec(@tsql)
/*
jack mary john jerry
----------- ----------- ----------- -----------
1 2 3 4
56 101 34 99
(2 row(s) affected)
*/
-- 2
truncate table detail
insert into detail
select 1, 56, 'jack' union all
select 1, 10, 'jack' union all
select 1, 10, 'jack' union all
select 2, 101, 'mary' union all
select 3, 34, 'john' union all
select 4, 99, 'jerry'
declare @tsql varchar(6000)
select @tsql=isnull(@tsql+',','')+'['+name+']'
from (select distinct ID,name from detail) t
order by ID
select @tsql='select '+@tsql
+' from (select distinct ID,name from detail) a '
+' pivot(max(ID) for name in('+@tsql+')) p '
+' union all '
+'select '+@tsql
+' from (select sum(VAL) ''VAL'',name from detail group by name) a '
+' pivot(max(VAL) for name in('+@tsql+')) p '
exec(@tsql)
/*
jack mary john jerry
----------- ----------- ----------- -----------
1 2 3 4
76 101 34 99
(2 row(s) affected)
*/