Forum guidelines
  • Home
  • »
  • C# Language
  • »
  • sql query for getting 4 colums data into one column
AuthorQuestion
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










AuthorReply
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



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







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 | MVP (C# Corner) | http://hemant-srivastava.blogspot.com/
brunda k
  • 0
  • 0
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

SPONSORED BY

Custom Software Development
MCN is your source for developing solutions involving websites, mobile apps, cloud-computing, databases, BI, back-end services and processes and client-server applications.