March 15, 2013 at 10:18 am
Looking for SQL Query help on following.
Have a key value pair, as below.
declare @t table (id int, varchar(10), value varchar(10))
insert @t values (1, 'ColA', 'ABC123')
insert @t values (1, 'colB', 'DEF456')
insert @t values (2, 'colA', '2ColABC')
insert @t values (2, 'colC', '2ColDef')
insert @t values (2, 'colE', '2Colxyz')
Need to bundle the Key-value combination into XML for each ID row. The expected results are
1, '<AdditionalCols><Col ColName="colA">ABC123</Col><Col ColName="colB">DEF456</Col></AdditionalCols>'
2, '<AdditionalCols><Col ColName="colA">2ColABC</Col><Col ColName="colC">2ColDef</Col><Col ColName="colE">2Colxyz</Col></AdditionalCols>'
Tried few combinations using FOR XML, but could not get the desired result at each row level.
March 15, 2013 at 10:38 am
SELECT a.id,
(SELECT b. AS "@ColName",
b.value AS "text()"
FROM @t b
WHERE b.id=a.id
FOR XML PATH('Col'),ROOT('AdditionalCols'),TYPE) AS keyvalues
FROM @t a
GROUP BY a.id
ORDER BY a.id;
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537March 15, 2013 at 11:16 am
Thank you very much. The solution worked, and this was exactly what I was looking for..!!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy