Multi-column pivot table help

  • I have the following data in [table_1]:

    last_updated  type_desc  size_bytes  used_bytes
    2019-05-23 LOG 1835008 532480
    2019-05-23 ROWS 1153695744 1132855296
    2019-05-24 LOG 1835008 557056
    2019-05-24 ROWS 1153695744 1132855296
    2019-05-25 LOG 1835008 663552
    2019-05-25 ROWS 1153695744 1132855296
    etc.

    which I'd like to display as:

    last_updated  ROWS_size   ROWS_used   LOG_size  LOG_used
    2019-05-23 1153695744 1132855296 1835008 532480
    2019-05-24 1153695744 1132855296 1835008 557056
    2019-05-25 1153695744 1132855296 1835008 663552

    but I can't get it to work without using intermediate (horrible) CTE:

    WITH
        [raw_size]
    AS (
        SELECT
            [last_updated],
            [type_desc] + '_size' AS [type_desc],
            [size_bytes] AS [bytes]
        FROM
           [table_1]
    ),
        [raw_used]
    AS (
        SELECT
          [last_updated],
          [type_desc] + '_used' AS [type_desc],
          [used_bytes] AS [bytes]
        FROM
           [table_1]
    ),
        [raw]
    AS (
        SELECT * FROM [raw_size]
        UNION ALL
        SELECT * FROM [raw_used]
    )
    SELECT
        FORMAT([last_updated], 'dd-MM-yyyy') AS [timestamp],
        [ROWS_size],
        [ROWS_used],
        [LOG_size],
        [LOG_used]
    FROM
        [raw]
    PIVOT (
        MIN([bytes])
        FOR [type_desc] IN ([ROWS_size], [ROWS_used], [LOG_size], [LOG_used])
    ) AS [pt]
    ORDER BY
        [last_updated] ASC

    Hopefully there's a better way to achieve this. Thanks.

  • CREATE TABLE #table_1 (
    last_updated date NOT NULL,
    type_desc varchar(30) NULL,
    size_bytes bigint NULL,
    used_bytes bigint NULL
    )
    INSERT INTO #table_1 VALUES
    ('2019-05-23', 'LOG', 1835008 , 532480),
    ('2019-05-23', 'ROWS', 1153695744, 1132855296),
    ('2019-05-24', 'LOG' , 1835008 , 557056),
    ('2019-05-24', 'ROWS' , 1153695744, 1132855296),
    ('2019-05-25', 'LOG' , 1835008 , 663552),
    ('2019-05-25', 'ROWS' , 1153695744, 1132855296)

    SELECT
    FORMAT(last_updated, 'dd-MM-yyyy') AS last_updated_,
    MAX(CASE WHEN type_desc = 'ROWS' THEN size_bytes ELSE 0 END) AS ROWS_size,
    MAX(CASE WHEN type_desc = 'ROWS' THEN used_bytes ELSE 0 END) AS ROWS_used,
    MAX(CASE WHEN type_desc = 'LOG' THEN size_bytes ELSE 0 END) AS LOG_size,
    MAX(CASE WHEN type_desc = 'LOG' THEN used_bytes ELSE 0 END) AS LOG_used,
    MAX(CASE WHEN type_desc = 'ROWS' THEN size_bytes ELSE 0 END) -
    MAX(CASE WHEN type_desc = 'ROWS' THEN used_bytes ELSE 0 END) AS ROWS_free,
    MAX(CASE WHEN type_desc = 'LOG' THEN size_bytes ELSE 0 END) -
    MAX(CASE WHEN type_desc = 'LOG' THEN used_bytes ELSE 0 END) AS LOG_free
    FROM #table_1
    GROUP BY last_updated
    ORDER BY last_updated

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • FORMAT is a dog when comes to performance.

    You would be better off using CONVERT.

    The following 2 expressions return the same result.  Although formatting is best left to the presentation layer.

    FORMAT(last_updated, 'dd-MM-yyyy')
    CONVERT(varchar(10), last_updated, 105)

     

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply