asked on

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

avirups

Try using COALESCE to concatenate and bring the result in one row.

avirups

http://msdn.microsoft.com/en-us/library/ms190349.aspx

gvamsimba

ASKER

can any one give me that tsql code please ?

gvamsimba

ASKER

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 Gs

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 Shah

hey,

check out this.

https://www.experts-exchange.com/questions/26254576/How-to-concatenate-one-field-from-similar-records.html?cid=1133&anchorAnswerId=32975534#a32975534

might be your query get resolve.

gvamsimba

ASKER

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 Gs

Can you please tell me the purpose of doing that ?

Raj

gvamsimba

ASKER

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

Rajkumar Gs

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

ASKER CERTIFIED SOLUTION

gvamsimba

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rajkumar Gs

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