c# - Pivot query across 3 tables in SQL Server -

i can't head around solution following problem: have 3 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 want query result: (if machine , part assigned, case when there matching row in machinepartassign true (or 1), otherwise should false (or 0). insert 1 row every part / machine combination machinepartassign , include additional boolean (bit) column if makes easier. still need similar pivot-query then. (?))

desired result (true or false can exchanged 1 , 0 if makes easier)

+-----------+--------+--------+--------+ |           | part 1 | part 2 | part 3 | | press 1   | true   | true   | true   | | press 2   | false  | true   | false  | | press 3   | false  | true   | true   | +-----------+--------+--------+--------+ 

at moment im doing loop in c#: first selecting every machine, parts , afterwards selecting machinepartassign specific machine/part combination. if >1 rows true. means 1 query every single machine / part.

im sure theres more elegant way that. know mssql provides pivot-functionality im not sure how use in case.

thanks lot!

here dynamic version of pivot function, in case have more 3 parts in parts table:

declare @cols_part nvarchar(max), @query  nvarchar(max)  select @cols_part = stuff((select distinct ',' + quotename(partname)  parts xml path(''), type ).value('.', 'nvarchar(max)') ,1,1,'')  set @query = 'select machinename, ' + @cols_part + '                                 (                     select mp.machinename, mp.partname, iif(mpa.id null,                      0, 1) machinepart                      ( select m.machineid, m.machinename, p.partid,                                     p.partname machines m cross join                              parts p ) mp                      left join machinepartassign mpa on mp.machineid =                      mpa.machineid , mp.partid = mpa.partid                  ) x               pivot ( max(machinepart) partname in (' + @cols_part + ')              )p ' select @query -- check generated query execute sp_executesql @query; 

since need have in columns every part, if there no parts specific machine, used cross join between machines , parts , left join machinepartassign table, find if there parts specific car , if so, display 1.

here can see result of query.