Split and Dynamic Pivot with Case


  

create table test ( strCol varchar(2000))
insert into test values( '|Xyz,Type1,03/01/2018 10:00 PM,L1,P,-1|Abc,Type2,03/01/2018 10:00 PM,L1,A,1|Mno,NA,03/01/2018 10:00 PM,L2,P,-1') 
,( '|Xyz2,Type1,03/01/2018 10:00 PM,L1,P,-1|Abc2,Type2,03/01/2018 10:00 PM,L1,A,1|Mno2,NA,03/01/2018 10:00 PM,L2,P,-1') 
 
 
--===== Create number table on-the-fly
;WITH Num1 (n) AS (
SELECT 1 as n
UNION ALL SELECT n+1 as n
FROM Num1 Where n<101),
Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num2)

--load number table
Select * into NumsTable
from Nums
 
;with mycte as (
select  strCol, n,  
substring(strCol, n, charindex('|', strCol + '|', n) - n)  splitData 
 
from test   
cross apply NumsTable
Where n <= len(strCol) AND substring('|' + strCol, n, 1) = '|'
)
--Load data into temp table 1
Select  IDENTITY(int, 1,1) id, * 
into mytemp1
from   mycte



;with mycte1 as (

select id, splitData, m.n
, row_number()Over(partition by id  Order by id,m.n) rn2
, substring(splitData, Nums.n, charindex(',', splitData + ',', Nums.n) - Nums.n)  splitData2 

from mytemp1 m  
cross apply NumsTable nums
Where Nums.n <= len(splitData) AND substring(',' + splitData, Nums.n, 1) = ','
)

--Load data into temp table 2
 Select *  into mytemp2
 from mycte1
 

 --dynamic pivot with CASE
 Declare @sql nvarchar(max);
Declare @ColumnHeaders VARCHAR(MAX) 
SET @ColumnHeaders = STUFF( (SELECT DISTINCT ',' + 'Max(CASE WHEN rn2=' + cast(rn2 as varchar(5)) 
+ ' THEN splitData2 else null end ) as ' + quotename(rn2,'[')  + char(10)+char(13)
  FROM mytemp2
 FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '');
 --print @ColumnHeaders

 set @sql = N'SELECT ' + @ColumnHeaders + '  FROM   mytemp2  group by id ';
--print @sql
exec(@sql);
 
 --clean up
drop table  mytemp2, mytemp1,NumsTable
drop table test


--dynamic pivot query
 create table test ( strCol varchar(2000))
insert into test values( '|Xyz,Type1,03/01/2018 10:00 PM,L1,P,-1|Abc,Type2,03/01/2018 10:00 PM,L1,A,1|Mno,NA,03/01/2018 10:00 PM,L2,P,-1') 
,( '|Xyz2,Type1,03/01/2018 10:00 PM,L1,P,-1|Abc2,Type2,03/01/2018 10:00 PM,L1,A,1|Mno2,NA,03/01/2018 10:00 PM,L2,P,-1') 
 
 
--===== Create number table on-the-fly
;WITH Num1 (n) AS (
SELECT 1 as n
UNION ALL SELECT n+1 as n
FROM Num1 Where n<101),
Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num2)

Select * into NumsTable
from Nums
 
;with mycte as (
select  strCol, n,row_number()Over(  Order by strCol) rn1,  
substring(strCol, n, charindex('|', strCol + '|', n) - n)  splitRole 
 
from test   
cross apply NumsTable
Where n <= len(strCol) AND substring('|' + strCol, n, 1) = '|'
)

Select  IDENTITY(int, 1,1) id, * 
into mytemp1
from   mycte
;with mycte1 as (

select id, splitRole,rn1, m.n, row_number()Over(partition by id,rn1 Order by id,m.n) rn2, substring(splitRole, Nums.n, charindex(',', splitRole + ',', Nums.n) - Nums.n)  splitRole2 

from mytemp1 m  
cross apply NumsTable nums
Where Nums.n <= len(splitRole) AND substring(',' + splitRole, Nums.n, 1) = ','
)
 Select *  into mytemp2
 from mycte1
 

 --dynamic
 Declare @sql nvarchar(max);
 Declare @cols varchar(max);
  Declare @cols2 varchar(max);
 set @cols = STUFF((SELECT ',max(' + QUOTENAME(rn2)  +') as ' +QUOTENAME(rn2)
                    FROM mytemp2
                   group by  rn2
                    order by  rn2
                    FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,'') ;
 
set @cols2 = STUFF((SELECT ',' + QUOTENAME(rn2)   
                    FROM mytemp2
                    group by rn2
                    order by rn2
                    FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,'') ;
 
 set @sql = N'SELECT id,' + @cols + ' 
            FROM   mytemp2
            PIVOT (MAX(splitRole2) FOR rn2 IN (' + @cols2 + ')) p group by id ';
print @sql
exec(@sql);
 
  
 --select * from mytemp1
 --select * from mytemp2

drop table  mytemp2, mytemp1,NumsTable
drop table test

 

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/8189de40-33f5-4814-a836-5911d435a99b/split-strings-into-a-table-without-loop-in-sql-server-2008r2?forum=transactsql

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s