Link to home
Start Free TrialLog in
Avatar of Denis Orozco
Denis OrozcoFlag for United States of America

asked on

Pivot a SQL statement

I need to turn a query into a pivot table
Select S.COMP_TYPE, S.SUB_TYPE,  S.DESCRIPTION, I.DESCRIPTION 
FROM TB_Subtypes S LEFT OUTER JOIN DV_ITEMS I ON S.COMP_TYPE = I.COMP_TYPE AND S.SUB_TYPE = I.SUB_TYPE
WHERE S.COMP_TYPE IN (Select COMP_TYPE FROM DV_Main WHERE (((DV_Main.CUR_VER_FLAG)= 'Y') AND ((DV_Main.APD_FLAG) = 'P'))AND DV_Main.Department = 'BUILDING')
AND I.RECORD_TYPE ='RV'
ORDER BY S.SUB_TYPE

Open in new window


The column I.DESCRIPTION contains the values that i need to turn into columns. How can I achieve this?
Here is an exmaple of the data: (very small)
MECHANIC      ACWESUDI                                                AC WELL - SUPPLY, DISCHARGE, ETC      BLDG-ELECTRICAL-PLAN REVIEW
MECHANIC      ACWESUDI                                                AC WELL - SUPPLY, DISCHARGE, ETC      BLDG-MECHANICAL-PLAN REVIEW
MECHANIC      ACWESUDI                                                AC WELL - SUPPLY, DISCHARGE, ETC      BLDG-PLUMBING-PLAN REVIEW

I need to turn it into one single record that reads like this:
MECHANIC      ACWESUDI                                                AC WELL - SUPPLY, DISCHARGE, ETC      BLDG-ELECTRICAL-PLAN REVIEW   BLDG-MECHANICAL-PLAN REVIEW  BLDG-PLUMBING-PLAN REVIEW    

Keep in mind that not all the records have 3 rows some of them might have 6 or even 10
How then can i achieve this?
Thanks!
ASKER CERTIFIED SOLUTION
Avatar of PortletPaul
PortletPaul
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
As much as I would love to write a pivot, it doesnt seem to be a pivot problem.

You arent aggregating anything, so it is a string concatenation....

If on SQL2017 or later, use STRING_AGG()    : https://docs.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-2017

Otherwise, FOR XML will work, or, could use a procedure/function....

First, using a CTE to expose the current results, we can use that CTE to then format the rows into columns....It also seems that the DV_ITEMS.DESCRIPTION elements are delimited by space(s)
;with cte_types as
( Select S.COMP_TYPE, S.SUB_TYPE,  S.DESCRIPTION, I.DESCRIPTION as IDESC 
  FROM TB_Subtypes S 
  LEFT OUTER JOIN DV_ITEMS I ON S.COMP_TYPE = I.COMP_TYPE AND S.SUB_TYPE = I.SUB_TYPE
  WHERE S.COMP_TYPE IN (Select COMP_TYPE FROM DV_Main WHERE (((DV_Main.CUR_VER_FLAG)= 'Y') AND ((DV_Main.APD_FLAG) = 'P'))AND DV_Main.Department = 'BUILDING')
  AND I.RECORD_TYPE ='RV'
) select distinct C.COMP_TYPE, C.SUB_TYPE,  C.DESCRIPTION, ltrim(DV.ITEMS) as DV_ITEMS
  from cte_types C
  cross apply (select '  '+ IDESC from cte_types t where C.COMP_TYPE = t.comp_type and C.SUB_TYPE = t.sub_type and C.DESCRIPTION = t.description for xml path('')) DV(ITEMS)    
  order by c.sub_type

Open in new window


Please let us know what version of SQL you are using.
 
And if any problems understanding the code above, or, use of spaces as delimiter....
dang it, it was string_agg() I meant to refer to .... please ignore my earlier reference to concat_ws

But this comment allows me to note that you have a where clause condition that overrides the left join:

    AND I.RECORD_TYPE ='RV'

This requires that every row MUST have that record type and therefore it is the equivalent of an INNER join. If you really need the left join then that filtering condition needs to permit no row at all from "I" e.g.

    AND ( I.RECORD_TYPE ='RV' or I.RECORD_TYPE IS NULL )

I missed the signifigance of this predicate in my original reply, so  -instead - include it in the apply:
SELECT
    s.COMP_TYPE
  , s.SUB_TYPE
  , s.DESCRIPTION
  , ca.Notes
FROM TB_Subtypes s
    OUTER APPLY (
        SELECT
        STUFF((
              SELECT
                    ', ' + i.DESCRIPTION
               FROM [DV_ITEMS] AS i
              WHERE s.COMP_TYPE = i.COMP_TYPE
              AND i.RECORD_TYPE = 'RV'
              ORDER BY i.DESCRIPTION
              FOR XML PATH ('')
              )
             , 1, 1, '')
         ) ca (Notes)
WHERE s.COMP_TYPE IN (
    SELECT
        COMP_TYPE
    FROM DV_Main
    WHERE DV_Main.CUR_VER_FLAG = 'Y'
    AND DV_Main.APD_FLAG = 'P'
    AND DV_Main.Department = 'BUILDING'
    )
ORDER BY
    s.SUB_TYPE

Open in new window

Note; if you don't really need the effect of a left join, just change OUTER APPLY to CROSS APPLY and it will behave in a manner similar to an inner join.

Please note that you don't need "select distinct" in the approach above.

If it happens you do have access to string_agg() then the query would look like this:
SELECT
    s.COMP_TYPE
  , s.SUB_TYPE
  , s.DESCRIPTION
  , string_agg(i.DESCRIPTION, ',') AS notes
FROM TB_Subtypes s
LEFT OUTER JOIN DV_ITEMS i ON s.COMP_TYPE = i.COMP_TYPE
    AND s.SUB_TYPE = i.SUB_TYPE
    AND i.RECORD_TYPE = 'RV'
WHERE s.COMP_TYPE IN (
    SELECT
        COMP_TYPE
    FROM DV_Main
    WHERE (((DV_Main.CUR_VER_FLAG) = 'Y')
    AND ((DV_Main.APD_FLAG) = 'P'))
    AND DV_Main.Department = 'BUILDING'
)
GROUP BY
    s.COMP_TYPE
  , s.SUB_TYPE
  , s.DESCRIPTION
ORDER BY
    s.SUB_TYPE

Open in new window

anf here, if you don't need the effect of the left join change it to an inner join.
@PortletPaul,

You're right about "AND I.RECORD_TYPE ='RV'" essentially making it an inner join.  Thats why I used CROSS APPLY....


Upon reflection, I would be inclined to include in the join criteria :

LEFT OUTER JOIN DV_ITEMS I ON S.COMP_TYPE = I.COMP_TYPE AND S.SUB_TYPE = I.SUB_TYPE AND I.RECORD_TYPE ='RV'

Then if you want to report the columns from  TB_Subtypes regardless of  existence  in DV_ITEMS, you still can.

So the question then becomes LEFT OUTER, or, INNER join.

I do have an uneasiness with :
WHERE S.COMP_TYPE IN (Select COMP_TYPE FROM DV_Main WHERE (((DV_Main.CUR_VER_FLAG)= 'Y') AND ((DV_Main.APD_FLAG) = 'P'))AND DV_Main.Department = 'BUILDING')

-- AND WOULD PREFER TO SEE

WHERE EXISTS  (Select NULL FROM DV_Main M WHERE M.COMP_TYPE = S.COMP_TYPE AND M.CUR_VER_FLAG= 'Y' AND M.APD_FLAG = 'P' AND M.Department = 'BUILDING')

Open in new window

Checking EXISTS is more efficient than returning a set of values and  testing if s.comp_type is one of them.

But looking at the SELECT, it could probably be written as
SELECT S.COMP_TYPE, S.SUB_TYPE,  S.DESCRIPTION, I.DESCRIPTION as IDESC 
FROM TB_Subtypes S 
INNER JOIN DV_Main M ON M.COMP_TYPE = S.COMP_TYPE AND M.CUR_VER_FLAG = 'Y' AND M.APD_FLAG = 'P' AND M.Department = 'BUILDING'
INNER JOIN DV_ITEMS I ON I.COMP_TYPE = M.COMP_TYPE AND I.SUB_TYPE = S.SUB_TYPE AND I.RECORD_TYPE ='RV'
ORDER BY S.SUB_TYPE

Open in new window

Might return more rows, but the dependencies seems to flow a bit better. Imagining that there is a relationship between DV_Main and DV_ITEMS to help legitimise the DV_ITEMS selections..., And depending on indexing, would probably be more efficient...

The *nice* aspect of the CTE is it doent change much. Just that base query....
;with cte_types as
( SELECT S.COMP_TYPE, S.SUB_TYPE,  S.DESCRIPTION, I.DESCRIPTION as IDESC 
  FROM TB_Subtypes S 
  INNER JOIN DV_Main M ON M.COMP_TYPE = S.COMP_TYPE AND M.CUR_VER_FLAG = 'Y' AND M.APD_FLAG = 'P' AND M.Department = 'BUILDING'
  INNER JOIN DV_ITEMS I ON I.COMP_TYPE = M.COMP_TYPE AND I.SUB_TYPE = S.SUB_TYPE AND I.RECORD_TYPE ='RV'
) select distinct C.COMP_TYPE, C.SUB_TYPE,  C.DESCRIPTION, ltrim(DV.ITEMS) as DV_ITEMS
  from cte_types C
  cross apply (select '  '+ IDESC from cte_types t where C.COMP_TYPE = t.comp_type and C.SUB_TYPE = t.sub_type and C.DESCRIPTION = t.description for xml path('')) DV(ITEMS)    
  ORDER BY C.SUB_TYPE

Open in new window

Hello ?