如何把列转换为行？

[scvzhang]

已知ID,如何从ID得到table1 B列中的值并把它转换为行？

(table1)
A B.ID B.VALUE
-------------------
ID P1 P1V
ID P2 P2V
ID P3 P3V
.. ..

(table2)
YGID B.ID B.VALUE
---------------------
001 P1 P1V1
001 P2 P2V1
001 P3 P3V1
001 P1 P1V2
002 P1 ...
－－－>
YGID P1 P2 P3 ...
-----------------------------
001 xxx xxx xxx xxx
001 xxx xxx xxx xxx
002 xxx xxx xxx xxx

[hsh_boy]

看一下这个例子
http://community.csdn.net/Expert/topic/3473/3473665.xml?temp=.299206

[Andy__Huang]

參考一個例子


create table tb(名稱 varchar(10),數量 numeric(10),類型 varchar(5))
Insert into tb
select 'L001','1','A'
union all select 'L001','2','B'
union all select 'L002','5','C'
union all select 'L003','6','D'
union all select 'L004','9','A'
union all select 'L004','5','D'

select * from tb

declare @sql varchar(1000)
set @sql=''
select @sql=@sql+',['+max(類型)+']=sum(case 類型 when '''+max(類型)+''' then 數量 else 0 end)'
from tb group by 類型
print @sql

exec('select 名稱'+@sql+' from tb group by 名稱')
--結果
名稱 A B C D
---------------------------------------
L001 1 2 0 0
L002 0 0 5 0
L003 0 0 0 6
L004 9 0 0 5

[yjdn]

用动态语句来解决：
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+',sum(case [B.ID] when '''+[B.ID]+''' then [B.VALUE] else '' end) as ['+[B.ID]+']'
from table1
group by [B.ID]

exec('select YGID'+@sql+' from table2 group by YGID')


--没有测试

[yjdn]

--B.VALUE字段应该是字符串型 的吧，改一下。
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+',max(case [B.ID] when '''+[B.ID]+''' then [B.VALUE] else '' end) as ['+[B.ID]+']'
from table1
group by [B.ID]

exec('select YGID'+@sql+' from table2 group by YGID')

[yjdn]

--建表
create table table1([A] varchar(10),[B.ID] varchar(10),[B.Value] varchar(10))

insert into table1 select
'ID' , 'P1' , 'P1V'
union all select
'ID' , 'P2' , 'P2V'
union all select
'ID' , 'P3' , 'P3V'


create table table2(YGID varchar(10), [B.ID] varchar(10), [B.VALUE] varchar(10))
insert into table2 select
'001' , 'P1' , 'P1V1'
union all select
'001' , 'P2' , 'P2V1'
union all select
'001' , 'P3' , 'P3V1'
union all select
'001' , 'P1' , 'P1V2'
union all select
'002' , 'P1' , '...'

--查询
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+',max(case [B.ID] when '''+[B.ID]+''' then [B.VALUE] else '''' end) as ['+[B.ID]+']'
from table1
group by [B.ID]

exec('select YGID'+@sql+' from table2 group by YGID')


--结果：
001 P1V2 P2V1 P3V1
002 ...