CriminyJickets CriminyJickets - 4 months ago 14
SQL Question

SQL How to select columns multiple times on one row based on value in other column

I need to create a report based on four tables as follows:

Table A: Columns(record_id, contact_id, role_id)

Table B: Columns(contact_id, fname, lname)

Table C: Columns(role_id,role_description)

Table D: Columns(Record_id, date)


Query:

select d.record_id,d.date,b.fname,b.lname,c.role_description
from a,b,c,d
where a.contact_id = b.contact_id and
a.role_id = c.role_id and
a.role_id in('legal','finance') and
a.record_id = d.record_id


This query will produce multiple rows as follows:

Record ID | Date | Fname | Lname | Role |
=============================================
1 |Jan 2 | Bob | Smith | Legal |
----------------------------------------------
1 |Jan 2 | Jim | Jones | Finance |
----------------------------------------------


What I need to produce is this:

-----------------------------------------------------------------------------
record ID | Date | Legal User | Finance User |
--------------------------------------------------------------------
1 | Jan 2 |Bob Smith | Jim Jones |


I understand joins and concatenation, but can't figure out how to get the 2 users on one row based on the role.

Answer
SELECT *
FROM
    (
       select d.record_id,d.date
          ,b.fname + b.lname AS name
          ,c.role_description + ' User' as role_description
       from a,b,c,d
       where a.contact_id = b.contact_id and
            a.role_id = c.role_id and
            a.role_id in('legal','finance') and
            a.record_id = d.record_id
    ) q
    PIVOT 
    (
       MAX(name)
       FOR role_description IN ([Legal User], [Finance User])
    )

You should be able to use PIVOT. I know it seems weird because you are taking MAX() of a varchar but it works to do so.

Per your comment for your specific syntax that is not simplified I would go with this:

SELECT *
FROM
    (
       select
          d.ctx_id
          ,effective_date,
          b.first_name + b.last_name AS name
          ,c.description
       from
          ctx_contract_contacts a
          INNER JOIN ctx_contacts b
          ON a.contact_id = b.contact_id
          INNER JOIN code_lookup c
          ON a.association = c.lookup_code 
          INNER JOIN ctx_basic_info d 
          ON a.ctx_id = d.ctx_id
       where
          and a.association in(117601,117759)
    ) q 

    PIVOT
    (
       MAX(name) 
       FOR description IN ([Legal Reviewer], [NDA reviewer])
    ) p
Comments