T-SQL query for conditional string cocatenation

Question

PersonID	Name	ResultDate	CategoryName	CategoryValue
569	A	2015-11-10	Note	NULL
569	A	2015-11-10	Result	Passed
569	A	2015-11-10	Score	16
585	B	2015-11-09	Note	Hello!
585	B	2015-11-09	Result	Failed
585	B	2015-11-09	Score	8
585	B	2020-03-01	Note	NULL
585	B	2020-03-01	Result	Passed
585	B	2020-03-01	Score	10

I have this table of exam results in SQL Server.

Each PersonID can have multiple ResultDate rows, and for each ResultDate they always have CategoryName values of Note, Result, and Score.

I want to concatenate the CategoryValues columns whose CategoryName are Result and Score, so I have a Result(Score) format.

For example, for PersonID 569 on 2015-11-10 I want to see Passed(16). For PersonID 585 I want to see 'Failed(8)' for 2015-11-09 and Passed(10) for 2020-03-01.

How can I achieve this?

I've tried STRING_AGG and CONCAT with conditional queries but failed.

Answer 1

You can use STRING_AGG with CONCAT, it's somewhat unpretty though:

select personid,name, ResultDate
, string_agg(CONCAT(CategoryValue, case when CategoryName = 'Result' THEN ' (' ELSE ')' END), '') WITHIN GROUP (ORDER BY case when CategoryName = 'Result' THEN 0 ELSE 1 END) AS Result
from (
    VALUES  (569, N'A', N'2015-11-10', N'Note', NULL)
    ,   (569, N'A', N'2015-11-10', N'Result', N'Passed')
    ,   (569, N'A', N'2015-11-10', N'Score', N'16')
    ,   (585, N'B', N'2015-11-09', N'Note', N'Hello!')
    ,   (585, N'B', N'2015-11-09', N'Result', N'Failed')
    ,   (585, N'B', N'2015-11-09', N'Score', N'8')
    ,   (585, N'B', N'2020-03-01', N'Note', NULL)
    ,   (585, N'B', N'2020-03-01', N'Result', N'Passed')
    ,   (585, N'B', N'2020-03-01', N'Score', N'10')
) t (PersonID,Name,ResultDate,CategoryName,CategoryValue)
WHERE CategoryName IN ('Result', 'Score')
group by personid, name, resultDate

Outputs:

personid	name	ResultDate	Result
569	A	2015-11-10	Passed (16)
585	B	2015-11-09	Failed (8)
585	B	2020-03-01	Passed (10)