SQL Server 2008:多个联接的列到行? [英] SQL Server 2008: Multiple joined columns to rows?

问题描述

有人试图从SQL Server中提取数据，使其更易于使用Excel格式.这是来自SQL Server的示例数据.注意:我将文字从我们的行业特定内容更改为汽车类比，但仅此而已:

Someone is trying to extract data from SQL Server into a format that is more Excel-manipulable. Here is sample data from SQL Server. Note: I changed the text from our industry-specific stuff to a car analogy, but that's all:

表格:产品

products_id | products_model
============================
100         | Saturn Vue
200         | Toyota Prius
300         | Ford Focus

表格:类别

categories_id | categories_name
===============================
1             | Leather Seats
2             | Heated Seats
3             | Tapedeck
4             | Heater
5             | Hybrid
6             | Sunroof
7             | Cruise Control

表:Products_Categories

products_id | categories_id
===========================
100         | 3
200         | 1
200         | 4
200         | 5
300         | 4
300         | 7

这是他们希望结果/输出看起来像的样子:

products_id | products_model | Leather Seats | Heated Seats | Tapedeck | Heater | Hybrid | Sunroof | Cruise Control
===================================================================================================================
100         | Saturn Vue     | N             | N            | Y        | N      | N      | N       | N
200         | Toyota Pruis   | Y             | N            | N        | Y      | Y      | N       | N
300         | Ford Focus     | N             | N            | N        | Y      | N      | N       | Y

我不知道如何使它工作.我尝试过与PIVOT一起玩，但是对我来说太复杂了.只要最终结果与上面的结果相同，我将接受任何类型的解决方案.

I can't figure out how to get it to work. I tried playing with PIVOT, but it got too complex for me. I'll accept any type of solution so long as the final result looks like the one above.

创建脚本位于 SQLFiddle 中.

推荐答案

您可以使用PIVOT函数来转换此数据:

You can use the PIVOT function to transform this data:

select products_id,
  products_model,
  Isnull([Leather Seats], 'N') [Leather Seats], 
  Isnull([Heated Seats], 'N') [Heated Seats],
  Isnull([Tapedeck], 'N') [Tapedeck], 
  Isnull([Heater], 'N') [Heater], 
  Isnull([Hybrid], 'N') [Hybrid], 
  Isnull([Sunroof], 'N') [Sunroof],
  Isnull([Cruise Control], 'N') [Cruise Control]
from
(
  select p.products_id,
    p.products_model,
    c.categories_name,
    'Y' flag
  from products p
  left join Products_Categories pc
    on p.products_id = pc.products_id
  left join Categories c
    on pc.categories_id = c.categories_id
) src
pivot
(
  max(flag)
  for categories_name in ([Leather Seats], [Heated Seats],
                          [Tapedeck], [Heater], 
                          [Hybrid], [Sunroof],
                          [Cruise Control])                        
) piv

请参见带演示的SQL提琴.

如果categories_name值未知或不固定，则可以使用动态sql:

If the categories_name values are unknown or not fixed, then you can use dynamic sql:

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

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(categories_name) 
                    from Categories
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

select @colsNull = STUFF((SELECT ',IsNull(' + QUOTENAME(categories_name)+', ''N'')'+' as '+QUOTENAME(categories_name) 
                    from Categories
                    group by categories_name, categories_id
                    order by categories_id
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT products_id,
                  products_model,' + @colsNull + ' from 
             (
                select p.products_id,
                  p.products_model,
                  c.categories_name,
                  ''Y'' flag
                from products p
                left join Products_Categories pc
                  on p.products_id = pc.products_id
                left join Categories c
                  on pc.categories_id = c.categories_id
            ) x
            pivot 
            (
                max(flag)
                for categories_name in (' + @cols + ')
            ) p '

execute(@query)

请参见带有演示的SQL提琴

这两个查询的结果是:

| PRODUCTS_ID | PRODUCTS_MODEL | LEATHER SEATS | HEATED SEATS | TAPEDECK | HEATER | HYBRID | SUNROOF | CRUISE CONTROL |
-----------------------------------------------------------------------------------------------------------------------
|         300 |     Ford Focus |             N |            N |        N |      Y |      N |       N |              Y |
|         100 |     Saturn Vue |             N |            N |        Y |      N |      N |       N |              N |
|         200 |   Toyota Prius |             Y |            N |        N |      Y |      Y |       N |              N |

这篇关于SQL Server 2008:多个联接的列到行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！