May 10, 2019 at 2:51 pm
Hi, I am looking for help to understand how I can turn multiple rows, into 1 row as a sting. In hopes to better articulate what I am trying to accomplish, I have provided the following sample code. Any help is sincerely appreciated and thank you in advance!
CREATE TABLE #t (CID int, qGroup char(2), AVal char(1))
INSERT INTO #t (CID, qGroup, AVal) VALUES (101, 'EI', 'E')
INSERT INTO #t (CID, qGroup, AVal) VALUES (101, 'JP', 'J')
INSERT INTO #t (CID, qGroup, AVal) VALUES (101, 'SN', 'S')
INSERT INTO #t (CID, qGroup, AVal) VALUES (101, 'TF', 'T')
INSERT INTO #t (CID, qGroup, AVal) VALUES (102, 'EI', 'I')
INSERT INTO #t (CID, qGroup, AVal) VALUES (102, 'JP', 'P')
INSERT INTO #t (CID, qGroup, AVal) VALUES (102, 'SN', 'N')
INSERT INTO #t (CID, qGroup, AVal) VALUES (102, 'TF', 'F')
INSERT INTO #t (CID, qGroup, AVal) VALUES (103, 'EI', 'E')
INSERT INTO #t (CID, qGroup, AVal) VALUES (103, 'JP', 'P')
INSERT INTO #t (CID, qGroup, AVal) VALUES (103, 'SN', 'S')
INSERT INTO #t (CID, qGroup, AVal) VALUES (103, 'TF', 'F')
SELECT * FROM #t
-- Desired outcome - We want to take the listed AVal and turn them into a string/col as follows:
-- 101 EJST
-- 102 IPNF
-- 103 EPSF
May 10, 2019 at 3:06 pm
Two solutions. The first is somewhat obscure when you see it the first time, but works on all versions from SQL 2005 and up. The latter is more straightforward, but requires SQL 2017.
SELECT C.CID, T.str
FROM (SELECT DISTINCT CID FROM #t) AS C
CROSS APPLY (SELECT AVal AS [text()]
FROM #t T
WHERE C.CID = T.CID
ORDER BY T.qGroup
FOR XML PATH('')) AS T(str)
ORDER BY C.CID
SELECT CID, string_agg(AVal, '') WITHIN GROUP (ORDER BY qGroup)
FROM #t
GROUP BY CID
ORDER BY CID
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply