Please solve it - SQL Server Forums

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

Please solve it

Author

Topic

raky
Aged Yak Warrior

767 Posts

Posted - 2008-03-05 : 07:36:01

hi,

I need to get values in a column of different rows to be comma seperated.

example:

table a

col1 col2 col3
1 a b
2 c d
3 e f

I want output like this

col1 col2 col3
1,2,3 a,c,e b,d,f

Thanks in advance.........

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-03-05 : 08:17:29

http://www.sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-05 : 08:17:46

[code]DECLARE @t table
(col1 int,
col2 char(1),
col3 char(1)
)

INSERT INTO @t VALUES(1, 'a', 'b')
INSERT INTO @t VALUES(2, 'c', 'd')
INSERT INTO @t VALUES(3, 'e', 'f')

SELECT LEFT(c1.clist1,LEN(c1.clist1)-1) + ',' +
LEFT(c2.clist2,LEN(c2.clist2)-1) + ',' +
LEFT(c3.clist3,LEN(c3.clist3)-1)
FROM
(SELECT CAST(col1 AS char(1)) + ',' as [text()]
FROM @t
FOR XML PATH(''))c1(clist1)
CROSS APPLY(SELECT col2 + ',' as [text()]
FROM @t
FOR XML PATH(''))c2(clist2)
CROSS APPLY(SELECT col3 + ',' as [text()]
FROM @t
FOR XML PATH(''))c3(clist3)

output
-----------------------------
1,2,3,a,c,e,b,d,f[/code]

raky
Aged Yak Warrior

767 Posts

Posted - 2008-03-05 : 08:27:06

quote:

Originally posted by visakh16

DECLARE @t table
(col1 int,
 col2 char(1),
col3 char(1)
)

INSERT INTO @t VALUES(1, 'a', 'b')
INSERT INTO @t VALUES(2, 'c', 'd')
INSERT INTO @t VALUES(3, 'e', 'f')

SELECT LEFT(c1.clist1,LEN(c1.clist1)-1) + ',' +
       LEFT(c2.clist2,LEN(c2.clist2)-1) + ',' +
       LEFT(c3.clist3,LEN(c3.clist3)-1)
FROM
(SELECT CAST(col1 AS char(1)) + ',' as [text()]
 FROM @t
 FOR XML PATH(''))c1(clist1)
CROSS APPLY(SELECT col2 + ',' as [text()]
 FROM @t
 FOR XML PATH(''))c2(clist2)
CROSS APPLY(SELECT col3 + ',' as [text()]
 FROM @t
 FOR XML PATH(''))c3(clist3)
 

output
-----------------------------
1,2,3,a,c,e,b,d,f

Thanks for ur reply but i need some modification. i want output like this

col1
1,2,3

col2
a,c,e

col3
b,d,f

that is i want values belonging to same column are to appeared as comma separated but not values of all columns.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-05 : 08:30:38

Are you using Microsoft SQL Server 2000 or Microsoft SQL Server 2005?

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

raky
Aged Yak Warrior

767 Posts

Posted - 2008-03-05 : 08:33:05

quote:
Originally posted by Peso

Are you using Microsoft SQL Server 2000 or Microsoft SQL Server 2005?

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

iam using sql server 2005

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-05 : 08:35:42

quote:
Originally posted by raky

quote:
Originally posted by visakh16

DECLARE @t table
(col1 int,
col2 char(1),
col3 char(1)
)

INSERT INTO @t VALUES(1, 'a', 'b')
INSERT INTO @t VALUES(2, 'c', 'd')
INSERT INTO @t VALUES(3, 'e', 'f')

SELECT LEFT(c1.clist1,LEN(c1.clist1)-1) + ',' +
LEFT(c2.clist2,LEN(c2.clist2)-1) + ',' +
LEFT(c3.clist3,LEN(c3.clist3)-1)
FROM
(SELECT CAST(col1 AS char(1)) + ',' as [text()]
FROM @t
FOR XML PATH(''))c1(clist1)
CROSS APPLY(SELECT col2 + ',' as [text()]
FROM @t
FOR XML PATH(''))c2(clist2)
CROSS APPLY(SELECT col3 + ',' as [text()]
FROM @t
FOR XML PATH(''))c3(clist3)

output
-----------------------------
1,2,3,a,c,e,b,d,f

Thanks for ur reply but i need some modification. i want output like this

col1
1,2,3

col2
a,c,e

col3
b,d,f

that is i want values belonging to same column are to appeared as comma separated but not values of all columns.

change like this. Also make it a point to specify what you want clearly.

SELECT LEFT(c1.clist1,LEN(c1.clist1)-1) AS Col1,
       LEFT(c2.clist2,LEN(c2.clist2)-1) AS Col2,
       LEFT(c3.clist3,LEN(c3.clist3)-1) AS Col3
FROM
(SELECT CAST(col1 AS char(1)) + ',' as [text()]
 FROM @t
 FOR XML PATH(''))c1(clist1)
CROSS APPLY(SELECT col2 + ',' as [text()]
 FROM @t
 FOR XML PATH(''))c2(clist2)
CROSS APPLY(SELECT col3 + ',' as [text()]
 FROM @t
 FOR XML PATH(''))c3(clist3)
output
---------------------------
Col1       Col2        Col3
--------- ----------- -----------
1,2,3      a,c,e       b,d,f

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-05 : 08:39:55

[code]-- Prepare sample data
DECLARE @Sample TABLE
(
Col1 INT,
Col2 CHAR(1),
Col3 CHAR(1)
)

INSERT @Sample
SELECT 1, 'a', 'b' UNION ALL
SELECT 2, 'c', 'd' UNION ALL
SELECT 3, 'e', 'f'

-- Show the expected output
SELECT SUBSTRING(c1.Col1, 2, 8000) AS Col1,
SUBSTRING(c2.Col1, 2, 8000) AS Col2,
SUBSTRING(c3.Col1, 2, 8000) AS Col3
FROM (
SELECT ',' + CAST(Col1 AS VARCHAR(12))
FROM @Sample
FOR XML PATH('')
) AS c1 (Col1)
CROSS APPLY (
SELECT ',' + Col2
FROM @Sample
FOR XML PATH('')
) AS c2 (Col1)
CROSS APPLY (
SELECT ',' + Col3
FROM @Sample
FOR XML PATH('')
) AS c3 (Col1)[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources