UFN_SALESORDER_TICKETS_FLAT

create function [dbo].[UFN_SALESORDER_TICKETS_FLAT](@SALESORDERID uniqueidentifier)
returns table
as
return
    (select TKTDESC1, TKTSTARTDT1, TKTSTARTTM1, TKTQTY1, TKTPRICE1, isnull(TKTDSC1,0.00) as TKTDSC1, isnull(TKTFEE1,0.00) as TKTFEE1, TKTTOTAL1,
TKTDESC2, TKTSTARTDT2, TKTSTARTTM2, TKTQTY2, TKTPRICE2, case when TKTDESC2 is null then null else isnull(TKTDSC2,0.00) end as TKTDSC2, case when TKTDESC2 is null then null else isnull(TKTFEE2,0.00) end as TKTFEE2, case when TKTDESC2 is null then null else TKTTOTAL2 end as TKTTOTAL2,
TKTDESC3, TKTSTARTDT3, TKTSTARTTM3, TKTQTY3, TKTPRICE3, case when TKTDESC3 is null then null else isnull(TKTDSC3,0.00) end as TKTDSC3, case when TKTDESC3 is null then null else isnull(TKTFEE3,0.00) end as TKTFEE3, case when TKTDESC3 is null then null else TKTTOTAL3 end as TKTTOTAL3,
TKTDESC4, TKTSTARTDT4, TKTSTARTTM4, TKTQTY4, TKTPRICE4, case when TKTDESC4 is null then null else isnull(TKTDSC4,0.00) end as TKTDSC4, case when TKTDESC4 is null then null else isnull(TKTFEE4,0.00) end as TKTFEE4, case when TKTDESC4 is null then null else TKTTOTAL4 end as TKTTOTAL4,
TKTDESCREST, TKTQTYREST, TKTPRICEREST, case when TKTDESCREST is null then null else isnull(TKTDSCREST,0.00) end as TKTDSCREST, case when TKTDESCREST is null then null else isnull(TKTFEEREST,0.00) end as TKTFEEREST, case when TKTDESCREST is null then null else TKTTOTALREST end as TKTTOTALREST
from
    (select max(case RowNum when 1 then V1.DESCRIPTION else null end) as TKTDESC1,
        max(case RowNum when 1 then V1.STARTDATE else null end) as TKTSTARTDT1,
        max(case RowNum when 1 then substring(convert(varchar,convert(datetime,stuff(V1.STARTTIME,3,0,':')),100),12,8) else null end) as TKTSTARTTM1,
        sum(case RowNum when 1 then V1.QUANTITY else null end) as TKTQTY1,
        sum(case RowNum when 1 then V1.PRICE else null end) as TKTPRICE1,
        sum(case RowNum when 1 then V1.DISCOUNTS else null end) as TKTDSC1,
        sum(case RowNum when 1 then V1.FEES else null end) as TKTFEE1,
        sum(case RowNum when 1 then V1.TOTAL else null end) as TKTTOTAL1,
        max(case RowNum when 2 then V1.DESCRIPTION else null end) as TKTDESC2,
        max(case RowNum when 2 then V1.STARTDATE else null end) as TKTSTARTDT2,
        max(case RowNum when 2 then substring(convert(varchar,convert(datetime,stuff(V1.STARTTIME,3,0,':')),100),12,8) else null end) as TKTSTARTTM2,
        sum(case RowNum when 2 then V1.QUANTITY else null end) as TKTQTY2,
        sum(case RowNum when 2 then V1.PRICE else null end) as TKTPRICE2,
        sum(case RowNum when 2 then V1.DISCOUNTS else null end) as TKTDSC2,
        sum(case RowNum when 2 then V1.FEES else null end) as TKTFEE2,
        sum(case RowNum when 2 then V1.TOTAL else null end) as TKTTOTAL2,
        max(case RowNum when 3 then V1.DESCRIPTION else null end) as TKTDESC3,
        max(case RowNum when 3 then V1.STARTDATE else null end) as TKTSTARTDT3,
        max(case RowNum when 3 then substring(convert(varchar,convert(datetime,stuff(V1.STARTTIME,3,0,':')),100),12,8) else null end) as TKTSTARTTM3,
        sum(case RowNum when 3 then V1.QUANTITY else null end) as TKTQTY3,
        sum(case RowNum when 3 then V1.PRICE else null end) as TKTPRICE3,
        sum(case RowNum when 3 then V1.DISCOUNTS else null end) as TKTDSC3,
        sum(case RowNum when 3 then V1.FEES else null end) as TKTFEE3,
        sum(case RowNum when 3 then V1.TOTAL else null end) as TKTTOTAL3,
        max(case RowNum when 4 then V1.DESCRIPTION else null end) as TKTDESC4,
        max(case RowNum when 4 then V1.STARTDATE else null end) as TKTSTARTDT4,
        max(case RowNum when 4 then substring(convert(varchar,convert(datetime,stuff(V1.STARTTIME,3,0,':')),100),12,8) else null end) as TKTSTARTTM4,
        sum(case RowNum when 4 then V1.QUANTITY else null end) as TKTQTY4,
        sum(case RowNum when 4 then V1.PRICE else null end) as TKTPRICE4,
        sum(case RowNum when 4 then V1.DISCOUNTS else null end) as TKTDSC4,
        sum(case RowNum when 4 then V1.FEES else null end) as TKTFEE4,
        sum(case RowNum when 4 then V1.TOTAL else null end) as TKTTOTAL4,
        max(case when RowNum > 4 then 'Other tickets' else null end) as TKTDESCREST,
        sum(case when RowNum > 4 then V1.QUANTITY else null end) as TKTQTYREST,
        null as TKTPRICEREST,
        sum(case when RowNum > 4 then V1.DISCOUNTS else null end) as TKTDSCREST,
        sum(case when RowNum > 4 then V1.FEES else null end) as TKTFEEREST,
        sum(case when RowNum > 4 then V1.TOTAL else null end) as TKTTOTALREST
    from
        (select DESCRIPTION, STARTDATE, STARTTIME, QUANTITY, PRICE, DISCOUNTS, FEES, TOTAL, row_number() over (order by TOTAL desc) as RowNum
        from dbo.UFN_SALESORDER_TICKETS(@SALESORDERID)) V1 ) V2
    )