We help IT Professionals succeed at work.

Converting multiple rows into a single column in TSQL


Converting multiple rows into a single column in TSQL

HI, I have 3 columns as shown in the below sample output. Basically, i want a sinlge row for each output
id. For example, for the outputid 1298630 and 1275588, my row  should like this -

1298630      281011       Current_Orginal 279030 Orginal_Orginal 281012 Current_Additional  279031 Orginal_Additional
1275588 156061      Current_Orginal 156056 Orginal_Orginal null    null                null    null


sample output -

outputid detailid   flag

1298630       281011        Current_Orginal
1298630       279030        Orginal_Orginal
1298630       281012        Current_Additional
1298630       279031        Orginal_Additional
1275588       156061        Current_Orginal
1275588       156056        Orginal_Orginal

Many Thanks
Comment
Watch Question

Commented:
Try using COALESCE to concatenate and bring the result in one row.
gvamsimbaIT Consultant

Author

Commented:
can any one give me that tsql code please ?
gvamsimbaIT Consultant

Author

Commented:

HI, but COALESCE is just giving me the non-null values. This is NOT what i want. I want all the values
for each outputid in a single row.
Rajkumar GsSoftware Engineer

Commented:
Try this code.

In this sample,
table name is #table
columns are value1, value2, comment

Replace with yours

Raj
SELECT DISTINCT value1,
  NULLIF(   
    STUFF(   
      (SELECT ', ' + ltrim(rtrim(cast(value2 as varchar(20)))) + ' ' + comment 
			FROM #table WHERE value1 = D.value1 FOR XML PATH('')  
                        ), 1, 2, ''   
    ), ''   
  ) AS AllNotes 
FROM #table D

Open in new window

Bhavesh ShahLead Analyst
Top Expert 2010

Commented:
gvamsimbaIT Consultant

Author

Commented:
Thank Raj, but instead of concatinating different rows into a single column,
is it possible to split them into different columns like below -

1298630      281011       Current_Orginal 279030 Orginal_Orginal 281012 Current_Additional  279031 Orginal_Additional
Rajkumar GsSoftware Engineer

Commented:
Can you please tell me the purpose of doing that ?

Raj
gvamsimbaIT Consultant

Author

Commented:
Basically, i want to put that output in to a table and use that table in my inner query of the main query...
Rajkumar GsSoftware Engineer

Commented:
I think It is not easy as achieve in a query.

According to my logic, It will take many steps to achieve.
You need to use a temporary table.

Need to find out maximum columns required.
select max(ccount) * 2 from
(select value1, count(value2) as ccount
from #table
group by value1) a

Then create a temporary table with that much colmns. Then insert values by looping inside a cursor
...................

If you post your original requirement, we can suggest any other easy way to achieve that, if any.

Raj
IT Consultant
Commented:
Hi Raj,
              The below query has done the trick and has given me exactly what i want....

select distinct  a.outputid ,
b.detailid as orginal_Orginal,
c.detailid as Orginal_additional,
d.detailid as Current_original,
e.detailid as Current_additional

from #test a

left outer join

(select outputid , detailid ,flag from #test where flag='Orginal_Orginal') b
on a.outputid=b.outputid

left outer join

(select outputid , detailid ,flag from #test where flag='Orginal_Additional') c
on a.outputid=c.outputid

left outer join

(select outputid , detailid ,flag from #test where flag='Current_Orginal') d
on a.outputid=d.outputid

left outer join

(select outputid , detailid ,flag from #test where flag='Current_Additional') e
on a.outputid=e.outputid
Rajkumar GsSoftware Engineer

Commented:
Ok Good.

I thought the value in column 'flag' is not limited to four different types (Orginal_Orginal, Orginal_Additional, Current_Orginal, Current_Additional). That why mentioned to find out maximum columns required. If it is only four, your query is fine.

If this is your answer, i suggest you to accept your answer itself as the accepted solution, so that someone in future will get the idea.

Raj