我正在使用sql Server(2008/2012),我知道很多搜索都有类似的答案,但是我似乎无法为我的案例找到合适的示例/指针.
我在sql Server表中有一个XML列来保存这些数据：
<Items>
<Item>
<FormItem>
<Text>FirstName</Text>
<Value>My First Name</Value>
</FormItem>
<FormItem>
<Text>LastName</Text>
<Value>My Last Name</Value>
</FormItem>
<FormItem>
<Text>Age</Text>
<Value>39</Value>
</FormItem>
</Item>
<Item>
<FormItem>
<Text>FirstName</Text>
<Value>My First Name 2</Value>
</FormItem>
<FormItem>
<Text>LastName</Text>
<Value>My Last Name 2</Value>
</FormItem>
<FormItem>
<Text>Age</Text>
<Value>40</Value>
</FormItem>
</Item>
</Items>
即使< FormItem>的结构也是如此.将是相同的,我可以有多个(通常不超过20-30)套表格项目..
我本质上是尝试以下面的格式从sql返回一个查询,即基于/ FormItem / Text的动态列：
FirstName LastName Age ---> More columns as new `<FormItem>` are returned My First Name My Last Name 39 Whatever value etc.. My First Name 2 My Last Name 2 40
所以,目前我有以下内容：
select
Tab.Col.value('Text[1]','nvarchar(100)') as Question,Tab.Col.value('Value[1]','nvarchar(100)') as Answer
from
@Questions.nodes('/Items/Item/FormItem') Tab(Col)
当然,这并没有将我的XML行转换成列,显然也是用字段修复的.我一直在尝试各种“动态sql”方法,其中sql执行(在我的情况下)< Text>的不同选择.节点,然后使用某种Pivot？但我似乎无法找到神奇的组合来返回我需要的结果作为每一行的动态列集(< Item>在< Items>的集合中).
我确信看到这么多非常相似的例子可以做到,但是我的解决方案再次出现了！
任何帮助感激不尽！
解决方法
解析XML相当昂贵,因此不是解析一次来构建动态查询,而是一次获取数据,您可以创建一个带有Name-Value列表的临时表,然后将其用作动态数据透视查询的源.
dense_rank用于创建要转移的ID.
要在动态查询中构建列列表,它使用for xml path(”)技巧.
dense_rank用于创建要转移的ID.
要在动态查询中构建列列表,它使用for xml path(”)技巧.
此解决方案要求您的表具有主键(ID).如果您在变量中使用XML,则可以稍微简化一下.
select dense_rank() over(order by ID,I.N) as ID,F.N.value('(Text/text())[1]','varchar(max)') as Name,F.N.value('(Value/text())[1]','varchar(max)') as Value
into #T
from YourTable as T
cross apply T.XMLCol.nodes('/Items/Item') as I(N)
cross apply I.N.nodes('FormItem') as F(N)
declare @sql nvarchar(max)
declare @Col nvarchar(max)
select @Col =
(
select distinct ','+quotename(Name)
from #T
for xml path(''),type
).value('substring(text()[1],2)','nvarchar(max)')
set @sql = 'select '+@Col+'
from #T
pivot (max(Value) for Name in ('+@Col+')) as P'
exec (@sql)
drop table #T
