Problem with SQL Server syntax. Combining Pivot, XML Analysis, and JOIN

Question

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 product attributes in our database, and therefore, while I'm only interested in the user ID and product identifier, extra information can be stored here, for example:

  Info Irrelevant ID=666,User ID=123124,AnotherIrrelevantID=1232342,Item ID=1213124 

And so I have an SQL query:

 -- 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 TNvalue('local-name(.)', 'varchar(max)') as Name, --selects all attributes returned in varchar(max) format as Name TNvalue('.', '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 the columns to me as follows:

 UserID 1 2 3 4 5 

Now I have another table, Table2 , which contains the orders that the user made. I want to use UserID as a reference in this table, and therefore, instead of returning only UserID, I will return the rows in this table where the UserID returned above is equal to the rows in this table.

So, instead of the above, we get:

 UserID Table2Col Table2Col2 2 Info Info 5 Info Info 5 Info2 Info2 5 Info3 Info3 

2 questions - how can I execute a JOIN or make a subquery to join two tables, I cannot figure out how to do this with the correct syntax. Secondly, I wrote some comments on my request above, which show how I understand that the request should work. Are they right?

Answer 1

It is possible that I have something missing for your question, but it looks like you can just expand your existing query as follows. This still uses CTE and PIVOT, but the PIVOT request is placed in a subquery that allows you to join 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 TNvalue('local-name(.)', 'varchar(max)') as Name, TNvalue('.', '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