Pivot Table with many to many table

Question

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

My attempt to do this returns null for all warehouses.

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

Taryn · Accepted Answer · 2013-09-10T18:27:01.043

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;

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;

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 |

the result is the other way around (it's `CustC` who is linked with all warehouses and `CustA` with `WH1` only). Anyway, I'm sure that's a small error and can be fixed easily. +1 for the dynamic approach; that's very useful. — Cristian Lupascu, Sep 10 '13 at 18:24
@w0lf fixed, I had fat-fingered/reversed the join columns. thanks — Taryn, Sep 10 '13 at 18:27

score 0 · Answer 2 · answered Sep 10 '13 at 18:22

Here's what I came up with after viewing the Complex PIVOT Example on this MSDN page:

SELECT
  CustomerName,
  case when [wh01] is null then null else 'wh01' end,
  case when [wh02] is null then null else 'wh02' end,
  case when [wh03] is null then null else 'wh03' end
FROM (
  SELECT
    c.Name AS CustomerName,
    cw.id AS cwid,
    w.name AS WarehouseName
  FROM Customer c
  JOIN CustomerWarehouse cw
    ON c.id = cw.customerId
  JOIN Warehouse w
    ON w.id = cw.warehouseId
) AS SourceTable
pivot (
  max(cwid)
  FOR WarehouseName IN (
      [wh01], [wh02], [wh03]
    )
) AS PivotTable

On SQLFiddle: http://sqlfiddle.com/#!3/d5c60/42

Pivot Table with many to many table

2 Answers2

Linked