David Tunnell David Tunnell - 1 year ago 35
SQL Question

Taking JOINed data and making it a row instead of column

I'm working on a query that uses a join to pull some information.

SELECT ca.item_id
,ca.FIELD_ID
,ca.attr_val
,ca.upd_dtt
,ca.upd_usr
,mf.[ITEM_NAME]
FROM contract_attr ca
left JOIN mfr mf on ca.attr_val = mf.[ITEM_PK]


enter image description here

The Joined data is coming in as another column for the row. I want this column to be another row:

12, 0, PAR PHARMA, current_timestamp, ''

How do I do that?

Answer Source

I think you require Union all to get those records as rows:

    SELECT ca.item_id
        ,ca.FIELD_ID
        ,ca.attr_val
        ,ca.upd_dtt
        ,ca.upd_usr
    FROM contract_attr ca
    UNION ALL
    SELECT ... cols along with, --If no columns available in mfr then provide nulls accordingly before providing column name Item_Name
      ,mf.[ITEM_NAME]
    FROM mfr mf

Both columns list has to be identical order

With JOIN to finish question:

  SELECT ca.item_id
        ,ca.FIELD_ID
        ,ca.attr_val
        ,ca.upd_dtt
        ,ca.upd_usr
    FROM contract_attr ca
    UNION ALL
    SELECT ca.item_id,9999,mf.[ITEM_NAME],'',''
 FROM mfr mf
 JOIN contract_attr ca on ca.attr_val = mf.[ITEM_PK]
 Order by ca.item_id
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download