SQL查询 - 将列分组为XML

I have a table that looks like this in MS SQL Server.

我有一个在MS SQL Server中看起来像这样的表。

I want to query this table so that the TagId and Value columns are grouped together by EntityId and displayed as XML.

我想查询此表,以便TagId和Value列由EntityId组合在一起并显示为XML。

Sample query result

示例查询结果

The XML structure is not fixed. Any type of XML structure will do. Is it possible?

XML结构不是固定的。任何类型的XML结构都可以。可能吗?

2 个解决方案

#1

You could use:

你可以使用:

SELECT o.EntityName,
       o.EntityId,
       (SELECT (SELECT i.TagId "Tag/@id",
                       i.Value "Tag/@value"
                       FROM elbat i
                       WHERE i.EntityId = o.EntityId
                       FOR XML PATH(''),
                               TYPE)
               FOR XML PATH('Tags')) Value
       FROM elbat o
       GROUP BY o.EntityName,
                o.EntityId;

SQL Fiddle

#2

This not seems like a better method. But it gives solution:

这似乎不是一个更好的方法。但它提供了解决方案:

DECLARE @Table TABLE(id int, TagId int, Entityname Varchar(100),EntityId INt, value VARCHAR(50))

INSERT INTO @Table VALUES(1,1,'TravelerProfile',856,'Finanace')
INSERT INTO @Table VALUES(2,2,'TravelerProfile',856,'A')
INSERT INTO @Table VALUES(3,3,'TravelerProfile',856,'pune')

 select
   distinct Entityname, EntityId,
    stuff((
        select ',' + 'OpenTag'+ CAST(u.id AS VARCHAR)+'MidTag'+ u.value+'EndTag'
        from @Table u
        where u.EntityId = EntityId
        order by u.EntityId
        for xml path('')
    ),1,1,'') as Value
INTO #temp
from @Table
group by Entityname,EntityId

SELECT Entityname, EntityId
    ,REPLACE(REPLACE(REPLACE(REPLACE(VALUE,'OpenTag','<Tags><TagId = "'),'MidTag','" value="'),'EndTag','"/></Tags>'),',','') Value
FROM #temp

DROP TABLE #temp

Output:

Entityname      EntityId    Value
TravelerProfile 856         <Tags><TagId = "1" value="Finanace"/></Tags><Tags><TagId = "2" value="A"/></Tags><Tags><TagId = "3" value="pune"/></Tags>

#1