Syntax issue SQL Server. Combining Pivot, XML parse and JOIN
I have a column (varchar400) in the following form in an SQL table :
Info
User ID=1123456,Item ID=6685642
This column is used to store attributes of products in our database, and so while I am only concerned with User ID and Item ID, there may be superfluous information stored here, for example :
Info
Irrelevant ID=666,User ID=123124,AnotherIrrelevantID=1232342,Item ID=1213124
And so I have an SQL query as follows:
-- 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
Now, this correctly returns me a columns as follows :
UserID
1
2
3
4
5
Now I have another table, Table2 , which holds the orders a user has made. I want to use the UserID as a reference into this table, and so instead of returning just UserID, I will return the rows on this table where the UserID I returned above equals the rows in this table.
So, instead of above, we get:
UserID Table2Col Table2Col2
2 Info Info
5 Info Info
5 Info2 Info2
5 Info3 Info3
2 questions - how can I perform a JOIN or do a subquery to combine the two tables, I cannot figure out how to do this with correct syntax. Secondly, I have written some comments on my query above that show how I understand the query to be working. Are they correct?
It is quite possible that I am missing something with your question, but it seems like you can just expand your existing query the following way. This still uses the CTE and the PIVOT, but the PIVOT query is placed in a subquery which allows you to join to table2 :
; with cte as --imports a library of common table expressions
(
select TOP 1000 cast('<info ' + REPLACE(REPLACE(REPLACE(REPLACE(OtherInformation,' ', ''),',', '" '),'=','="'),'.','') + '" />' as XML) info
, 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(.)', 'varchar(max)') as Name,
T.N.value('.', 'varchar(max)') as 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;
See SQL Fiddle with Demo
链接地址: http://www.djcxy.com/p/72278.html上一篇: 在具有多个小数位的列上排序