concatenate cols in rows of a recordset

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

SQL Server Development (2000)

concatenate cols in rows of a recordset

Author

Topic

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2004-09-22 : 09:38:38

I have a recordset returning rows , of random number of cols.
I am trying to achieve the result of converting every row , into a csv, which I can then pass as a recordset , of only 1 col.
For example,
col1 | col2 | col 3
1 | 2 | 3
becomes
col1
1,2,3

and then , for example
col1 | col2 | col 3 | col4
1 | 2 | 3 | 4
becomes
col1
1,2,3,4

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-22 : 09:52:07

my esp server crashed today

, but is this it?

select col1 + ',' + col2 + ',' + col3 + ',' + col4 as col
from MyTable

Go with the flow & have fun! Else fight the flow

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2004-09-22 : 09:57:37

partly, but the problem is the columns are of a random number, so sometimes it can be 3 cols and sometimes 15 cols, as this is a dynamically created query. In effect, it is a SELECT *

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2004-09-22 : 09:57:58

Presumably you'd want to do a bit of conversion and tesing for nulls in there:

SELECT CONVERT(VARCHAR(10), ISNULL(col1, '')) + ',' + CONVERT(VARCHAR(10), ISNULL(col2, '')) , etc.

Mark

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2004-09-22 : 10:12:40

partly, but the problem is the columns are of a random number, so sometimes it can be 3 cols and sometimes 15 cols, as this is a dynamically created query. In effect, it is a SELECT * .

is there a function , that could do a csv on a * ?

--------------------------------------------------------------------------------

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-22 : 10:21:15

no.
u could use inforamtion_schema views...
look at:

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

Go with the flow & have fun! Else fight the flow

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2004-09-22 : 10:25:48

I'd already done this by the time Spirit posted, so you might as well have it!!

--Declare local variables
DECLARE @table VARCHAR(255)
DECLARE @sql VARCHAR(8000)

--Set table name here
SET @table = 'MyTable'

--Initialise sql string
SET @sql = 'SELECT '

--Build sql string from information schema view
SELECT
@sql = COALESCE(@sql + 'CONVERT(VARCHAR(25), ISNULL(' + COLUMN_NAME + ', ''''))' + CASE WHEN ORDINAL_POSITION = (SELECT MAX(ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @Table) THEN '' ELSE ' + '','' + ' END, '' )
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_NAME = @table

--Terminate sql string
SET @sql = @sql + CHAR(32) + ' FROM ' + @table

--Execute sql string
EXEC (@sql)

Mark

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-22 : 10:26:42

If you are building it dynamically then why can't you just build it with only one column? Can you show us a sample of your dynamic query?

Corey

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2004-09-22 : 10:55:16

An example is (which could also be , any number ):
Select * From (Select 'Man Utd' = backOdds1 From t_getMarket Where selectionName = 'Man Utd' AND marketId = 1977618 AND groupId = 1 ) S1,
(Select 'The Draw' = backOdds1 From t_getMarket Where selectionName = 'The Draw' AND marketId = 1977618 AND groupId = 1 ) S2,
(Select 'Arsenal' = backOdds1 From t_getMarket Where selectionName = 'Arsenal' AND marketId = 1977618 AND groupId = 1 ) S3

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-22 : 11:03:15

why couldn't you build as such:

Select [Man Utd] +','+ [The Draw] +','+ [Arsenal] From (Select 'Man Utd' = backOdds1 From t_getMarket Where selectionName = 'Man Utd' AND marketId = 1977618 AND groupId = 1 ) S1,
(Select 'The Draw' = backOdds1 From t_getMarket Where selectionName = 'The Draw' AND marketId = 1977618 AND groupId = 1 ) S2,
(Select 'Arsenal' = backOdds1 From t_getMarket Where selectionName = 'Arsenal' AND marketId = 1977618 AND groupId = 1 ) S3

Corey

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2004-09-22 : 11:32:41

thanks, that was a good one

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources