convert to csv - 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

SQL Server 2005 Forums

Transact-SQL (2005)

convert to csv

Author

Topic

persiangulf098
Starting Member

10 Posts

Posted - 2010-02-18 : 13:23:53

HELLO
1 2
-- --
1 100
4 50
1 200
6 20
1 45
5 120
5 120
6 80

I want this result

1,100,200,45
4,50
6,20,80
5,120,80

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-02-18 : 13:27:04

[code]SELECT DISTINCT a.[1],
STUFF((SELECT DISTINCT TOP 100 PERCENT ',' + b.[2] FROM <urtable> AS b WHERE b.[1] = a.[1] ORDER BY ',' + b.[2] FOR XML PATH('')), 1, 1, '') AS [csv]
FROM <urtable> AS a
ORDER BY a.[1][/code]

EDIT : [1] and [2] are ur column names...from the sample.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-18 : 13:28:09

use bcp or OPENDATASOURCE.
for getting the related column 2 values as csv use below

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

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

persiangulf098
Starting Member

10 Posts

Posted - 2010-02-18 : 15:31:12

quote:
Originally posted by vijayisonly

SELECT DISTINCT a.[1],
STUFF((SELECT DISTINCT TOP 100 PERCENT ',' + b.[2] FROM <urtable> AS b WHERE b.[1] = a.[1] ORDER BY ',' + b.[2] FOR XML PATH('')), 1, 1, '') AS [csv]
FROM <urtable> AS a
ORDER BY a.[1]

EDIT : [1] and [2] are ur column names...from the sample.

Thanks for your attention but
This error :Conversion failed when converting the varchar value ',' to data type int.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-02-18 : 15:35:13

convert ur column [2] to a varchar value before concatenation... like

SELECT DISTINCT	a.[1],
		STUFF((SELECT DISTINCT TOP 100 PERCENT ',' + convert(varchar(10),b.[2]) FROM table AS b WHERE b.[1] = a.[1] ORDER BY ',' + convert(varchar(10),b.[2]) FOR XML PATH('')), 1, 1, '') AS [csv]
FROM	table AS a
ORDER BY	a.[1]

persiangulf098
Starting Member

10 Posts

Posted - 2010-02-18 : 15:49:49

quote:
Originally posted by vijayisonly

convert ur column [2] to a varchar value before concatenation... like
SELECT DISTINCT a.[1],
STUFF((SELECT DISTINCT TOP 100 PERCENT ',' + convert(varchar(10),b.[2]) FROM table AS b WHERE b.[1] = a.[1] ORDER BY ',' + convert(varchar(10),b.[2]) FOR XML PATH('')), 1, 1, '') AS [csv]
FROM table AS a
ORDER BY a.[1]

Sorry ,I have result set but csv has null values

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-02-18 : 15:53:19

What does that mean? I get correct results with your sample data.

declare @t table 
([1] int, [2]  int)
insert @t
select 1, 100
union all select 4, 50
union all select 1, 200
union all select 6, 20
union all select 1, 45
union all select 5, 120
union all select 5, 120
union all select 6, 80

SELECT DISTINCT	a.[1],
		STUFF((SELECT DISTINCT TOP 100 PERCENT ',' + convert(varchar(10),b.[2]) FROM @t AS b WHERE b.[1] = a.[1] ORDER BY ',' + convert(varchar(10),b.[2]) FOR XML PATH('')), 1, 1, '') AS [csv]
FROM	@t AS a
ORDER BY	a.[1]

persiangulf098
Starting Member

10 Posts

Posted - 2010-02-18 : 16:21:34

quote:

Originally posted by vijayisonly

What does that mean? I get correct results with your sample data.

declare @t table 
([1] int, [2]  int)
insert @t
select 1, 100
union all select 4, 50
union all select 1, 200
union all select 6, 20
union all select 1, 45
union all select 5, 120
union all select 5, 120
union all select 6, 80

SELECT DISTINCT	a.[1],
		STUFF((SELECT DISTINCT TOP 100 PERCENT ',' + convert(varchar(10),b.[2]) FROM @t AS b WHERE b.[1] = a.[1] ORDER BY ',' + convert(varchar(10),b.[2]) FOR XML PATH('')), 1, 1, '') AS [csv]
FROM	@t AS a
ORDER BY	a.[1]

Thanks

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-02-18 : 18:03:15

You're welcome.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources