Pivot query across 3 tables in SQL Server

Question

I can't get my head around a solution for the following problem: I have three tables (MS SQL):

Machines

+-----------+-------------+
| MachineID | MachineName |
+-----------+-------------+
|         1 | Press 1     |
|         2 | Press 2     |
|         3 | Press 3     |
+-----------+-------------+

Parts

+-----------+-------------+
| PartID    | PartName    |
+-----------+-------------+
|         1 | Part 1      |
|         2 | Part 2      |
|         3 | Part 3      |
+-----------+-------------+

MachinePartAssign

+----+-----------+--------+--+
| ID | MachineId | PartID |  |
+----+-----------+--------+--+
|  1 |         1 |      1 |  |
|  2 |         1 |      2 |  |
|  3 |         1 |      3 |  |
|  4 |         2 |      2 |  |
|  5 |         3 |      2 |  |
|  6 |         3 |      3 |  |
+----+-----------+--------+--+

And thats what i want to get as my query result: (If Machine and Part are assigned, which is the case when there is a matching row in MachinePartAssign its true (or 1), otherwise its should be false (or 0). I could also insert one row for every part / machine combination into MachinePartAssign and include an additional boolean (bit) column if that makes it easier. I would still need a similar pivot-query then. (?))

desired Result (true or false can be exchanged with 1 and 0 if that makes it easier)

+-----------+--------+--------+--------+
|           | Part 1 | Part 2 | Part 3 |
| Press 1   | true   | true   | true   |
| Press 2   | false  | true   | false  |
| Press 3   | false  | true   | true   |
+-----------+--------+--------+--------+

At the moment im doing this with a loop in c#: first selecting every machine, then all parts and afterwards selecting the MachinePartAssign for that specific machine/part combination. If i get >1 rows back its true. That means one query for every single machine / part.

Im sure theres a more elegant way for that. I know that MSSQL provides PIVOT-functionality but im not sure how to use that in my case.

Thanks a lot!

Answer 1

This is a projection, and you're always better doing projections in SQL. Because you've got a cell for every combination of Machine and Part, this is one of the rare occasions when you need a cross join, so...

SELECT m.MachineName, p.PartName, IIF(mpa.id is null, 0, 1)
FROM Machines m CROSS JOIN Parts p
LEFT JOIN MachinePartAssign mpa
ON m.MachineId = mpa.MachineId and p.PartId = mpa.PartId
ORDER BY m.MachineId, p.PartId

I think you're better off constructing the table in C# because the dynamic sql option to me is difficult to understand and maintain. Back in your app, you just need to loop through your result set and spit out a new line every time MachineId changes.

Here's the full working example on Rextester, what a cool tool!

Answer 2

Here is a dynamic version of the PIVOT function, in case you will have more than just 3 parts in your Parts table:

declare @cols_part as nvarchar(max), @query  as nvarchar(max)

select @cols_part = stuff((select distinct ',' + quotename(partname) from 
parts for xml path(''), type ).value('.', 'nvarchar(max)') ,1,1,'')

set @query = 'select machinename, ' + @cols_part + ' 
              from 
                (
                    select mp.machinename, mp.partname, iif(mpa.ID IS NULL, 
                    0, 1) as machinepart
                    from  ( select m.machineid, m.machinename, p.partid, 
                                   p.partname from machines m cross join 
                            parts p ) mp 
                    left join machinepartassign mpa on mp.machineid = 
                    mpa.machineid and mp.partid = mpa.partid

                ) x
              pivot ( max(machinepart) for partname in (' + @cols_part + ') 
            )p '
select @query -- to check the generated query
execute sp_executesql @query;

Since you need to have in the columns every part, even if there are no parts for a specific machine, I used a cross join between machines and parts and then a left join to the machinepartassign table, to find if there are any parts for a specific car and if so, display 1.

Here you can see the result of this query.

Answer 3

Maybe PIVOT like this

DECLARE @Parts AS TABLE (PartID  int,  PartName    varchar(30))
INSERT INTO @Parts VALUES
(1 ,'Part 1'),
(2 ,'Part 2'),
(3 ,'Part 3')

DECLARE @Machine AS TABLE( MachineID int,  MachineName varchar(30))
INSERT INTO @Machine VALUES (1, 'Press 1'), (2, 'Press 2'), (3, 'Press 3')

DECLARE @MachinePartAssign AS TABLE( ID int,  MachineId int,  PartID int  )
INSERT INTO @MachinePartAssign VALUES (1,1,1),(2,1,2),(3,1,3),(4,2,2),(5,3,2),(6,3,3)

SELECT MachineName, Isnull([Part 1],0) AS [Part 1], Isnull([Part 2],0) AS [Part 2], isnull([Part 3],0) AS [Part 3]
FROM
(
   SELECT m.MachineName, p.PartName, IIF(mpa.ID IS NULL, 0, 1) AS MachinePart FROM @Machine m
   LEFT JOIN @MachinePartAssign mpa ON m.MachineID = mpa.MachineId
   LEFT JOIN @Parts p ON p.PartID = mpa.PartID
) src
PIVOT
(
   max(MachinePart) FOR PartName IN ([Part 1], [Part 2], [Part 3] )
) pvt

Result of this query: In rextester

If table Part can be added other items, you could use dynamic sql query like this

CREATE TABLE #Parts (PartID  int,  PartName    varchar(30))
INSERT INTO #Parts VALUES
(1 ,'Part 1'),
(2 ,'Part 2'),
(3 ,'Part 3'),
(4 ,'Part 4')

CREATE TABLE #Machine ( MachineID int,  MachineName varchar(30))
INSERT INTO #Machine VALUES (1, 'Press 1'), (2, 'Press 2'), (3, 'Press 3')

CREATE TABLE #MachinePartAssign ( ID int,  MachineId int,  PartID int  )
INSERT INTO #MachinePartAssign VALUES (1,1,1),(2,1,2),(3,1,3),(4,2,2),(5,3,2),(6,3,3)

DECLARE @PivotColumns nvarchar(max) 
SELECT @PivotColumns = STUFF((SELECT concat(',[',p.PartName,']')  FROM #Parts p FOR XML PATH ('')),1,1,'')

DECLARE @HeaderColumns nvarchar(max)
SELECT @HeaderColumns = STUFF((SELECT concat(', ISNULL([',p.PartName,'],0) AS [', p.PartName,']')  FROM #Parts p FOR XML PATH ('')),1,1,'')

--SELECT @HeaderColumns, @PivotColumns

DECLARE @sql nvarchar(max) = CONCAT('
SELECT MachineName,', @HeaderColumns , 
' FROM
(
   SELECT m.MachineName, p.PartName, IIF(mpa.ID IS NULL, 0, 1) AS MachinePart FROM #Machine m
   LEFT JOIN #MachinePartAssign mpa ON m.MachineID = mpa.MachineId
   LEFT JOIN #Parts p ON p.PartID = mpa.PartID
) src
PIVOT
(
   max(MachinePart) FOR PartName IN (',@PivotColumns,')
) pvt'
)

-- PRINT @sql

EXEC(@sql)

DROP TABLE #Machine
DROP TABLE #Parts
DROP TABLE #MachinePartAssign

Link demo: Dynamic pivot

Answer 4

It also works if the pivot column and the valu colum is the same:

SELECT MachineName, [Part 1], [Part 2], [Part 3]
    FROM
    (
        SELECT m.MachineName, p.PartName
        FROM dbo.MachinePartAssign a
        INNER JOIN dbo.Parts p ON a.PartID = p.PartID
        INNER JOIN dbo.Machines m ON a.MachineID = m.MachineID
    ) x
    PIVOT
    (
        COUNT(PartName) FOR PartName IN ([Part 1], [Part 2], [Part 3])
    ) p