Sales View Transform

SELECT
    [FormattedData]
FROM
(
    --Get our headers from the View, assuming header data is in first row for any given TranNumber as data looks to be denormalized
    SELECT
        [TranNumber],
        '00' + CAST([TranNumber] AS varchar(10)) + CAST([TranTotal] AS varchar(30)) AS [FormattedData],
        0 AS [LineType] -- Zero to identify line as a header
    FROM
    (
        SELECT TranNumber, TranTotal, ROW_NUMBER() OVER(ORDER BY TranNumber) AS [RowNumber]
        FROM SalesView
    ) HEADER
    WHERE HEADER.[RowNumber] = 1


    UNION ALL


    --Get our repeating details rows
    SELECT
        [TranNumber],
        '01' + [Item] + CAST([Quantity] AS varchar(30)) + CAST([Price] AS varhchar(30)) AS [FormattedData],
        1 AS [LineType] -- 1 to identify line as details
    FROM SalesView


    UNION ALL


    --Get our Footers, again assuming Location is denormalized and repeated for each details row
    SELECT
        [TranNumber],
        '02' + CAST(Location AS varchar(10)) AS [FormattedData],
        2 AS [LineType] --2 to identify line as footer
    FROM
    (
        SELECT TranNumber, Location, ROW_NUMBER() OVER(ORDER BY TranNumber) AS [RowNumber]
        FROM SalesView
    ) FOOTER
    WHERE FOOTER.[RowNumber] = 1
) SALESEXPORT
ORDER BY [TranNumber], [LineType] --put the line types in order by their TranNumber