CODEWITHSUNDEEP
sql-serverstring
edgarmtze
edgarmtze

Reputation: 25048

All columns in a table to string SQL

How to make multiple columns a string in SQL

Following a single column to string by @Alex Aza: 

 select stuff(
    (select ',' + cast([colum_name] as varchar)
    from [dbtest].[dbo].[table]
    for xml path('')),
    1, 1, '')

How to use that to get all columns in a table a string? I was thinking using dynamic SQL....

So for example

col1  col2 col3
---------------
1    7    13 
2    8    14
3    9    15
4   10    16
5   11    17
6   12    18

(this results something like: 1,2,3,4,5,6 )

How to make it Return:

1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18

Upvotes: 2

Views: 6638

Answers (2)

nathan_jr
nathan_jr

Reputation: 9282

This was such a unique request I just had to give it a shot. It is possible to concatenate the csv without prior knowledge of column names (albeit, using very poor performing xquery tricks). A dynamic query of UNIONs is far simpler to test and maintain, but here's how it could be done:

declare @t table (col1 int, col2 int, col3 int);

-- test data
insert into @t
select distinct 
        number,
        number+10,
        number+20
from master..spt_values where number between 1 and 10

select * from @t;
--

declare @x xml;
set @x = (select * from @t for xml path('r'));

;with c_ (s, r)
as  (   select  p.n.query('data(.)').value('.', 'varchar(10)'), 
                p.n.query('local-name(..)').value('.','varchar(10)')
        from    @x.nodes('r//.')p(n)
    )
select [csv] = stuff(
    (   select top 100 percent ','+s
        from    c_ 
        where   r = 'r'
        order
        by      len(s), s
        for xml path('')
    ),1,1,'');

Upvotes: 1

user756519
user756519

Reputation:

May be, you can try something like this.

SELECT STUFF(
                    (
                        SELECT  ',' + CAST([col] AS VARCHAR)
                        FROM
                        (
                            SELECT col1 AS col FROM [dbtest].[dbo].[table] UNION
                            SELECT col2 AS col FROM [dbtest].[dbo].[table] UNION
                            SELECT col3 AS col FROM [dbtest].[dbo].[table] 
                        ) alldata
                        FOR XML PATH('')
                    )
                ,   1
                ,   1
                ,   ''
            )

With sample data in the table like this...

Data

query output would be as following:

Output

Upvotes: 3

Related Questions