MiaWal MiaWal - 1 month ago 13
SQL Question

How to split dynamic column values to dynamic multiple column values

I have a problem splitting dynamic column values to dynamic multiple column.

i have table like this:

media campaign number --- Dynamic varchar
-----------------------------------------------------
1 --- d1:11d2:5d3:11d4:0
2 --- d1:2d2:2d3:3d4:2d5:0
3 --- d1:0d2:0d3:1d4:4d5:0d6:2d7:1


I want to separate the cell into dynamic number of columns, according to times the "dNumber" appear.

The result will be :

media campaign number -- d1 - d2 - d3 - d4 - d5 - d6 - d7
1 - 11 - 5 - 11 - 0
2 - 2 - 2 - 3 - 2 - 0
3 - 0 - 0 - 1 - 4 - 0 - 2 - 1


Any idea how to solve it?

Answer

I suspect you may have to go dynamic unless you can to define a max number of columns.

The following uses a Parse/Solit function

Declare @YourTable table (ID int, [campaign number] varchar(max))
Insert Into @YourTable values
(1,'d1:11d2:5d3:11d4:0'),
(2,'d1:2d2:2d3:3d4:2d5:0'),
(3,'d1:0d2:0d3:1d4:4d5:0d6:2d7:1')

Select A.ID
      ,ColSeq   = cast(B.RetSeq-1 as varchar(25))
      ,ColValue = IIF(CharIndex('d',RetVal)=0,RetVal,Left(RetVal,CharIndex('d',RetVal)-1))
Into  #Temp
From  @YourTable A
Cross Apply (Select * from [dbo].[udf-Str-Parse](A.[campaign number],':') Where RetSeq>1) B

Declare @SQL varchar(max) = ''
Select @SQL=@SQL+',D'+ColSeq+'=max(case when ColSeq='+ColSeq+' then ColValue else null end)' 
  From (Select Top 100 Percent ColSeq from #Temp Group By ColSeq Order By cast(ColSeq as int)) A

Select @SQL = 'Select ID'+@SQL+' From #Temp Group By ID Order By ID'
Exec(@SQL)

Returns

enter image description here

The Parse Function if needed

CREATE FUNCTION [dbo].[udf-Str-Parse] (@String varchar(max),@Delimiter varchar(10))
Returns Table 
As
Return (  
    Select RetSeq = Row_Number() over (Order By (Select null))
          ,RetVal = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
    From (Select x = Cast('<x>'+ Replace(@String,@Delimiter,'</x><x>')+'</x>' as xml).query('.')) as A 
    Cross Apply x.nodes('x') AS B(i)
);
Comments