来自一个透视表的 SQL FOR XML 多级

SQL FOR XML multilevel from one pivoted table(来自一个透视表的 SQL FOR XML 多级)

问题描述

我一直在尝试使用 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 多级的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！