November 12, 2019 at 10:00 pm
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.
November 12, 2019 at 10:33 pm
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
November 13, 2019 at 4:22 am
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