Row 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 2000 Forums

Transact-SQL (2000)

Row to CSV

Author

Topic

gsgill76
Posting Yak Master

137 Posts

Posted - 2008-09-24 : 02:20:46

Hi, it might be asked number of times, but i am not able to find the solution.
I am using SQL Server 2000
The problem is i have

SELECT Amount FROM MainAmount

the output is as


Amount
-----
100
101
102
103
104

I want it as


100, 101, 102, 103, 104

Thanks.
Gurpreet Gill

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-24 : 02:56:11

there are few methods listed here:-
http://www.projectdmx.com/tsql/rowconcatenate.aspx

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-09-24 : 03:17:34

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]

gsgill76
Posting Yak Master

137 Posts

Posted - 2008-10-14 : 00:10:03

Hi
I had


DECLARE @t TABLE (amount INT )

INSERT INTO @t SELECT 100
INSERT INTO @t SELECT 101
INSERT INTO @t SELECT 102
INSERT INTO @t SELECT 103
INSERT INTO @t SELECT 104

SELECT  t1.AMOUNT	FROM    @t t1

Output:
Amount
-----
100
101
102
103
104


But i want the Output as 
Amount
-----------------------
100, 101, 102, 103, 104

Please note i don't have any other column.
I know i can use loop, but any other way to do the same?

Thanks
Gurpreet Gill

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-14 : 01:58:02

did you even look at link i posted. it contained couple of methods. anyways try this

DECLARE @t TABLE (amount INT )

INSERT INTO @t SELECT 100
INSERT INTO @t SELECT 101
INSERT INTO @t SELECT 102
INSERT INTO @t SELECT 103
INSERT INTO @t SELECT 104



DECLARE @p VARCHAR(8000) ;
           SET @p = '' ;
        SELECT @p = @p + CAST(AMOUNT AS varchar(10)) + ','
          FROM @t

SELECT LEFT(@p,LEN(@p)-1) AS CSV

output
---------------------------------
CSV
---------------------
100,101,102,103,104

gsgill76
Posting Yak Master

137 Posts

Posted - 2008-10-14 : 05:17:49

visakh16 GR8

Thanks.........Thanks...........Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources