Rows into CSV resultset
-
Hi
I need to display the list of rows in CSV formated result set
Eg.
MyTable
ColA ColB
1 AAAA
2 BBBB
3 CCCC
4 DDDD
I need to display the as 1, 2, 3, 4 . I don't need ColB for this.
Thanks in Advance
Sathish
S A T ...
Sathish's Blog -
Use DTS for this, it is the easiets way.
Hope this helps...
Ford Fairlane
Rock and Roll Detective
-
SET NOCOUNT ON
DECLARE @MyTable TABLE
(
ColA INT,
ColB VARCHAR(10)
)
INSERT INTO @MyTable VALUES (1,'AAAA')
INSERT INTO @MyTable VALUES (2,'BBBB')
INSERT INTO @MyTable VALUES (3,'CCCC')
INSERT INTO @MyTable VALUES (4,'DDDD')
INSERT INTO @MyTable VALUES (5,'EEEE')
/* RESULT */
DECLARE @Result VARCHAR(100)
SELECT @Result = COALESCE(@Result + ', ', '') + CONVERT(VARCHAR, ColA)
FROM
@MyTable
SELECT @Result Result
Regards,
gova -
I like that one...
But what if the row order changes?
SET NOCOUNT ON
DECLARE @MyTable TABLE
(
ColA INT,
ColB VARCHAR(10)
)
INSERT INTO @MyTable VALUES (1,'AAAA')
INSERT INTO @MyTable VALUES (2,'BBBB')
INSERT INTO @MyTable VALUES (4,'DDDD')
INSERT INTO @MyTable VALUES (5,'EEEE')
INSERT INTO @MyTable VALUES (3,'CCCC')
/* RESULT */
DECLARE @Result VARCHAR(100)
SELECT @Result = COALESCE(@Result + ', ', '') + CONVERT(VARCHAR, ColA)
FROM
@MyTable
SELECT @Result Result
This returns
, 1, 2, 4, 5, 3
and you (probably) still want 1, 2, 3, 4, 5 back. Can you manage that (without putting a clustered index on colA)?
-
SELECT @Result = COALESCE(@Result + ', ', '') + CONVERT(VARCHAR, ColA)
FROM
(SELECT TOP 100 PERCENT * FROM @MyTable ORDER BY 1) A
Regards,
gova -
Very nice, I'm impressed - I will store this trick for later use
-
The same result:
SELECT @Result = COALESCE(@Result + ', ', '') + CONVERT(VARCHAR, ColA)
FROM @MyTable
ORDER BY ColA
-
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply