时间戳

首页 > 解决方案 > 如何在 SQL Server 中对 XML 格式数据使用 Group by 格式

sql - 如何在 SQL Server 中对 XML 格式数据使用 Group by 格式

问题描述

我有这些数据。

declare @T table
(
  ID int,
[subject]  varchar(30),
Marks int
)

insert into @T values  
(1, 'Maths',78),  
(1, 'Science',89),  
(2, 'Maths',90),  
(3, 'Maths',91),  
(4, 'Maths',92)

我试过这个查询：

SELECT ID
 ,(SELECT t1.* FOR XML PATH('body'),TYPE) AS TheRowAsXml
FROM @T as t1

但它为每个条目提供 1 行。

我想按 ID 分组

我的预期输出如下：

    <body>
      <ID>1</ID>
      <subject>Maths</subject>
      <Marks>78</Marks>
      <subject>Science</subject>
      <Marks>89</Marks>
    </body>

对于 ID 1 等

请任何建议将不胜感激

标签： sqlsql-serversql-server-2012

解决方案

您可以使用子查询生成，如下所示：

declare @T table
(
  ID int,
[subject]  varchar(30),
Marks int
)


insert into @T values
(1, 'Maths',78),
(1, 'Science',89),
(2, 'Maths',90),
(3, 'Maths',91),
(4, 'Maths',92)

--SELECT id, subject, marks from @T WHERE id = 1
--for xml path('')

SELECT distinct  id,CONCAT('<body><ID>',cast(id as varchar(10)),'</ID>',
(SELECT subject, marks from @T WHERE id = t.id
for xml path('')
),'</body>') as Rowxml
from @t as t



+----+-------------------------------------------------------------------------------------------------------------+
| id |                                                   Rowxml                                                    |
+----+-------------------------------------------------------------------------------------------------------------+
|  1 | <body><ID>1</ID><subject>Maths</subject><marks>78</marks><subject>Science</subject><marks>89</marks></body> |
|  2 | <body><ID>2</ID><subject>Maths</subject><marks>90</marks></body>                                            |
|  3 | <body><ID>3</ID><subject>Maths</subject><marks>91</marks></body>                                            |
|  4 | <body><ID>4</ID><subject>Maths</subject><marks>92</marks></body>                                            |
+----+-------------------------------------------------------------------------------------------------------------+

更新

一种更清洁的方法

SELECT id "ID",max(t2.rowxml)
from @t as t
CROSS APPLY 
(SELECT subject, marks 
from @T WHERE id = t.id
for xml path('')
) as t2(rowxml)
group by t.id
for xml path('body')

推荐阅读