CODEWITHSUNDEEP
sql-servert-sql
Paradox
Paradox

Reputation: 4566

Convert one column into one row in T-SQL

I have a select statement which yields a single large column. What I would like to do is convert the one column into one row so that I can feed it into a another stored procedure. How could I transpose the table so that it is one single row? So far, I have tried UNIPIVOT but have not been able to get it working.

Here is the format of the current table (in reality, it's much longer with a variable amount of rows):

+------+
| Col1 |
+------+
|    1 |
|   56 |
|   83 |
|  345 |
| 4322 |
| 4456 |
+------+

which is stored in a local table #localtable

I would like to turn the above table into the below table:

+------+------+------+------+------+------+
| Col1 | Col2 | Col3 | Col4 | col5 | col6 |
+------+------+------+------+------+------+
|    1 |   56 |   83 |  345 | 4322 | 4456 |
+------+------+------+------+------+------+

as an intermediate step in converting it into the following comma-delimited string:

'1, 56, 83, 345, 4322, 4456'

With the goal of feeding it into an exec like:

exec myfunction '1, 56, 83, 345, 4322, 4456'

Upvotes: 1

Views: 3000

Answers (3)

Paradox
Paradox

Reputation: 4566

I was able to solve this with the following query, creating my desired comma-delimited string:

SELECT STUFF((SELECT  ',' + CAST(Col1 AS VARCHAR(50))
            FROM #localtable
        FOR XML PATH('')), 1, 1, '') AS listStr

Upvotes: 3

Andrea
Andrea

Reputation: 12355

Here is another solution that will work with older SQL Server version (where no string_agg is available):

--create test table
declare @tmp table (Col1 int)

--populate test table
insert into @tmp
values
 (    1)
,(   56)
,(   83)
,(  345)
,( 4322)
,( 4456)

--declare a variable that will contain the final result
declare @result varchar(4000) = ''

--populate final result from table
select @result = @result + ', ' + cast(Col1 as varchar) from @tmp

--remove first unnecessary comma
set @result = STUFF(@result, 1, 1, '') 

--print result
select @result as result

Result:

enter image description here

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269773

You can use string_agg() in the most recent versions of SQL Server:

select string_agg(col, ', ')
from #localtable;

Upvotes: 2

Related Questions