UFN_PDCOMPOSITESEGMENTENTRIES_TOITEMLISTXML

CREATE function dbo.UFN_PDCOMPOSITESEGMENTENTRIES_TOITEMLISTXML(
  @PDACCOUNTSTRUCTUREID uniqueidentifier
)
returns xml
with execute as caller
as begin
  return (

    -- This is the poor man's pivot, I have to do this since I need to pivot two columns and the statement only supports one.

    -- This is faster than two pivots that are then joined together in case you were wondering.  

      select
            CSM.ID 
            ,max( case when CSTL.SEQUENCE = 1 then CSME.LONGDESCRIPTIONID end ) as ID1
            ,max( case when CSTL.SEQUENCE = 1 then CSMV.LONGDESCRIPTION end ) as LD1
            ,max( case when CSTL.SEQUENCE = 2 then CSME.LONGDESCRIPTIONID end ) as ID2
            ,max( case when CSTL.SEQUENCE = 2 then CSMV.LONGDESCRIPTION end ) as LD2
            ,max( case when CSTL.SEQUENCE = 3 then CSME.LONGDESCRIPTIONID end ) as ID3
            ,max( case when CSTL.SEQUENCE = 3 then CSMV.LONGDESCRIPTION end ) as LD3
            ,max( case when CSTL.SEQUENCE = 4 then CSME.LONGDESCRIPTIONID end ) as ID4
            ,max( case when CSTL.SEQUENCE = 4 then CSMV.LONGDESCRIPTION end ) as LD4
            ,CSM.NAME
            ,CSM.PDACCOUNTSEGMENTVALUEID as SEGMENTID
        from
            dbo.PDCOMPOSITESEGMENTMAPPING AS CSM
            inner join dbo.PDCOMPOSITESEGMENTMAPPINGENTRY as CSME
                on CSM.ID = CSME.PDCOMPOSITESEGMENTMAPPINGID
            inner join dbo.PDCOMPOSITESEGMENTTABLELIST as CSTL
                on CSTL.ID = CSME.PDCOMPOSITESEGMENTTABLELISTID
            inner join dbo.PDCOMPOSITESEGMENTMAPPINGVIEW as CSMV
                on CSMV.PDACCOUNTTABLESAVAILABLEFORSEGMENTID = CSTL.PDACCOUNTTABLESAVAILABLEFORSEGMENTID
                and CSMV.LONGDESCRIPTIONID = CSME.LONGDESCRIPTIONID
        where
            CSM.PDACCOUNTSTRUCTUREID = @PDACCOUNTSTRUCTUREID
        group by CSM.ID, name, PDACCOUNTSEGMENTVALUEID
        order by CSM.NAME
for xml raw('ITEM'),type,elements,root('MAPPINGS'),BINARY BASE64

  );  
end