sql query for getting 4 colums data into one column

Question

Hi,

I tried allot on this qury but I am not succeeded.
My problem is:

I have 4 columns in my table:

example:

col1 col2 col3 col4
xxx     1 11 111
xxx 5 55          555
yyy          2 22 222
zzz 3 33          333
zzz 4 44         444

my result output should be:

col1 col2
xxx           col2-1; col3 -11; col4-111; col2-5; col3 -55; col4-555;
yyy   col2-2; col2-22; col2-222;
zzz   col2-3; col3 -33; col4-333; col2-4; col3 -44; col4-444;

thanks in advance

Darma

brunda k · Answer

drop table #temp create table #temp(col1 varchar(10),col2 varchar(50)) insert into #temp select col1,'col2-'+Convert(nvarchar(50),[col2])+'; col3-'+Convert(nvarchar(50),isnull(col3,0))+'; col4-'+Convert(nvarchar(50),isnull(col4,0)) as col2 from tablename SELECT distinct c.blogId, col2 = REPLACE( ( SELECT col2 AS [data()] FROM temp s WHERE s.col1 = c.col1 FOR XML PATH ('')), ' ', '') FROM #temp c group by c.col1 Ref: http://www.sqlservercentral.com/Forums/Topic877218-1292-1.aspx

Hemant Srivastava · Answer

it's kind of looping within loop.. this kind of situation is tough to handle in SQL, it would be very simpler if you do it through code (provided that you have option)

darma teja · Answer

Thanks Brunda! My problem is with concatenation of two "xxx" values and two "zzz" values. Thanks, Darma

brunda k · Answer

select 'col2-'+Convert(nvarchar(50),col2)+'; col3-'+Convert(nvarchar(50),isnull(col3,0)) as col2 from tablename http://www.codeproject.com/Questions/310086/How-to-concatenate-two-columns-in-SQL-server-2008

sql query for getting 4 colums data into one column

Answers (4)