CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2
C Sharper
C Sharper

Reputation: 8646

Combine multiple rows into single row

I have below table :- table1

 ID        Desc

 1         ABC

 2         DEF

 3         GHI

 3         JKL

 4         MNO

 4         PQR

 4         STU

I want to show data as :-

 ID      Desc

 1         ABC

 2         DEF

 3         GHI

           JKL

 4         MNO

           PQR

           STU

I tried to make it as :-

select distinct ID , Desc from table1

But its not working.

Upvotes: 1

Views: 99

Answers (4)

zarruq
zarruq

Reputation: 2465

Below query will generate the desired result.

SELECT CASE WHEN (Rank() Over(ORDER BY id ASC)) = (Row_Number() Over (ORDER BY id ASC)) THEN id ELSE NULL END as ID, desc FROM table1

Upvotes: 1

Kannan Kandasamy
Kannan Kandasamy

Reputation: 13969

This needs to be handled in presentation layer but you can query as below:

Select Case when Row_Number() over(Partition by Id order by Id) = 1 then Id else Null end as Id, 
    [Desc] from #data

Upvotes: 2

Gagan Sharma
Gagan Sharma

Reputation: 220

In Sql server its possible :

   Select distinct ID , stuff((SELECT  ','+Description FROM #a a WHERE 
   t.id=a.id for xml path('')),1,1,'') Description
   from #a T


  Output :  Id        Desc
            1         ABC
            2         DEF
            3         GHI,JKL
            4         MNO,PQR,STU

Upvotes: 4

matteo
matteo

Reputation: 21

for what i know you can't, you have to modify the look of your table after the request (for example in html)

Upvotes: 2

Related Questions