SQL行转列

-------http://topic.csdn.net/u/20091031/14/d51a4265-59a2-4f4d-92b8-b4f285753f4e.html?

-------自己写了一遍练手

seed=933577974&r=60818074#r_60818074

create table T1 (A nvarchar(10), B nvarchar(10), C nvarchar(10))
insert into t1 values('SOB90311','1U0949012','MO9914011')
insert into t1 values('SOB90311','1U0949011','MO9914012')
go
create table T2 (C nvarchar(20), D nvarchar(20), F Float)
insert into T2 values('MO9914011','灌蜡',24)
insert into T2 values('MO9914011','包装',47.6)
insert into T2 values('MO9914012','灌蜡',16)
insert into T2 values('MO9914012','包装',32.7)
go
--sql2000 静态执行 适合D固定值
select A,B,T1.C,
灌蜡=MAX(Case D when '灌蜡' then F else 0 end),
包装=MAX(Case D when '包装' then F else 0 end)
from T1
left Join T2 On T1.C=T2.C
Group By T1.A,T1.B,T1.C
--sql2000 静态执行 适合D不固定
declare @str nvarchar(1000)
set @str ='Select A,B,T1.C'
select @str = @str + ',' + QUOTENAME(D)+'=Max(Case D When '''+D+''' then f else 0 end)'
from (select distinct D From T2) T2

select @str = @str + ' from T1 left Join T2 On T1.C=T2.C Group By T1.A,T1.B,T1.C '
print(@str)
exec(@str)

---2005静态
select * from (select A,B,T1.C,D,F from  T1
left Join T2 On T1.C=T2.C ) T pivot (max(F) for D in (包装,灌蜡)) B
---2005动态
--SQL SERVER 2005 动态SQL。
declare @sql varchar(8000)
select @sql = isnull(@sql + '],[' , '') + T2.D  from T2 group by T2.D
set @sql = '[' + @sql + ']'
print @Sql
exec ('select * from (select A,B,T1.C,D,F from  T1
left Join T2 On T1.C=T2.C ) T pivot (max(F) for D in (' + @sql + ')) b')
---
drop table t1,t2

/*
A          B          C          包装                     灌蜡
---------- ---------- ---------- ---------------------- ----------------------
SOB90311   1U0949011  MO9914012  32.7                   16
SOB90311   1U0949012  MO9914011  47.6                   24

(2 行受影响)

*/