SQL Server : joining rows in one table to columns in another and displaying fields multiple times -

in microsoft sql server 2012 database have 2 tables. i’ve got in queries keep simple.

select * pricebook_criteria [part no] = 'saw312'   +---------+---------+-----------------------+------------------------+----------+ | part no | brandid | standarddescriptionid |        criteria        |  value   | +---------+---------+-----------------------+------------------------+----------+ | saw312  |   15356 |                    82 | diameter [mm]          | 257      | | saw312  |   15356 |                    82 | height [mm]            | 45       | | saw312  |   15356 |                    82 | thickness [mm]         | 26       | | saw312  |   15356 |                    82 | minimum thickness [mm] | 24       | | saw312  |   15356 |                    82 | inner diameter [mm]    | 61       | | saw312  |   15356 |                    82 | weight [kg]            | 6.5      | | saw312  |   15356 |                    82 | number of holes        | 4        | | saw312  |   15356 |                    82 | class                  | standard | | saw312  |   15356 |                    82 | disc vent              | standard | +---------+---------+-----------------------+------------------------+----------+ 

query:

select *  selected_criteria standarddescriptionid = 82  +-----------------------+-------------------+-------------------+ | standarddescriptionid | selectedcriteria1 | selectedcriteria2 | +-----------------------+-------------------+-------------------+ |                    82 | disc vent         | number of holes   | +-----------------------+-------------------+-------------------+ 

what i’m trying somehow join selected_criteria , pricebook_criteria , return following:

+---------+---------+-----------------------+-------------------+----------------+-------------------+----------------+ | part no | brandid | standarddescriptionid | selectedcriteria1 | selectedvalue1 | selectedcriteria2 | selectedvalue2 | +---------+---------+-----------------------+-------------------+----------------+-------------------+----------------+ | saw312  |   15356 |                    82 | disc vent         | standard       | number of holes   |              4 | +---------+---------+-----------------------+-------------------+----------------+-------------------+----------------+ 

so idea standarddescriptionid = 82 have picked 2 criteria selectedcriteria1 , selectedcriteria2. based on data have in pricebook_criteria we’d display this. i’m trying achieve is:

• no duplicated rows. [part no], brandid, standarddescriptionid combination unique, there should 1 line each
• the 1st , 2nd selected criteria , respective values in correct order displayed columns

i've gotten pretty close…. not quite there:

select *  pricebook_criteria  join selected_criteria criteria1 on (pricebook_criteria.standarddescriptionid = criteria1.standarddescriptionid , ((pricebook_criteria.criteria = criteria1.selectedcriteria1) or (pricebook_criteria.criteria = criteria1.selectedcriteria2))) [part no] = 'saw312'  +---------+---------+-----------------+----------+-----------------------+-----------------------+-------------------+-------------------+ | part no | brandid |    criteria     |  value   | standarddescriptionid | standarddescriptionid | selectedcriteria1 | selectedcriteria2 | +---------+---------+-----------------+----------+-----------------------+-----------------------+-------------------+-------------------+ | saw312  |   15356 | number of holes | 4        |                    82 |                    82 | disc vent         | number of holes   | | saw312  |   15356 | disc vent       | standard |                    82 |                    82 | disc vent         | number of holes   | +---------+---------+-----------------+----------+-----------------------+-----------------------+-------------------+-------------------+ 

the problem have i’ve duplicated part each criteria, need on 1 line example above.

how following:

select     pc1.[part no], pc1.brandid, pc1.standarddescriptionid  ,selectedcriteria1, pc1.value, selectedcriteria2, pc2.value selected_criteria sc inner join pricebook_criteria pc1 on (pc1.standarddescriptionid = sc.standarddescriptionid      , pc1.criteria = sc.selectedcriteria1) inner join pricebook_criteria pc2  on (pc2.standarddescriptionid = sc.standarddescriptionid     , pc2.criteria = sc.selectedcriteria2) pc1.[part no] = 'saw312' 

not sure if need pc2.[part no] = 'saw312' or if dependent on standarddescriptionid