sql-server - SQL Server 2008 垂直数据到水平数据

Question

我很抱歉提交了有关此主题的另一个问题，但我已经阅读了有关此主题的许多答案，但似乎无法让它为我工作。

我有三个表需要加入并提取信息。其中一张表只有 3 列，并且垂直存储数据。我想将该数据转置为水平格式。

如果我只是加入并拉取，数据将如下所示:

SELECT 
   a.app_id, 
   b.field_id, 
   c.field_name,
   b.field_value 
FROM table1 a
JOIN table2 b ON a.app_id = b.app_id
JOIN table3 c ON b.field_id = c.field_id  --(table3 is a lookup table for field names)

结果:

app_id  |  field_id  |   field_name   |  field_value
-----------------------------------------------------
 1234   |    101     |    First Name  |     Joe  
 1234   |    102     |     Last Name  |     Smith
 1234   |    105     |       DOB      |   10/15/72
 1234   |    107     |  Mailing Addr  |   PO BOX 1234
 1234   |    110     |      Zip       |     12345      
 1239   |    101     |    First Name  |     Bob  
 1239   |    102     |     Last Name  |     Johnson
 1239   |    105     |       DOB      |   12/01/78
 1239   |    107     |  Mailing Addr  |   1234 N Star Ave
 1239   |    110     |      Zip       |     12456  

相反，我希望它看起来像这样:

app_id  |  First Name  |   Last Name   |    DOB    |   Mailing Addr   |  Zip
--------------------------------------------------------------------------
 1234   |    Joe       |     Smith     |  10/15/72 |   PO BOX 1234    | 12345     
 1239   |    Bob       |    Johnson    |  12/01/78 |  1234 N Star Ave | 12456 

过去，我只是查找数据中所需的所有 field_id，并为每个字段创建 CASE 语句。用户使用的应用程序包含多个产品的数据，并且每个产品包含不同的字段。考虑到支持的产品数量和每个产品的字段数量(比我上面展示的基本示例多很多)，查找它们并写出大量 CASE 语句需要很长时间。

我想知道是否有一些作弊代码可以实现我所需要的，而无需查找 field_ids 并将内容写出来。我知道 PIVOT 功能可能就是我正在寻找的功能，但是，我似乎无法让它正常工作。

你们可以帮忙吗？

Answer 1

您可以使用 PIVOT 函数将数据行转换为列。

您的原始查询可用于检索所有数据，我对其进行的唯一更改是排除列 b.field_id 因为这会改变结果的最终显示。

如果您有想要转换为列的已知 field_name 值列表，则可以对查询进行硬编码:

select app_id,
  [First Name], [Last Name], [DOB],
  [Mailing Addr], [Zip]
from
(
  SELECT 
     a.app_id, 
     c.field_name,
     b.field_value 
  FROM table1 a
  INNER JOIN table2 b 
    ON a.app_id = b.app_id
  INNER JOIN table3 c 
    ON b.field_id = c.field_id 
) d
pivot
(
  max(field_value)
  for field_name in ([First Name], [Last Name], [DOB],
                     [Mailing Addr], [Zip])
) piv;

参见 SQL Fiddle with Demo 。

但是，如果 field_name 的值数量未知，那么您将需要实现动态 SQL 来获取结果:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(Field_name) 
                    from Table3
                    group by field_name, Field_id
                    order by Field_id
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT app_id,' + @cols + ' 
            from 
            (
              SELECT 
                 a.app_id, 
                 c.field_name,
                 b.field_value 
              FROM table1 a
              INNER JOIN table2 b 
                ON a.app_id = b.app_id
              INNER JOIN table3 c 
                ON b.field_id = c.field_id 
            ) x
            pivot 
            (
                max(field_value)
                for field_name in (' + @cols + ')
            ) p '

execute sp_executesql @query;

参见 SQL Fiddle with Demo 。这两者都会给出结果:

| APP_ID | FIRST NAME | LAST NAME |      DOB |    MAILING ADDR |   ZIP |
------------------------------------------------------------------------
|   1234 |        Joe |     Smith | 10/15/72 |     PO Box 1234 | 12345 |
|   1239 |        Bob |   Johnson | 12/01/78 | 1234 N Star Ave | 12456 |