我在sql表中有以下形式的列(varchar400)：
Info User ID=1123456,Item ID=6685642
此列用于存储数据库中产品的属性,因此虽然我只关注用户ID和项ID,但此处可能存储了多余的信息,例如：
Info Irrelevant ID=666,User ID=123124,AnotherIrrelevantID=1232342,Item ID=1213124
所以我有一个SQL查询如下：
-- convert info column to xml type
; with cte as --imports a library of common table expressions
(
select TOP 1000 cast('<info ' + REPLACE(REPLACE(REPLACE(REPLACE(OtherInformation,' ',''),','" '),'=','="'),'.','') + '" />' as XML) info,--puts the OtherInformation column into well formed XML
ROW_NUMBER() over (order by TableID) id --returns all rows??
FROM Table
WHERE TableEnum=51
)
SELECT DISTINCT UserID from --selects unique user ids from our returned xml
(
select T.N.value('local-name(.)','varchar(max)') as Name,--selects all attributes returned in varchar(max) format as Name
T.N.value('.','varchar(max)') as Value,id --Selects all values returned
from cte cross apply info.nodes('//@*') as T(N) -- from the XML we created above
) v
pivot (max(value) for Name in ([UserID])) p --creates a pivot table on Name,separating all of the attributes into different columns
现在,这正确地返回一列如下：
UserID 1 2 3 4 5
现在我有另一个表Table2,它保存用户所做的订单.我想使用UserID作为此表的引用,因此我将返回此表中我返回的UserID等于此表中的行的行,而不是仅返回UserID.
所以,而不是上面,我们得到：
UserID Table2Col Table2Col2 2 Info Info 5 Info Info 5 Info2 Info2 5 Info3 Info3
2个问题 – 如何执行JOIN或执行子查询来组合这两个表,我无法弄清楚如何使用正确的语法执行此操作.
其次,我在上面的查询中写了一些评论,说明我如何理解查询是否正常工作.他们是对的吗？
解决方法
很可能我错过了你的问题,但似乎你可以通过以下方式扩展现有的查询.这仍然使用CTE和PIVOT,但PIVOT查询放在子查询中,允许您加入table2：
; with cte as --imports a library of common table expressions
(
select TOP 1000 cast('<info ' + REPLACE(REPLACE(REPLACE(REPLACE(OtherInformation,ROW_NUMBER() over (order by TableID)) id
FROM yourtable
)
select d.userid,t2.col1,t2.col2
from
(
SELECT DISTINCT UserID
from
(
select T.N.value('local-name(.)',T.N.value('.',id
from cte
cross apply info.nodes('//@*') as T(N)
) v
pivot
(
max(value)
for Name in ([UserID])
) p
) d
inner join table2 t2
on d.userid = t2.userid;
