I pretty much suck at SQL queries. I have a table which has data as follows:
Would it be possible to visualize the data as so:
I guess I would describe it as grouping by name, then filename, then step (task, status, date, and notes would be on the same line always).
Sorry for the images, the site kept complaining of code not being formatted when I didn't have any code...
Any help is much appreciated.
try this:
;WITH dataset AS
(
SELECT *
FROM
(values
('Andrea', 'C Pony', 'Pre-Trial Applications', 'NOA', 'Pending Review', '2022-09-09', 'Sent to NL on'),
('Andrea', 'P Inc', 'Commencement of Proceeding', 'LOD of Client', 'Not Started', '2022-09-09', Null),
('Oliver', 'C Pony', 'Pre-Trial Applications', 'AR', 'Filed', '2022-09-09', NUll),
('Oliver', 'C Pony', 'Pre-Trial Applications', 'Desk Order', 'Not Started', '2022-09-09', NUll),
('Oliver', 'C Pony', 'Pre-Trial Applications', 'Affidavit', 'Not Started', '2022-09-09', NUll),
('Oliver', 'P Inc', 'Commencement of Proceeding', 'BF', 'Not Started', '2022-09-09', NUll)
) AS T (assigned_to, file_name, step, task, progress, assigned_date,notes)
)
,datasetRN AS
(
SELECT *, dense_rank() OVER(ORDER by assigned_to, file_name, step) as rn
FROM dataset
)
SELECT assigned_to, file_name, step, last4fieldsConcatenated
FROM (
SELECT rn, assigned_to, null as file_name, null as step, null as last4fieldsConcatenated
FROM datasetRN
UNION
SELECT rn, null, file_name, null, null
FROM datasetRN
UNION
SELECT rn, null, null, step, null
FROM datasetRN
UNION
SELECT rn, null, null, null, CONCAT(task, ' - ', progress, ' ', assigned_date, ' ', notes)
FROM datasetRN
) AS t
ORDER BY rn, assigned_to DESC, file_name DESC, step DESC
output result:
