SQL Server 2008 垂直数据到水平

2022-01-22 00:00:00 pivot transpose sql-server

对于提交有关此主题的另一个问题，我深表歉意，但我已阅读了许多有关此问题的答案，但我似乎无法让它为我工作.

I apologize for submitting another question on this topic, but I've read through many of the answers on this and I can't seem to get it to work for me.

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

I have three tables I need to join and pull info on. One of the tables is only 3 columns and stores the data vertically. I would like to transpose that data to a horizontal format.

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

The data will look like this if I just join and pull:

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

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

Instead, I would like it to look like this:

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 语句需要很长时间.

In the past, I just resorted to looking up all the field_id's I needed in my data and created CASE statements for each one. The app the users are using contains data for multiple products, and each product contains different fields. Considering the number of products supported and the number of fields for each product (many, many more than the basic example I showed, above) it takes a long time to look them up and write out huge chunks of CASE statements.

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

I was wondering if there's some cheat-code out there to achieve what I need without having to look up the field_ids and writing things out. I know the PIVOT function is likely what I'm looking for, however, I can't seem to get it to work correctly.

觉得你们可以帮忙吗?

推荐答案

你可以使用PIVOT 函数将您的数据行转换为列.

You can use the PIVOT function to convert your rows of data into columns.

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

Your original query can be used to retrieve all the data, the only change I would make to it would be to exclude the column b.field_id because this will alter the final display of the result.

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

If you have a known list of field_name values that you want to turn into columns, then you can hard-code your query:

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 来获得结果:

But if you are going to have an unknown number of values for field_name, then you will need to implement dynamic SQL to get the result:

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.这两个都会给出一个结果:

See SQL Fiddle with Demo. Both of these this will give a result:

| 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 |

相关文章