T-sql 行转换成列

[wo2xie]

请问:
DECLARE @doc xml
set @doc=(select Fxml from 订单产品记录表 where FID='06cb85ec-57fe-4554-a657-1e666cc590b4')
--上面那句话是取出一个XML字段的数据,数据如下("<root><user level='01'><game type='1' member='user'><item type='UserLimit'>abcd</item><item type='UserLimit2'>dfsdafds</item><item type='UserLimit3'>5</item></game></user></root>";)

select @doc

declare @up Table
(
feldkey varchar(127),
feldvalue varchar(127)
)
insert @up
SELECT cast(订单产品记录表.c.value('@type','varchar(127)') as varchar(127)) AS feldkey,
cast(订单产品记录表.c.query('text()') as varchar(127)) AS feldvalue
FROM @doc.nodes('(/root/user/game/item)') 订单产品记录表(c)


declare @set varchar(8000)
select @set=isnull(@set+',','') + a.feldkey from (
select cast(订单产品记录表.c.value('@type','varchar(200)') as varchar(2000)) AS feldkey, --节点属性
cast(订单产品记录表.c.query('text()') as varchar(2000)) AS feldvalue --节点值
FROM @doc.nodes('(/root/user/game/item)') 订单产品记录表(c)) a



SELECT * FROM (select * from @up) as t PIVOT (count(feldvalue) FOR feldkey IN (@set)) Alias

报错:必须声明标量变量 "@set"。或必须声明标量变量@up

[华夏小卒]

表名，字段名动态，要用动态语句

[liangCK]

FOR feldkey IN (@set)) Alias

要用动态SQL.

[--小F--]

exec 来执行动态语句

[xiequan2]

要用动态SQL.

[dawugui]

exec('SELECT * FROM (select * from '+ @up + ' as t PIVOT (count(feldvalue) FOR feldkey IN ( ''' + @set + ''')) Alias')

[liangCK]

动态SQL的话,用临时表,而不用表变量.

[dawugui]

[Quote=引用 6 楼 liangck 的回复:]
动态SQL的话,用临时表,而不用表变量.
[/Quote]哦,对.

[liangCK]

Create Table #up 
( 
    feldkey  varchar(127), 
    feldvalue  varchar(127) 
) 
insert #up (feldkey,feldvalue)
SELECT 订单产品记录表.c.value('@type','varchar(127)') AS feldkey, 
    订单产品记录表.c.value('.','varchar(127)') AS feldvalue 
 FROM @doc.nodes('(/root/user/game/item)') 订单产品记录表(c) 


declare @set varchar(8000) 
select @set=isnull(@set+',','') + feldkey from #up group by feldkey;
    
exec('select * from #up pivot(count(feldvalue) for feldkey in('+@set+')) as pvt')

drop table #up

[wo2xie]

Thank you 各位 跟贴,在下明白了.