MS SQL has a convenient workaround for concatenating a column value from multiple rows into one value:
SELECT col1
FROM table1
WHERE col2 = 'x'
ORDER by col3
FOR XML path('')
and that returns a nice recordset:
XML_F52E2B61-18A1-11d1-B105-00805F49916B
----------------------------------------
<col1>Foo</col1><col1>Bar</col1>
only the column name in the returned recordset is rather nasty!
The column name seems to include random elements (or a GUID), and hence I am reluctant to use it in my application (different instances or different servers might have another GUID). Unfortunately I cannot use * to select the value, and due to the restrictions in the existing application I cannot iterate through returned columns, either...
Is there a way to force the column name in the returned recordset to something more sensible?
6条答案
按热度按时间f45qwnt81#
That should do:
Not pretty but should give the result that you need
vnjpjtjt2#
Try this...
Note: If you omit the "type" after the "for xml", you get (I think) a string.
azpvetkf3#
stored procedure
w8biq8rn4#
For EXPLICIT xml generation - with unions you need to wrap results one more time (As a bonus result as XML):
ubbxdtey5#
cunj1qz16#