来自一个透视表的 SQL FOR XML 多级 [英] SQL FOR XML multilevel from one pivoted table

问题描述

我一直在尝试使用 FOR XML 来执行以下操作，但没有成功.

I've been trying to use FOR XML without success to do the following.

源表:

Country            | ID      | 1950        | 1955
-----------------------------------------------------
Country 1          | 1       | 2.43        | 2.55
Country 2          | 2       | 4.54        | 42.15

所需的输出:

<locations>
  <location>
    <loc name='Country 1' id='1' />
    <dub>
      <data year='1950' value='2.43' />
      <data year='1955' value='2.55' />
    </dub>
  </location>
  <location>
    <loc name='Country 2' id='2' />
    <dub>
      <data year='1950' value='4.54' />
      <data year='1955' value='42.15' />
    </dub>
  </location>
</locations>

是否有必要为配音元素取消旋转?我想要最简单的 SQL 查询.我认为 FOR XML 太难用了.您应该能够仅在列名上使用简单的 XPath 来指定层次结构，但它不会接受，例如，[dub/data/@year=1955/@value] 作为列名的名称列 [1950].

Will it be necessary to unpivot for the dub element? I wanted the simplest SQL query possible. I think FOR XML is too difficult to use. You should be able to specify the hierarchy just using simple XPath on column names but it won't accept, for example, [dub/data/@year=1955/@value] as the name of the column [1950].

推荐答案

SQL Fiddle

MS SQL Server 2012 架构设置:

create table YourTable
(
  Country varchar(20),
  ID int,
  [1950] numeric(5,2),
  [1955] numeric(5,2)
)

insert into YourTable values
('Country 1',           1,        2.43,         2.55),
('Country 2',           2,        4.54,         42.15)

查询 1:

select T.Country as 'loc/@name',
       T.ID as 'loc/@id',
       (
         select 1950 as 'data/@year',
                T.[1950] as 'data/@value',
                null,
                1955 as 'data/@year',
                T.[1955] as 'data/@value'
         for xml path(''), type
       ) as dub
from YourTable as T
for xml path('location'), root('locations'), type

结果:

<locations>
  <location>
    <loc name="Country 1" id="1" />
    <dub>
      <data year="1950" value="2.43" />
      <data year="1955" value="2.55" />
    </dub>
  </location>
  <location>
    <loc name="Country 2" id="2" />
    <dub>
      <data year="1950" value="4.54" />
      <data year="1955" value="42.15" />
    </dub>
  </location>
</locations>

这篇关于来自一个透视表的 SQL FOR XML 多级的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！