How to split single column into multiple columns

Question

I have a SQL Server table table_name like:

col1            col2
SomeString_1    23
SomeString_1    65
SomeString_1    300
SomeString_1    323

What I want to do is for one unique value in col1, I want to select all the values from col2 but each in it's own column.

So the query should be something like:

select col2 from table_name where col1 = 'SomeString_1';

But I need output in the form:

23  65  300 323

Basically each selected value should be in it's own column. So the result should always have one row and as many columns as the SomeString_1 is repeated.

I tried to search on SO but few questions I found had different conditions.

Possible duplicate of [ListAGG in SQLSERVER](http://stackoverflow.com/questions/15477743/listagg-in-sqlserver) — PM 77-1, Sep 01 '16 at 18:01
Possible duplicate of [Simulating group\_concat MySQL function in SQL Server?](http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-sql-server) — kjmerf, Sep 01 '16 at 18:35
You can find your answer here [Efficiently convert rows to columns in sql server](http://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server) — Munavvar, Sep 02 '16 at 09:10

score 2 · Answer 1 · answered Sep 01 '16 at 18:03

An approach can be applied by using FOR XML:

SELECT DISTINCT t.COL1, 
        (SELECT t1.COL2 + ' ' AS 'data()'
         FROM @TBL t1 
         WHERE t.COL1 = t1.COL1
         FOR XML PATH(''))
FROM @TBL t
WHERE t.COL1 = 'SomeString_1'

An example,

DECLARE @TBL TABLE (COL1 VARCHAR(100), COL2 VARCHAR(10))
INSERT INTO @TBL VALUES ('SomeString_1', '23'), 
                        ('SomeString_1', '65'), 
                        ('SomeString_1', '300'),
                        ('SomeString_1', '323')

SELECT DISTINCT t.COL1, 
        (SELECT t1.COL2 + ' ' AS 'data()'
         FROM @TBL t1 
         WHERE t.COL1 = t1.COL1
         FOR XML PATH(''))
FROM @TBL t
WHERE t.COL1 = 'SomeString_1'

which returns,

COL1            (No column name)
SomeString_1    23  65  300  323

score 2 · Accepted Answer · answered Sep 01 '16 at 19:09

2

Seems like OP is asking for separate column value for each row value:

create table #Table1 (COL1 VARCHAR(100), COL2 VARCHAR(10))
INSERT INTO #Table1 VALUES ('SomeString_1', '23'), 
                          ('SomeString_1', '65'), 
                          ('SomeString_1', '300'),
                          ('SomeString_1', '323')

DECLARE @columns nvarchar(MAX) = STUFF((
SELECT DISTINCT ',[col-' + cast(row_number() over (order by (select 1)) as varchar(4))+']'
FROM #Table1
FOR XML PATH('')), 1, 1, '')

DECLARE @sql nvarchar(MAX) = N'
SELECT * FROM
(
    SELECT col2, ''col-'' + cast(row_number() over (order by (select 1)) as varchar(4)) as dupcol2
    FROM #Table1 where col1 = ''SomeString_1''
) T
PIVOT
(MAX(col2) FOR dupcol2 IN ('+@columns+')) P'

EXEC (@sql)

Output:

col-1 | col-2 | col-3 | col-4
------------------------------
23    | 65    | 300   | 323

answered Sep 01 '16 at 19:09

p2k

2,126
4
23
39

Yes, you are correct. I am looking to have separate column value for each row value. Your suggested solution works best for me. But somehow I am getting extra columns like col-10, col-11,....col-20, col-21,....col-30, col-31,....col-40, col-14,....with NULL value in them. I also get col-1, col-2, col-3, col-4 with correct values. – 300 Sep 01 '16 at 19:49
Add the filter in 'WHERE COL2 is not null' in both the places. – p2k Sep 01 '16 at 20:11
I added it after both FROM but it still shows same result. – 300 Sep 01 '16 at 20:29
Without looking at your actual data, it is hard to suggest more. – p2k Sep 01 '16 at 21:01
It can be fixed with proper filter conditions. Please try to modify and test in where clause. – p2k Sep 01 '16 at 21:11

score 1 · Answer 3 · answered Sep 01 '16 at 18:13

1

You can use COALESCE also..

DECLARE @Names VARCHAR(8000) 

SELECT @Names = COALESCE(@Names + ' ', '') + COL2
FROM YourTable
WHERE COL1 = 'SomeString_1'

SELECT @Names

answered Sep 01 '16 at 18:13

Unnikrishnan R

4,965
1
12
21

How to split single column into multiple columns

3 Answers3

Linked