CODEWITHSUNDEEP
sqlsql-serveralgorithmstored-procedures
user1423316
user1423316

Reputation:

SQL Server: Convert single row to comma delimited (separated) format

As the title states, I need help in converting a single row of data E.g,

col1 col2 col3 <-- This are column names
 value1 value2 value3

To something like

dataResult <-- this is the column name from running the procedure or call
 value1,value2,value3

The requirements are that this call ( or rather procedure) needs to be able to accept the results of sql queries of any column length and is able to convert that row to a comma delimited string format. Been stuck at this for weeks any help would be greatly appreciated...

EDIT* Assume the unique key is the first column. Also assume that only 1 row will be returned with each query. Multiple rows will never occur.

The idea is to convert that row to a comma separated string without having to select the column names manually (in a sense automatically convert the query results)

Upvotes: 1

Views: 2178

Answers (2)

ahmed abdelqader
ahmed abdelqader

Reputation: 3558

Applay the next Approach:-

Demo:-

Create database MyTestDB
go 

Use MyTestDB
go

Create table Table1 ( col1 varchar(10), col2 varchar(10),col3 varchar(10))
go

insert into Table1 values ('Value1','Value2','Value3')
insert into Table1 values ('Value11','Value12','Value13')
insert into Table1 values ('Value21','Value22','Value23')
go


Declare @Values nVarchar(400),
        @TableName nvarchar (100),
        @Query nvarchar(max)

Set @TableName = 'Table1'
Select @Values =  Stuff(
        (
        Select '+'','' + ' + C.COLUMN_NAME
        From INFORMATION_SCHEMA.COLUMNS As C
        Where C.TABLE_SCHEMA = T.TABLE_SCHEMA
            And C.TABLE_NAME = T.TABLE_NAME
        Order By C.ORDINAL_POSITION
        For Xml Path('')
        ), 1, 2, '') 
From INFORMATION_SCHEMA.TABLES As T
where TABLE_NAME = @TableName

select @Values =  right(@Values,len(@Values)-4)

select @Query =  'select top(1)' + @Values + ' from ' + @TableName


exec sp_executeSQL @Query

Result:-

enter image description here

Upvotes: 1

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

You might try it like this:

A declared table variable to mock-up as test table. Be aware of the NULL value in col2!

DECLARE @tbl TABLE(col1 VARCHAR(100),col2 VARCHAR(100),col3 VARCHAR(100));
INSERT INTO @tbl VALUES('test1',NULL,'test3');

--This is the query:

SELECT 
STUFF(
(
    SELECT ',' + elmt.value('.','nvarchar(max)')
    FROM
    (
    SELECT
        (
    /*YOUR QUERY HERE*/
            SELECT TOP 1 * 
            FROM @tbl
    /*--------------------*/
            FOR XML AUTO ,ELEMENTS XSINIL,TYPE
        )
    ) AS A(t)
    CROSS APPLY t.nodes('/*/*') AS B(elmt)
    FOR XML PATH('')
),1,1,'')

FOR XML AUTO will return each row as XML with all the values within attributes. But this would omit NULL values. Your returned string would not inlcude the full count of values in this case. Stating ELEMENT XSINIL forces the engine to include NULL values into the XML. This CROSS APPLY t.nodes('/*/*') will return all the elements as derived table and the rest is re-conactenation.

See the double comma in the middle! This is the NULL value of col2

test1,,test3

ATTENTION: You must be aware, that the whole approach will break, if there is a comma part of a (string) column...

Hint

Better was a solution with XML or JSON. Comma separated values are outdated...

Upvotes: 1

Related Questions