June 5, 2015 at 10:45 am
Hi
I have 3 tables, a supplier table, a types table and a relationship table between the two...
I want to build a query that put the different types in columns, and use a Boolean value to identify if the supplier supplies that type.
CREATE TABLE #Suppliers(
id INT,
nam NVARCHAR(50)
)
CREATE TABLE #Types(
id INT,
nam NVARCHAR(50)
)
CREATE TABLE #Relation(
idS INT,
idN INT
)
INSERT INTO #Suppliers
SELECT 1,'Supplier1' UNION ALL
SELECT 2,'Supplier2' UNION ALL
SELECT 3,'Supplier3'
INSERT INTO #Types
SELECT 1,'Onions' UNION ALL
SELECT 2,'Potatos' UNION ALL
SELECT 3,'Chocolate'
INSERT INTO #Relation
SELECT 1,1 UNION ALL
SELECT 1,2 UNION ALL
SELECT 1,3 UNION ALL
SELECT 2,1 UNION ALL
SELECT 2,2 UNION ALL
SELECT 3,1
The header should be: Supplier Name, Onions, Potatos, Chocolate
It's possible to do this in "one" query?
Thanks
June 5, 2015 at 11:32 am
PIVOT will produce your results, provided that you are willing to specify the type names when you write the query:
SELECT [Supplier Name], Onions, Potatos, Chocolate
FROM
(SELECT s.nam AS [Supplier Name], t.Nam AS [TypeName], t.id AS [TypeID]
FROM #Suppliers s INNER JOIN
#Relation r ON s.id = r.idS INNER JOIN
#Types t ON t.id = r.idN
) AS src
PIVOT (COUNT([TypeID]) FOR [TypeName] IN (Onions, Potatos, Chocolate)) AS pvt
ORDER BY [Supplier Name]
If you want the column list to be driven by the contents of the type table, then you just need to write that query dynamically by selecting out of the type list (be mindful of data lengths):
DECLARE @sql varchar(1000) = N'SELECT [Supplier Name] ', @TList varchar(1000) = '';
SELECT @TList = @TList + ', [' + Nam + ']' FROM #Types;
SELECT @sql += @TList + '
FROM
(SELECT s.nam AS [Supplier Name], t.Nam AS [TypeName], t.id AS [TypeID]
FROM #Suppliers s INNER JOIN
#Relation r ON s.id = r.idS INNER JOIN
#Types t ON t.id = r.idN
) AS src
PIVOT (COUNT([TypeID]) FOR [TypeName] IN (' + RIGHT(@TList, LEN(@TList) - 2) + ')) AS pvt
ORDER BY [Supplier Name]';
print @sql;
exec(@sql);
Eddie Wuerch
MCM: SQL
June 8, 2015 at 5:32 am
Me and the PIVOT function... :doze:
Thanks works great.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply