query help ???

SUNIL GUTTA

12y710

Need a query for the below
Input
Col1 RANK
A 1
B 1
C 1
D 2
E 2
F 3
G 3

Output
Col1 Col2
1 A, B, C
2 D, E
3 F, G

Thank you . i just need a query :) to get the fallowing ouput

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Dhirendra Misra12y ago
Accepted answer
Hello Sunil,

The main query is

"SELECT [text()] = ', ' + ev2.col1
FROM Table1 ev2
WHERE ev2.[Rank] = ev.[Rank]
FOR XML PATH(''), TYPE)"

Which results concatenation as XML by specifying the FOR XML clause in the query. (, A, A, A, A).

Result value is being cast to Varchar using cast method to characters so that appearing first comma can be removed.

STUFF function works same as REPLACE function except for searching any value from specified string. It replaces part of the string in a specified string. Stuff is removing highlighted part.

, A, A, A, A

Hope it makes clear to you.
SUNIL GUTTA12y ago
ty friends you been so helpful :) well both of you deserved rating .. .. .. ty
Tirthak Shah12y ago
SELECT t.Rank as col1, STUFF(
(SELECT ',' + s.col1
FROM tes s
WHERE s.Rank = t.Rank
FOR XML PATH('')),1,1,'') AS col2
FROM tes AS t
GROUP BY t.Rank
GO

Here Is your Query Try It :-)
SUNIL GUTTA12y ago
Ty for showing your wisdom :) ty fellas Tirthak Shah & Dhirendra Misra

But , above mentioned query looks complex to understand i.e my db is SQLserver 2008..

please can you explain me the way you thought to get this thing done perfectly . I am confused about xml ? stuff ?? XQUERY ?? command you used in the code ..

Ty for ur help
Dhirendra Misra12y ago
Hello Sunil,

Solution depends on which database you are using.
If you are using,
1. MySQL : Use GROUP_CONCAT
SELECT
[Rank], group_concat(col1 SEPARATOR ' , ')
FROM
Table1
GROUP BY
[Rank];

2. Oracle : Use LISTAGG

3. SQL Server : There is no direct method to combine the result but you can do like this in single query using xQuery.
SELECT
ev.[Rank]
, Values1 = STUFF(CAST((
SELECT [text()] = ', ' + ev2.col1
FROM Table1 ev2
WHERE ev2.[Rank] = ev.[Rank]
FOR XML PATH(''), TYPE) AS VARCHAR(100)), 1, 2, '')
FROM (
SELECT DISTINCT [Rank]
FROM Table1
) ev

Thanks
Tirthak Shah12y ago
Hello,

Please Refer This ther is a solution.

Thank You,
Tirthak Shah