Combine multiple rows into one

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

General SQL Server Forums

New to SQL Server Programming

Combine multiple rows into one

Author

Topic

Mauricio Moreno
Starting Member

18 Posts

Posted - 2008-02-07 : 19:44:36

Hello,

I have a delima, and im not really sure if this possible. But i have a table like lets say

id | data1
1 this
2 that
3 stuff

i want to be able to return this as one row with the data from data1 in one column seperated by commas.

so the result would be

1 Column
this, that, stuff

can anyone help me with this.

Thank you,

~ Moe

CShaw
Yak Posting Veteran

65 Posts

Posted - 2008-02-07 : 20:52:47

You could do it in a Cursor, but I bet there are better answers. You may want to post this in the T-SQL Forum.

Chris Shaw
www.SQLonCall.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-08 : 00:19:31

You want entire column values of table in one row? then use this,
In SQL 2005

SELECT LEFT(l.list,LEN(l.list)-1)
FROM
(
SELECT data1 + ',' AS [text()]
FROM Table
FOR XML PATH(''))l(list)

If SQL 2000:-

DECLARE @list varchar(8000)

SELECT @list=CusName + ',' + COALESCE(@list,'')
FROM t

SELECT LEFT(@list,LEN(@list)-1)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-08 : 01:24:34

http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/rowset-string-concatenation-which-method-is-best.aspx

Madhivanan

Failing to plan is Planning to fail

Arshiya
Starting Member

1 Post

Posted - 2008-06-03 : 02:43:12

this query is working fine but the problem is how do i group this for eg:
i have a View an
ID SubID
1234 112
1234 113
1234 114
I want it to be displayed as
ID SubID
1234 112,113,114
Plz help me with this

quote:
Originally posted by visakh16

You want entire column values of table in one row? then use this,
In SQL 2005
SELECT LEFT(l.list,LEN(l.list)-1)
FROM
(
SELECT data1 + ',' AS [text()]
FROM Table
FOR XML PATH(''))l(list)

If SQL 2000:-
DECLARE @list varchar(8000)

SELECT @list=CusName + ',' + COALESCE(@list,'')
FROM t

SELECT LEFT(@list,LEN(@list)-1)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-03 : 03:31:32

[code]DECLARE @Sample TABLE (ID INT, SubID INT)

INSERT @Sample
SELECT 1234, 112 UNION ALL
SELECT 1234, 113 UNION ALL
SELECT 1234, 114 UNION ALL
SELECT 3234, 212 UNION ALL
SELECT 3234, 213 UNION ALL
SELECT 3234, 214

SELECT i.ID,
STUFF(g.y, 1, 1, '') AS SubIDs
FROM (
SELECT ID
FROM @Sample
GROUP BY ID
) AS i
CROSS APPLY (
SELECT DISTINCT ',' + CAST(SubID AS VARCHAR(11))
FROM @Sample AS s
WHERE s.ID = i.ID
ORDER BY ',' + CAST(SubID AS VARCHAR(11))
FOR XML PATH('')
) AS g(y)[/code]

E 12°55'05.25"
N 56°04'39.16"

musthaan
Starting Member

1 Post

Posted - 2012-03-02 : 05:50:40

I have Two Options,

1)
``````
declare @x varchar(100)
select @x= isnull(@x+', ','')+CityName from lkpUAECity
select @x

2)
```````

SELECT LEFT(l.list,LEN(l.list)-1)
FROM
(
SELECT CityName + ',' AS [text()]
FROM lkpUAECity
FOR XML PATH(''))l(list)

Musthaan Majeed

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-02 : 12:25:21

quote:
Originally posted by musthaan

I have Two Options,

1)
``````
declare @x varchar(100)
select @x= isnull(@x+', ','')+CityName from lkpUAECity
select @x

2)
```````

SELECT LEFT(l.list,LEN(l.list)-1)
FROM
(
SELECT CityName + ',' AS [text()]
FROM lkpUAECity
FOR XML PATH(''))l(list)

Musthaan Majeed

what was the purpose of opening an old thread?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources