Pivot table with many tables

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 request to return client / storage 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 build my query to return data in the required format?

Answer 1

To get the result, you will want to join the tables and apply the PIVOT function. I also suggest using the row_number() window function to get the number of warehouses for each client - this will be the value that will be used as the new column headings.

 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, 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 the result:

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

Answer 2

Here is what I came up with after seeing 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 

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