How to traverse 2 rows into a single row based on values of a single column

Question

I have a to club the values present in a column of my table , based on the values they have in a different column.

For example

--------------------------------------------- 
Col1.         |       Col 2.               |
Bokaro            Sector 9
Dhanbad.        Sector 29
Bokaro            Sector 11
Dhanbad         Sector 30
Ranchi.            Sector 50

I want this to be displayed as

Col1.                  Col2
---------------------------------------------------------
Bokaro.            Sector 9      Sector 11
Dhanbad.         Sector 29.   Sector 30
Ranchi.             Sector 50

How to do this in a SQL query .

If you are using `mysql`, look into using `group_concat`. Other databases support similar methods -- lots of examples on SO already for this though. — sgeddes, Oct 15 '16 at 01:46
Possible duplicate of [Simulating group\_concat MySQL function in SQL Server?](http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-sql-server) — andref, Oct 15 '16 at 02:42

score 0 · Answer 1 · answered Oct 15 '16 at 04:40

If you are using SQL server, use STUFF function.

  SELECT  y1.Col1
   ,STUFF((SELECT '  ' + y2.Col2 [text()]
     FROM YourTable y2 
     WHERE y1.col1 =y2.col1
     FOR XML PATH(''), TYPE)
    .value('.','NVARCHAR(MAX)'),1,2,' ') [Col2]
FROM YourTable y1
GROUP BY y1.col1

How to traverse 2 rows into a single row based on values of a single column

1 Answers1