级联将sql行分组到Xml节点中

I have the following rows:

我有以下行:

ID|Customer | Part Number | Part Number Price | Hardware ID | Hardware Value
------------------------------------------------------------------------------
1 | John    |     15      |       10          |      1      | 1000
2 | John    |     16      |       15          |      2      | 500

The output i'm trying to get in SQL Server is the following:

我试图进入SQL Server的输出如下:

<Order>
 <Customer>John</Customer>
 <PartNumbers>
  <PartNumber>15</PartNumber><PartNumberPrice>10</PartNumberPrice>  
  <PartNumber>16</PartNumber><PartNumberPrice>15</PartNumberPrice>  
 </PartNumbers>
 <Hardware>
  <HardwareId>1</HardwareId><HardwareValue>1000</HardwareValue>
  <HardwareId>1</HardwareId><HardwareValue>500</HardwareValue>
 </Hardware>
</Orders>

Any idea how to solve this one?

知道如何解决这个问题吗?

Thanks!

2 个解决方案

#1

declare @T table
(
  ID int,
  Customer varchar(10),
  [Part Number] int,
  [Part Number Price] int,
  [Hardware ID] int,
  [Hardware Value] int
)

insert into @T values
(1, 'John', 15, 10, 1, 1000),
(2, 'John', 16, 15, 2, 500)

select T1.Customer as Customer,
       (select T2.[Part Number] as PartNumber,
               T2.[Part Number Price] as PartNumberPrice
        from @T as T2
        where T1.Customer = T2.Customer       
        for xml path(''), root('PartNumbers'), type),
       (select T2.[Hardware ID] as HardwareId,
               T2.[Hardware Value] as HardwareValue
        from @T as T2
        where T1.Customer = T2.Customer       
        for xml path(''), root('Hardware'), type)
from @T as T1
group by T1.Customer
for xml path(''), root('Order')

#2

This may look a little funny with the self joins but that is only because your tables are not properly normalized. You might want to consider column names without spaces as well.

自我连接可能看起来有点滑稽,但这只是因为你的表没有正确规范化。您可能还想考虑没有空格的列名。

SELECT Customer as Customer,
      (SELECT DISTINCT o.[Part Number] partNumber,o.[Part Number Price] PartNumberPrice
       FROM yTable o
       where t.id = o.id
       FOR XML AUTO, TYPE),
      (SELECT DISTINCT x.[Hardware ID] hardwareid,x.[Hardware Value] hardwarevalue
       FROM yTable x
       where t.id = x.id
       FOR XML AUTO, TYPE)
FROM yTable t
FOR XML AUTO, TYPE