# 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 行受影响)

*/

• 0
点赞
• 0
收藏
• 打赏
• 0
评论
09-09
09-28 15
12-11
05-17
09-09
03-24 2467
03-09
05-10
07-05 2万+
09-09
11-20
08-30
06-19 2002

### “相关推荐”对你有帮助么？

• 非常没帮助
• 没帮助
• 一般
• 有帮助
• 非常有帮助

bancxc

¥2 ¥4 ¥6 ¥10 ¥20

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、C币套餐、付费专栏及课程。