How to Pivot one Row into One Column

Question

Can someone please help me out. I've looked around and can't find something similar to what I need to do. Basically,

I have a table that will need to be pivoted, it is coming from a flat file that loads all columns as one comma delimited column. I will need to break out the columns into their respective order before the pivot and I've got procedures that do this beautifully. However, the crux of this table is that I need to edit the headers before I can continue.

I need help to pivot the information in the first column and put it another table I've created. Therefore, I need this

    ID   Column01

    1    Express,,,Express,,,HyperMakert,,WebStore,Web

To End up like this....

    New_ID   New_Col

    1        Express
    2        
    3
    4        Express
    5
    6        
    7        HyperMarket
    8
    9        WebStore
    10       Web

Please note that I need to include the '' Black columns from the original row and.
I looked and the links below but they were not helpful;

SQL Server : Transpose rows to columns Efficiently convert rows to columns in sql server Mysql query to dynamically convert rows to columns

split then value with.. [SplitFunction][1] [1]: http://stackoverflow.com/questions/2647/how-do-i-split-a-string-so-i-can-access-item-x/2685#2685 — mordechai, Aug 18 '14 at 13:34
If you want a much faster split function look here. http://www.sqlservercentral.com/articles/72993/ — Sean Lange, Aug 18 '14 at 14:08

King King · Accepted Answer · 2014-08-18T15:48:35.923

There are many methods of splitting string in SQL Server you can find on the web, some are really complicated but some are just simple. I like the way of using dynamic query. It's just short and simple (not sure about the performance but I believe it would be not too bad):

declare @s varchar(max)
-- save the Column01 string/text into @s variable
select @s = Column01 from test where ID = 1
-- build the query string
set @s = 'select row_number() over (order by current_timestamp) as New_ID, c as New_Col from (values ('''
          + replace(@s, ',', '''),(''') + ''')) v(c)'

insert newTable exec(@s)    
go
select * from newTable

Sqlfiddle Demo

The use of values() clause above is some kind of anonymous table, here is a simple example of such usage (so that you can understand it better). The anonymous table in the following example has just 1 column, the table name is v and the column name is c. Each row has just 1 cell and should be wrapped in a pair of parentheses (). The rows are separated by commas and follow after values. Here is the code:

-- note about the outside (...) wrapping values ....
select * from (values ('a'),('b'),('c'), ('d')) v(c)

The result will be:

   c
------
1  a
2  b
3  c
4  d

Just try running that code and you'll understand how useful it is.

score 0 · Answer 2 · answered Aug 18 '14 at 23:44

You may want to use a tally table here. See http://www.sqlservercentral.com/articles/T-SQL/62867/

declare @parameter varchar(4000)

set @parameter = 'Express,,,Express,,,HyperMakert,,WebStore,Web'
set @parameter = ',' + @parameter + ',' -- add commas

with
    e1 as(select 1 as N union all select 1), -- 2 rows
    e2 as(select 1 as N from e1 as a, e1 as b), -- 4 rows
    e3 as(select 1 as N from e2 as a, e2 as b), -- 16 rows
    e4 as(select 1 as N from e3 as a, e3 as b), -- 256 rows
    e5 as(select 1 as N from e4 as a, e4 as b), -- 65536 rows
    tally as (select row_number() over(order by N) as N from e5
)
    select 
        substring(@parameter, N+1, charindex(',', @parameter, N+1) - N-1)
    from tally
    where 
        N < len(@parameter)
        and substring(@parameter, N, 1) = ','

How to Pivot one Row into One Column

2 Answers2

Sqlfiddle Demo