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 Simulating group_concat MySQL function in SQL Server? — kjmerf
– kjmerf, Commented Sep 1, 2016 at 18:35
You can find your answer here Efficiently convert rows to columns in sql server — Munavvar
– Munavvar, Commented Sep 2, 2016 at 9:10

Pintu Kawar · Accepted Answer · 2016-09-01 19:09:58Z

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 1, 2016 at 19:09

Pintu Kawar

2,1564 gold badges25 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

300 Over a year ago

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.

Pintu Kawar Over a year ago

Add the filter in 'WHERE COL2 is not null' in both the places.

300 Over a year ago

I added it after both FROM but it still shows same result.

Pintu Kawar Over a year ago

Without looking at your actual data, it is hard to suggest more.

Pintu Kawar Over a year ago

It can be fixed with proper filter conditions. Please try to modify and test in where clause.

Anthony Forloney · Accepted Answer · 2016-09-01 18:03:30Z

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

Unnikrishnan R · Accepted Answer · 2016-09-01 18:13:44Z

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 1, 2016 at 18:13

Unnikrishnan R

5,0511 gold badge15 silver badges21 bronze badges

Collectives™ on Stack Overflow

How to split single column into multiple columns

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related