CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2005parsing
Phil Murray
Phil Murray

Reputation: 6554

Splitting a string then pivoting result

If have a string passed from a .Net application that looks like the below 

2023|F66451,1684|648521,1684|600271,2137|019592

I have started to parse out the string using the method below but I need to Pivot the data returned from the Split ( surrounded by *'s) function in order to insert into the #tmpExceptions table

DECLARE @ExceptionsList as nvarchar(MAX)

SET @ExceptionsList = '2023|F66451,1684|648521,1684|600271,2137|019592'

SET NOCOUNT ON;

DECLARE @CurrentLineItem as nvarchar(255)

CREATE TABLE #ParsePassOne
(
    LineItem nvarchar(255)
)

CREATE TABLE #tmpExceptions
(
    AccountNumber int,
    ClaimNumber nvarchar(50)
)

INSERT INTO #ParsePassOne
    SELECT value FROM Split( ',' ,@ExceptionsList)

WHILE EXISTS(SELECT LineItem FROM #ParsePassOne)
    BEGIN
        SELECT TOP 1 @CurrentLineItem = LineItem FROM #ParsePassOne

        *******
            SELECT value FROM Split( '|' ,@CurrentLineItem) 
            *******

        DELETE FROM #ParsePassOne WHERE LineItem = @CurrentLineItem
    END

SELECT * FROM #tmpExceptions

DROP TABLE #ParsePassOne
DROP TABLE #tmpExceptions

So far the data returned looks as below. I just need to pivot the data to columns so I can insert it. How do I go about this?

enter image description here

Split Function

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--Creates an 'InLine' Table Valued Function (TVF)
ALTER FUNCTION [dbo].[Split] 
   (  @Delimiter varchar(5), 
      @List      varchar(8000)
   ) 
   RETURNS @TableOfValues table 
      (  RowID   smallint IDENTITY(1,1), 
         [Value] varchar(50) 
      ) 
AS 
   BEGIN

      DECLARE @LenString int 

      WHILE len( @List ) > 0 
         BEGIN 

            SELECT @LenString = 
               (CASE charindex( @Delimiter, @List ) 
                   WHEN 0 THEN len( @List ) 
                   ELSE ( charindex( @Delimiter, @List ) -1 )
                END
               ) 

            INSERT INTO @TableOfValues 
               SELECT substring( @List, 1, @LenString )

            SELECT @List = 
               (CASE ( len( @List ) - @LenString ) 
                   WHEN 0 THEN '' 
                   ELSE right( @List, len( @List ) - @LenString - 1 ) 
                END
               ) 
         END

      RETURN 

   END

Upvotes: 3

Views: 12257

Answers (3)

MariusE
MariusE

Reputation: 66

I had a similar problem, where I needed to split and pivot the values in a column, and I needed to have it all in a view.

I came up with the following code (for SQL 2016 and up)

/* Table */
DECLARE @data TABLE (id INT IDENTITY(1,1), [Name] VARCHAR(128) NOT NULL, [Selection] VARCHAR(512) NULL)

INSERT INTO @data ([Name],Selection)
VALUES('Bob','PC; Mouse; Keyboard; Network; Smartphone'),
('Mo','Violin; Hammer'),
('Jon','Magic; Blood; Teleporter'),
('Mhary','Vampire')

/* Pivot */
;WITH Data_RowNumber AS (
    SELECT
        id,
        [name],
        split.value,
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY id) AS RowNumber
    FROM @data
    CROSS APPLY STRING_SPLIT([Selection],';') AS split
)
SELECT 
    id,
    [name],
    [1],[2],[3],[4],[5],[6]
FROM Data_RowNumber
PIVOT (
    MAX([value])
    FOR [RowNumber] IN ([1],[2],[3],[4],[5],[6])
    ) AS p

Upvotes: 2

Kannan Kandasamy
Kannan Kandasamy

Reputation: 13969

If you are using SQL Server 2016 you can use String_Split() function and use cross apply/pivot to get into single row

create table #t (v varchar(50), i int)
insert into #t (v, i) values ('2023|F66451',1)
,('1684|648521',2), ('1684|600271', 3), ('2137|019592', 4)

--Inorder to get into same row -pivoting the data
select * from (
select * from #t t cross apply (select RowN=Row_Number() over (Order by (SELECT NULL)), value from string_split(t.v, '|') ) d) src
pivot (max(value) for src.RowN in([1],[2])) p

Upvotes: 6

podiluska
podiluska

Reputation: 51504

You can replace your WHILE EXISTS(SELECT LineItem FROM #ParsePassOne) loop with 

select *
from
(
select * from #parsepassone
    cross apply dbo.Split( '|' ,lineitem)  
) src
pivot
(max(value) for rowid in ([1],[2]))p

Or replace the whole thing with

insert #tmpExceptions (AccountNumber, ClaimNumber)
select [1],[2]
from
(
select e.rowid e, p.* from dbo.Split( ',' ,@ExceptionsList) e
    cross apply dbo.Split( '|' ,e.value) p ) s
pivot
(max(value) for rowid in ([1],[2]))p

Upvotes: 3

Related Questions