SQL Kind of Pivot

  • 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

  • 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

  • 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