具有多对多表的数据透视表 [英] Pivot Table with many to many table

问题描述

我的SQL提琴在这里: http://sqlfiddle.com/#!3/d5c60

My SQL Fiddle is here: http://sqlfiddle.com/#!3/d5c60

CREATE TABLE customer 
    (
     id int identity primary key, 
     name varchar(20), 
    );

CREATE TABLE warehouse 
    (
     id int identity primary key, 
     name varchar(20), 
    );

CREATE TABLE customerwarehouse 
    (
     id int identity primary key, 
     customerid int,
      warehouseid int
    );

INSERT INTO customer (name) 
VALUES
('CustA'),
('CustB'),
('CustC');

INSERT INTO warehouse (name) 
VALUES
('wh01'),
('wh02'),
('wh03');

INSERT INTO customerwarehouse (customerid, warehouseid)
VALUES
(1,1),
(2,1),
(2,2),
(3,1),
(3,2),
(3,3);

我想编写一个查询来以以下格式返回客户/仓库数据:

I would like to write a query to return the customer/warehouse data in the following format:

Customer    WH1    WH2    WH3
CustA       wh01    
CustB       wh01   wh02
CustC       wh01   wh02   wh03

我这样做的尝试对于所有仓库都返回null.

My attempt to do this returns null for all warehouses.

如何构造查询以所需格式返回数据?

How can I construct my query to return the data in the required format?

推荐答案

为了获得结果，您将需要加入表并应用PIVOT函数.我还建议使用row_number()窗口函数获取每个客户的仓库数量-这将用作新列标题的值.

In order to get the result, you will want to JOIN the tables and apply the PIVOT function. I would also suggest using the row_number() windowing function to get the number of warehouses for each customer - this will be the value that will be used as your new column headers.

select customername, wh1, wh2, wh3
from
(
  select w.name warehousename,
    c.name customername,
    'wh'+cast(row_number() over(partition by c.id
                                order by w.id) as varchar(10)) seq
  from customer c
  inner join customerwarehouse cw
    on c.id = cw.customerid
  inner join warehouse w
    on cw.warehouseid = w.id
) d
pivot
(
  max(warehousename)
  for seq in (wh1, wh2, wh3)
) piv;

请参见带演示的SQL小提琴.如果您有未知数量的值，则需要使用动态SQL来获取结果:

See SQL Fiddle with Demo. If you have an unknown number of values, then you will need to use dynamic SQL to get the result:

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

select @cols = STUFF((SELECT distinct ',' + QUOTENAME('wh'+cast(row_number() over(partition by customerid
                                                                                  order by warehouseid) as varchar(10))) 
                    from customerwarehouse
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT customername, ' + @cols + ' 
            from 
            (
                select w.name warehousename,
                  c.name customername,
                  ''wh''+cast(row_number() over(partition by c.id
                                              order by w.id) as varchar(10)) seq
                from customer c
                inner join customerwarehouse cw
                  on c.id = cw.customerid
                inner join warehouse w
                  on cw.warehouseid = w.id
            ) x
            pivot 
            (
                max(warehousename)
                for seq in (' + @cols + ')
            ) p '

execute sp_executesql @query;

请参见带演示的SQL小提琴.两者都给出结果:

See SQL Fiddle with Demo. Both give a result:

| CUSTOMERNAME |  WH1 |    WH2 |    WH3 |
|        CustA | wh01 | (null) | (null) |
|        CustB | wh01 |   wh02 | (null) |
|        CustC | wh01 |   wh02 |   wh03 |

这篇关于具有多对多表的数据透视表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！