Search code examples
sqlsql-serverpivotsql-server-2014

split a string in a column and pivot in sql

I am using sql server 2014 and I have a table like this (##tt_allresults)

ID      |       Area        |       Event                                                                           |
1       |       FB1         |   Dev_Chg, Old Value: 0, New Value: 50, Workstation: Blah1, Function: Blah1 func      |
1       |       FB2         |   Dev_Chg, Old Value: 99, New Value: 5, Workstation: Blah2, Function: Blah2 func      |       
1       |       FB1         |   Dev_Chg, Old Value: 50, New Value: 55, Workstation: Blah1, Function: Blah1 func     |

I would like to from a table like so (Expected Output)

Area    |   Old Value   |       New Value   |       Function        |
FB1     |   0           |       50          |   Blah1 func          |   
FB2     |   99          |       5           |   Blah2 func          |   
FB1     |   50          |       55          |   Blah1 func          |

This is what i have tried so far 

Declare @id int
WHILE EXISTS(SELECT * FROM ##tt_allresults)
BEGIN
Select Top 1 @id = Id from ##tt_allresults

-- Do the work --
Declare @area nvarchar(100)
set @area = (Select Area from ##tt_allresults where id = @id)

Insert into ##tt_changedetails
select @area, * from fnsplit((Select [event] from ##tt_allresults where id = @id),',')

-- Scrap the ID and Move On --
Delete ##tt_allresults where ID = @id
END


select * from ##tt_changedetails

I get the following result

Area    |       ChangeDetails       |
FB1     |   Dev_Chg                 |
FB1     |   Old value :0            |
FB1     |   New Value :50           |
FB1     |   Workstation :blah1      |
FB1     |   Function :blah1 func    |
FB2     |   Dev_Chg                 |
FB2     |   Old value :99           |
FB2     |   New Value :5            |
FB2     |   Workstation :blah2      |
FB2     |   Function :blah2 func    |
FB1     |   Dev_Chg                 |
FB1     |   Old value :50           |
FB1     |   New Value :55           |
FB1     |   Workstation :blah1      |
FB1     |   Function :blah1 func    |

How do i split my initial table and pivot it based on the split. I want to see the following result

FB1     |   0           |       50          |   Blah1 func          |   
FB2     |   99          |       5           |   Blah2 func          |   
FB1     |   50          |       55          |   Blah1 func          |
Solution

  • No need for a UDF, can all be done with the help of a CROSS APPLY and an a little XML

    You can expand or contract as needed. I left 9 Positions to illustrate

    1) Without a Function

    Declare @YourTable table (ID int,Area varchar(25),Event varchar(500))
Insert Into @YourTable values
(1,'FB1','Dev_Chg, Old Value: 0, New Value: 50, Workstation: Blah1, Function: Blah1 func'),
(1,'FB2','Dev_Chg, Old Value: 99, New Value: 5, Workstation: Blah2, Function: Blah2 func'),
(1,'FB1','Dev_Chg, Old Value: 50, New Value: 55, Workstation: Blah1, Function: Blah1 func')

Select A.Area
      ,[Old Value] = Substring(Pos2,CharIndex(':',Pos2)+1,Len(Pos2))
      ,[New Value] = Substring(Pos3,CharIndex(':',Pos3)+1,Len(Pos3))
      ,[Function]  = Substring(Pos5,CharIndex(':',Pos5)+1,Len(Pos5))
 From  @YourTable A
 Cross Apply (
                Select Pos1 = ltrim(rtrim(xDim.value('/x[1]','varchar(max)')))
                      ,Pos2 = ltrim(rtrim(xDim.value('/x[2]','varchar(max)')))
                      ,Pos3 = ltrim(rtrim(xDim.value('/x[3]','varchar(max)')))
                      ,Pos4 = ltrim(rtrim(xDim.value('/x[4]','varchar(max)')))
                      ,Pos5 = ltrim(rtrim(xDim.value('/x[5]','varchar(max)')))
                      ,Pos6 = ltrim(rtrim(xDim.value('/x[6]','varchar(max)')))
                      ,Pos7 = ltrim(rtrim(xDim.value('/x[7]','varchar(max)')))
                      ,Pos8 = ltrim(rtrim(xDim.value('/x[8]','varchar(max)')))
                      ,Pos9 = ltrim(rtrim(xDim.value('/x[9]','varchar(max)')))
                 From  (Select Cast('<x>' + replace((Select A.Event as [*] For XML Path('')),',','</x><x>')+'</x>' as xml) as xDim) as A 
       ) B

    Returns

    Area    Old Value   New Value   Function
FB1     0           50          Blah1 func
FB2     99          5           Blah2 func
FB1     50          55          Blah1 func

    Or 2 With A Function

    Select A.Area
      ,[Old Value] = Substring(Pos2,CharIndex(':',Pos2)+1,Len(Pos2))
      ,[New Value] = Substring(Pos3,CharIndex(':',Pos3)+1,Len(Pos3))
      ,[Function]  = Substring(Pos5,CharIndex(':',Pos5)+1,Len(Pos5))
 From  @YourTable A
 Cross Apply [dbo].[udf-Str-Parse-Row](A.Event,',') B

    The UDF if needed

    ALTER FUNCTION [dbo].[udf-Str-Parse-Row] (@String varchar(max),@Delimiter varchar(10))
Returns Table 
As
Return (
    Select Pos1 = ltrim(rtrim(xDim.value('/x[1]','varchar(max)')))
            ,Pos2 = ltrim(rtrim(xDim.value('/x[2]','varchar(max)')))
            ,Pos3 = ltrim(rtrim(xDim.value('/x[3]','varchar(max)')))
            ,Pos4 = ltrim(rtrim(xDim.value('/x[4]','varchar(max)')))
            ,Pos5 = ltrim(rtrim(xDim.value('/x[5]','varchar(max)')))
            ,Pos6 = ltrim(rtrim(xDim.value('/x[6]','varchar(max)')))
            ,Pos7 = ltrim(rtrim(xDim.value('/x[7]','varchar(max)')))
            ,Pos8 = ltrim(rtrim(xDim.value('/x[8]','varchar(max)')))
            ,Pos9 = ltrim(rtrim(xDim.value('/x[9]','varchar(max)')))
     From  (Select Cast('<x>' + replace((Select @String as [*] For XML Path('')),@Delimiter,'</x><x>')+'</x>' as xml) as xDim) as A 
)
--Select * from [dbo].[udf-Str-Parse-Row]('Dog,Cat,House,Car',',')
--Select * from [dbo].[udf-Str-Parse-Row]('John <test> Cappelletti',' ')

    If it helps with the visualization, the CROSS APPLY (which can easily be a TVF) produces the following

    enter image description here