combine multiple rows into single column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

combine multiple rows into single column

Author

Topic

karthi.kamal66
Starting Member

7 Posts

Posted - 2012-04-20 : 02:21:26

Hi all

I have some problem that combing the multiple rows into single columns.

This is the table structure for i have

Name
Kamal
Siva
Raju
Ram

But i need the table in the following structure
Name
Kamal,Siva,Raju,Ram

If any one knows please guide me..

Thanks and regards

R.karthikeyan

KARTHIKEYAN R

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-04-20 : 02:27:35

refer to
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254
http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/rowset-string-concatenation-which-method-is-best.aspx

KH
[spoiler]Time is always against us[/spoiler]

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-04-27 : 07:58:18

Here you go:


SELECT 
MAX(CASE WHEN rn = 1 THEN NAME ELSE NULL END)+','+
MAX(CASE WHEN rn = 2 THEN NAME ELSE NULL END)+','+
MAX(CASE WHEN rn = 3 THEN NAME ELSE NULL END)+','+
MAX(CASE WHEN rn = 4 THEN NAME ELSE NULL END) As Names
FROM (SELECT NAME, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rn
      FROM <table_name>) a;

N 28° 33' 11.93148"
E 77° 14' 33.66384"

pphillips001
Starting Member

23 Posts

Posted - 2012-04-30 : 05:20:25

vinu's reply is very specific to the example, ie if there were more or less than four names it would break.

khtan's links are spot on. Another example of seek and ye shall find - a message to the OP ;)

===========================
There is no reality - only perspective.

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2012-05-03 : 14:29:00

quote:
Originally posted by karthi.kamal66

Hi all

I have some problem that combing the multiple rows into single columns.

This is the table structure for i have

Name
Kamal
Siva
Raju
Ram

But i need the table in the following structure
Name
Kamal,Siva,Raju,Ram

If any one knows please guide me..

Thanks and regards

R.karthikeyan

KARTHIKEYAN R



CREATE  TABLE #TAB 
(NAME VARCHAR(200))

INSERT INTO #TAB  (NAME)
SELECT 'Kamal'
UNION ALL
 SELECT 'Siva'
 UNION ALL
SELECT 'Raju'
UNION ALL
SELECT 'Ram'

select * into  #t2 from #TAB where 1=2 

DECLARE @COL VARCHAR(500)
DECLARE @Neval VARCHAR(500)

SELECT @COL = COALESCE(@COL, '') + NAME +',' FROM #TAB
--PRINT(@COL)
SET @Neval= SUBSTRING(@COL,1,LEN(@COL)-1)
print @Neval
insert into #t2(name) values(@Neval)

select * from #TAB
select * from #t2

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources