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
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
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.