Example Dataset:
ID seat code
15098 1 AA21
15098 2 AA21
15105 1 AA21
15105 1 DD15
15105 1 NN60
15196 1 AA21
15196 2 DD50
15196 2 DD51
15209 1 AA21
15209 3 AA21
15209 2 CC50
15209 1 DD01
15209 3 DD01
15210 1 AA21
15210 2 AA21
15210 3 AA21
15210 1 DD21
15210 2 DD21
15210 3 DD21
15211 1 CC51
15211 1 DD20
15212 1 AA21
15212 1 DD03
and Desired Result is:
ID seat Codes
15098 1 AA21
15098 2 AA21
15105 1 AA21, DD15, NN60
15196 1 AA21
15196 2 DD50, DD51
15209 1 AA21, DD01
15209 2 CC50
15209 3 AA21, DD01
15210 1 AA21, DD21
15210 2 AA21, DD21
15210 3 AA21, DD21
15211 1 CC51, DD20
15212 1 AA21, DD03
Example Dataset table query:
CREATE TABLE #Temp(
ID varchar(50),
seat varchar(50),
code varchar(150))
INSERT INTO #Temp VALUES ('15098', '1', 'AA21');
INSERT INTO #Temp VALUES ('15098', '2', 'AA21');
INSERT INTO #Temp VALUES ('15105', '1', 'AA21');
INSERT INTO #Temp VALUES ('15105', '1', 'DD15');
INSERT INTO #Temp VALUES ('15105', '1', 'NN60');
INSERT INTO #Temp VALUES ('15196', '1', 'AA21');
INSERT INTO #Temp VALUES ('15196', '2', 'DD50');
INSERT INTO #Temp VALUES ('15196', '2', 'DD51');
INSERT INTO #Temp VALUES ('15209', '1', 'AA21');
INSERT INTO #Temp VALUES ('15209', '3', 'AA21');
INSERT INTO #Temp VALUES ('15209', '2', 'CC50');
INSERT INTO #Temp VALUES ('15209', '1', 'DD01');
INSERT INTO #Temp VALUES ('15209', '3', 'DD01');
INSERT INTO #Temp VALUES ('15210', '1', 'AA21');
INSERT INTO #Temp VALUES ('15210', '2', 'AA21');
INSERT INTO #Temp VALUES ('15210', '3', 'AA21');
INSERT INTO #Temp VALUES ('15210', '1', 'DD21');
INSERT INTO #Temp VALUES ('15210', '2', 'DD21');
INSERT INTO #Temp VALUES ('15210', '3', 'DD21');
INSERT INTO #Temp VALUES ('15211', '1', 'CC51');
INSERT INTO #Temp VALUES ('15211', '1', 'DD20');
INSERT INTO #Temp VALUES ('15212', '1', 'AA21');
INSERT INTO #Temp VALUES ('15212', '1', 'DD03');
I am using below query as a part of my SSRS report stored procedure to get the result but it is taking too long to run as my original dataset is more than a 100000 rows. Is there any other efficient way to get the result.
SELECT
SS.ID
,SS.seat
,STUFF((SELECT ', ' + CAST(LTRIM(RTRIM(CR.Code)) AS VARCHAR(10)) [text()]
FROM #Temp CR
WHERE CR.ID = SS.ID and CR.seat = SS.seat
FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)'),1,2,' ') Codes
FROM #Temp SS
GROUP BY SS.ID, SS.seat
Aucun commentaire:
Enregistrer un commentaire