MS SQL有一个方便的解决方法,用于将多个行的列值连接成一个值:
SELECT col1 FROM table1 WHERE col2 = 'x' ORDER by col3 FOR XML path('')并返回一个不错的记录集:
XML_F52E2B61-18A1-11d1-B105-00805F49916B ---------------------------------------- <col1>Foo</col1><col1>Bar</col1>只有在返回的记录集中的列名称相当讨厌!
列名似乎包含随机元素(或GUID),因此我不愿意在我的应用程序中使用它(不同的实例或不同的服务器可能有另一个GUID)。 不幸的是,我不能使用*来选择该值,并且由于现有应用程序中的限制,我无法遍历返回的列,无论是...
有没有办法强制返回的记录集中的列名称更加明智?
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?
最满意答案
应该这样做:
select( SELECT col1 FROM table1 WHERE col2 = 'x' ORDER by col3 FOR XML path('') ) as myName不漂亮,但应该给你需要的结果
That should do:
select( SELECT col1 FROM table1 WHERE col2 = 'x' ORDER by col3 FOR XML path('') ) as myNameNot pretty but should give the result that you need
更多推荐
发布评论