sql query for getting 4 colums data into one column

darma teja

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

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

brunda k13y ago
Accepted 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 Srivastava13y ago
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 teja13y ago
Thanks Brunda!

My problem is with concatenation of two "xxx" values and two "zzz" values.

Thanks,

Darma
brunda k13y ago
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