sql query for getting 4 colums data into one column

Home
»
C# Language
»
sql query for getting 4 colums data into one column

AuthorQuestion

darma teja

sql query for getting 4 colums data into one column
Posted on: 15 Feb 2013

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

Reply Quick Reply Report a Spam

Our Recommended Solutions

[ + ]

AuthorReply

brunda k

accepted

Re: sql query for getting 4 colums data into one column
Posted on: 16 Feb 2013 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

Reply Quick Reply

brunda k

Re: sql query for getting 4 colums data into one column
Posted on: 15 Feb 2013

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

Reply Quick Reply

darma teja

Re: sql query for getting 4 colums data into one column
Posted on: 15 Feb 2013

Thanks Brunda!

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

Thanks,

Darma

Reply Quick Reply

Hemant Srivastava

Re: sql query for getting 4 colums data into one column
Posted on: 15 Feb 2013

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)

Hemant Srivastava

Software Developer
MVP (C# Corner)
http://hemant-srivastava.blogspot.com/

Please mark this answer as accepted answer if it resolves your problem.

Reply Quick Reply

SEARCH ANSWERS

Our recommended forum posts

HOT QUESTIONS

In Focus

Our Recommended Solutions

SEARCH ANSWERS