How to split dynamic column values to dynamic multiple column values

Question

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?

Presumably you have a maximum number of columns you want, such as 7. — Gordon Linoff
– Gordon Linoff, Commented Oct 30, 2016 at 14:38

John Cappelletti · Accepted Answer · 2016-10-30 15:17:05Z

2

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

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)
);

answered Oct 30, 2016 at 15:17

John Cappelletti

82.3k7 gold badges50 silver badges75 bronze badges

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

2 Comments

MiaWal Over a year ago

Thank u. it's Work. do you have any documentation about your solution?

John Cappelletti Over a year ago

@MiaWal Run Select * from [dbo].[udf-Str-Parse]('d1:11d2:5d3:11d4:0',':') just to visualize what the parser does. The query will apply the parse for each record. This normalized data set is dropped into #Temp then we dynamically create the columns to pivot.

Collectives™ on Stack Overflow

How to split dynamic column values to dynamic multiple column values

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related