sql 行转列
原表结构
1 A B C D E F
2 。。。。。。。
3 。。。。。。。
4 。。。。。。。
5 。。。。。。。
转换成
2 3 4 5
A 。。。。
B 。。。。
C 。。。。
D 。。。。
E 。。。。
F 。。。。
[解决办法]
select
[1],
[A]=max(case when [column1]='2' then [column2] else 0 end),
[B]=max(case when [column1]='3' then [column2] else 0 end),
[C]=max(case when [column1]='4' then [column2] else 0 end),
[D]=max(case when [column1]='5' then [column2] else 0 end)
from
T
group by [1]
select 2 id,1 A,2 B,3 C,4 D,5 E,6 F
into #t
union all select 3 ,1,2,3,4,5,6
union all select 4 ,1,2,3,4,5,6
union all select 5 ,1,2,3,4,5,6
select * from #t
--查询
select name,max([2]) [2]
,max([3]) [3]
,max([4]) [4]
,max([5]) [5]
from
(
select 'A' NAME,case when id=2 then A end as '2'
,case when id=3 then A end as '3'
,case when id=4 then A end as '4'
,case when id=5 then A end as '5'
from #t
union all
select 'B',case when id=2 then B end as '2'
,case when id=3 then B end as '3'
,case when id=4 then B end as '4'
,case when id=5 then B end as '5'
from #t
union all
select 'C',case when id=2 then C end as '2'
,case when id=3 then C end as '3'
,case when id=4 then C end as '4'
,case when id=5 then C end as '5'
from #t
union all
select 'D',case when id=2 then D end as '2'
,case when id=3 then D end as '3'
,case when id=4 then D end as '4'
,case when id=5 then D end as '5'
from #t
union all
select 'E',case when id=2 then E end as '2'
,case when id=3 then E end as '3'
,case when id=4 then E end as '4'
,case when id=5 then E end as '5'
from #t
union all
select 'F',case when id=2 then F end as '2'
,case when id=3 then F end as '3'
,case when id=4 then F end as '4'
,case when id=5 then F end as '5'
from #t
) t
GROUP BY name
----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2014-01-03 13:18:56
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
--Dec 28 2012 20:23:12
--Copyright (c) Microsoft Corporation
--Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据：[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([1] VARCHAR(10),[A] varchar(10),[B] varchar(10),[C] varchar(10),[D] varchar(10),[E] varchar(10),[F] varchar(10))
insert [huang]
select 2,'asdf','1B','hf','e','n4','nb' union all
select 3,'asdf','1B','hf','e','n4','nb' union all
select 4,'asdf','1B','hf','e','n4','nb' union all
select 5,'asdf','1B','hf','e','n4','nb'
--------------开始查询--------------------------
SELECT CAST(letter AS VARCHAR) letter,[2]=max(CASE WHEN [1]=2 THEN [value] ELSE NULL END )
,[3]=max(CASE WHEN [1]=3 THEN [value] ELSE NULL END )
,[4]=max(CASE WHEN [1]=4 THEN [value] ELSE NULL END )
,[5]=max(CASE WHEN [1]=5 THEN [value] ELSE NULL END )
FROM (
select
[1],[letter],[value]
from
huang
unpivot
([value] for [letter] in([A],[B],[C],[D],[E],[F]))b)a
GROUP BY letter
----------------结果----------------------------
/*
letter 2 3 4 5
------------------------------ ---------- ---------- ---------- ----------
A asdf asdf asdf asdf
B 1B 1B 1B 1B
C hf hf hf hf
D e e e e
E n4 n4 n4 n4
F nb nb nb nb*/
