SQL Server 2008垂直数据转为水平数据

Question

SQL Server 2008垂直数据转为水平数据

16

非常抱歉在此话题上提交另一个问题，但我已经阅读了许多答案，似乎对我没有用。

我有三个表格需要连接并提取信息。其中一个表格仅具有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函数可能是我要寻找的，但我似乎无法使其正常工作。

你们能帮忙吗？

- Fill in the Blank

5个回答

3

试试这个

SELECT 
    [app_id]
    ,MAX([First Name]) AS [First Name]
    ,MAX([Last Name]) AS [Last Name]
    ,MAX([DOB]) AS [DOB]
    ,MAX([Mailing Addr]) AS [Mailing Addr]
    ,MAX([Zip]) AS [Zip] 
FROM Table1
PIVOT
(
    MAX([field_value]) FOR [field_name] IN ([First Name],[Last Name],[DOB],[Mailing Addr],[Zip])
) T
GROUP BY [app_id]

SQL FIDDLE DEMO

- bvr

0

bluefeet的回答对我来说是正确的，但我需要在列清单上使用distinct：

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

select @cols = STUFF((SELECT Distinct ',' + QUOTENAME(Field_name) 
                from Table3
                group by field_name, Field_id
                order by ',' + QUOTENAME(Field_name) 
        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;

- ScottFoster1000

0

可以使用GROUP BY和MAX函数来解决，而不是使用PIVOT：

 SELECT PK_ID, MAX(PHONE) AS PHONE, MAX(MAIL) AS MAIL

 FROM ( 
    
    SELECT 
                        PK_ID,
                        CASE  
                          WHEN CONTACT_ALIAS.CONTACT_TYPE = 'COMPANY'  THEN   CONTACT_ALIAS.CONTACT_VALUE
                        END AS PHONE ,
                        
                        CASE  
                          WHEN CONTACT_ALIAS.CONTACT_TYPE = 'BUSINESS'  THEN   CONTACT_ALIAS.CONTACT_VALUE
                        END AS MAIL 
                
                    FROM  T_CONTACT_EMPLOYERS  CONTACT_ALIAS
                    WHERE CONTACT_ALIAS.CONTACT_TYPE IN ('COMPANY' , 'BUSINESS') 
     ) TEMP
                 GROUP BY PK_ID

- Tiago Medici

-1

使用 SQL Pivot

   SELECT [Id], [FirstName], [LastName], [Email] 
FROM
(
 SELECT Id, Att_Id, Att_Value FROM VerticalTable
) as source
PIVOT
(
 MAX(Att_Value) FOR Att_Id IN ([FirstName], [LastName], [Email])
) as target

- Code

你能添加一个简短的描述吗？ - RtmY

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Taryn · Accepted Answer

您可以使用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。

但是如果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。这两个都会给出结果：

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