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:
Table: Products
products_id | products_model
============================
100 | Saturn Vue
200 | Toyota Prius
300 | Ford Focus
Table: Categories
categories_id | categories_name
===============================
1 | Leather Seats
2 | Heated Seats
3 | Tapedeck
4 | Heater
5 | Hybrid
6 | Sunroof
7 | Cruise Control
Table: Products_Categories
products_id | categories_id
===========================
100 | 3
200 | 1
200 | 4
200 | 5
300 | 4
300 | 7
This is what they want the results/output to look like:
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
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.
The create script is in SQLFiddle.
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
See SQL Fiddle with Demo.
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)
See SQL Fiddle with Demo
The result of both queries is:
| 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 |
easier to understand version
select distinct
p.products_id
,p.products_model
,case when pc_ls.products_id is null then 'N' else 'Y' end as 'Leather Seats'
,case when pc_hs.products_id is null then 'N' else 'Y' end as 'Heated Seats'
,case when pc_td.products_id is null then 'N' else 'Y' end as 'Tapedeck'
,case when pc_ht.products_id is null then 'N' else 'Y' end as 'Heater'
,case when pc_hy.products_id is null then 'N' else 'Y' end as 'Hybrid'
,case when pc_sr.products_id is null then 'N' else 'Y' end as 'Sunroof'
,case when pc_cc.products_id is null then 'N' else 'Y' end as 'Cruise Control'
from products p
left join products_categories pc_ls on pc_ls.products_id = p.products_id and pc_ls.categories_id= 1
left join products_categories pc_hs on pc_hs.products_id = p.products_id and pc_hs.categories_id= 2
left join products_categories pc_td on pc_td.products_id = p.products_id and pc_td.categories_id= 3
left join products_categories pc_ht on pc_ht.products_id = p.products_id and pc_ht.categories_id= 4
left join products_categories pc_hy on pc_hy.products_id = p.products_id and pc_hy.categories_id= 5
left join products_categories pc_sr on pc_sr.products_id = p.products_id and pc_sr.categories_id= 6
left join products_categories pc_cc on pc_cc.products_id = p.products_id and pc_cc.categories_id= 7
