CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008-r2
geoffb-csharpguy
geoffb-csharpguy

Reputation: 70

Sql Query Join on Comma Separated Value

I have a table that has a composite key and a comma separated value. I need the single row split into one row for each comma separated element. I have seen similar questions and similar answers but have not been able to translate them into a solution for myself.

I'm running SQL Server 2008 R2.

| Key Part 1 | Key Part 2 | Key Part 3 | Values        |
|------------------------------------------------------|
| A          | A          | A          | PDE,PPP,POR   |
| A          | A          | B          | PDE,XYZ       |
| A          | B          | A          | PDE,RRR       |
|------------------------------------------------------|

and I need this as output

| Key Part 1 | Key Part 2 | Key Part 3 | Values        | Sequence   |
|-------------------------------------------------------------------|
| A          | A          | A          | PDE           | 0          |
| A          | A          | A          | PPP           | 1          | 
| A          | A          | A          | POR           | 2          |
| A          | A          | B          | PDE           | 0          |
| A          | A          | B          | XYZ           | 1          |
| A          | B          | A          | PDE           | 0          |
| A          | B          | A          | RRR           | 1          |
|-------------------------------------------------------------------|

Thanks

Geoff

Upvotes: 1

Views: 229

Answers (3)

Shailesh Mistry
Shailesh Mistry

Reputation: 1

-- Create Table 

Create table YourTable 
(
p1 varchar(50),
p2 varchar(50),
p3 varchar(50),
pval varchar(50)
)
go

-- Insert Data 

    insert into YourTable values ('A','A','A','PDE,PPP,POR'),
('A','A','B','PDE,XYZ'),('A','B','A','PDE,RRR')

    go

-- View Sample Data 

SELECT p1, p2, p3 , pval FROM YourTable
go

-- Required Result 

SELECT p1,p2,p3,  LTRIM(RTRIM(Split.a.value('.', 'VARCHAR(100)'))) as Value1 , ROW_NUMBER() OVER(PARTITION BY id ORDER BY id ASC)-1 AS SequenceNo
FROM  
(SELECT ROW_NUMBER() over (order by (SELECT NULL)) AS ID, p1,p2,p3, pval, CAST ('<M>' + REPLACE(pval, ',', '</M><M>') + '</M>' AS XML) AS Data from YourTable 
) AS A 
CROSS APPLY Data.nodes ('/M') AS Split(a)
go

-- Remove Temp created table

drop table YourTable
go

Upvotes: 0

Jason A. Long
Jason A. Long

Reputation: 4442

If all CSV values are exactly 3 characters (as you have in your test data) you can use a a tally table in an incredibly efficient manner by creating the exact number of rows needed up front (as opposed to creating a row for every character to find the delimiter character)... because you already know the delimiter location.

In this case, I'll use a tally function but you can use a fixed tally table as well.

Code for the tfn_Tally function...

 SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
CREATE FUNCTION dbo.tfn_Tally
/* ============================================================================
07/20/2017 JL, Created. Capable of creating a sequense of rows 
                ranging from -10,000,000,000,000,000 to 10,000,000,000,000,000
============================================================================ */
(
    @NumOfRows BIGINT,
    @StartWith BIGINT 
)
RETURNS TABLE WITH SCHEMABINDING AS 
RETURN
    WITH 
        cte_n1 (n) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (n)),   -- 10 rows
        cte_n2 (n) AS (SELECT 1 FROM cte_n1 a CROSS JOIN cte_n1 b),                             -- 100 rows
        cte_n3 (n) AS (SELECT 1 FROM cte_n2 a CROSS JOIN cte_n2 b),                             -- 10,000 rows
        cte_n4 (n) AS (SELECT 1 FROM cte_n3 a CROSS JOIN cte_n3 b),                             -- 100,000,000 rows
        cte_Tally (n) AS (
            SELECT TOP (@NumOfRows)
                (ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1) + @StartWith
            FROM 
                cte_n4 a CROSS JOIN cte_n4 b                                                    -- 10,000,000,000,000,000 rows
            )
    SELECT 
        t.n
    FROM 
        cte_Tally t;
GO

How to use it in the solution...

-- create some test data...
IF OBJECT_ID('tempdb..#TestData', 'U') IS NOT NULL 
DROP TABLE #TestData;

CREATE TABLE #TestData (
    KeyPart1 CHAR(1),
    KeyPart2 CHAR(1),
    KeyPart3 CHAR(1),
    [Values] varchar(50) 
    );

INSERT #TestData (KeyPart1, KeyPart2, KeyPart3, [Values]) VALUES 
    ('A', 'A', 'A', 'PDE,PPP,POR'),
    ('A', 'A', 'B', 'PDE,XYZ'),
    ('A', 'B', 'A', 'PDE,RRR,XXX,YYY,ZZZ,AAA,BBB,CCC');

--==========================================================

-- solution query...
SELECT 
    td.KeyPart1, 
    td.KeyPart2, 
    td.KeyPart3, 
    x.SplitValue,
    [Sequence] = t.n
FROM
    #TestData td
    CROSS APPLY dbo.tfn_Tally(LEN(td.[Values]) - LEN(REPLACE(td.[Values], ',', '')) + 1, 0) t
    CROSS APPLY ( VALUES (SUBSTRING(td.[Values], t.n * 4 + 1, 3)) ) x (SplitValue);

And the results...

KeyPart1 KeyPart2 KeyPart3 SplitValue Sequence
-------- -------- -------- ---------- --------------------
A        A        A        PDE        0
A        A        A        PPP        1
A        A        A        POR        2
A        A        B        PDE        0
A        A        B        XYZ        1
A        B        A        PDE        0
A        B        A        RRR        1
A        B        A        XXX        2
A        B        A        YYY        3
A        B        A        ZZZ        4
A        B        A        AAA        5
A        B        A        BBB        6
A        B        A        CCC        7

If the assumption that all of the csv elements are the number of characters is incorrect, you'd be better off using a traditional tally based splitter. In which case my recommendation is DelimitedSplit8K written by Jeff Moden.

In that case, the solution query would look like this...

SELECT 
    td.KeyPart1, 
    td.KeyPart2, 
    td.KeyPart3, 
    SplitValue = dsk.Item,
    [Sequence] = dsk.ItemNumber - 1
FROM
    #TestData td
    CROSS APPLY dbo.DelimitedSplit8K(td.[Values], ',') dsk;

Ann the result...

KeyPart1 KeyPart2 KeyPart3 SplitValue Sequence
-------- -------- -------- ---------- --------------------
A        A        A        PDE        0
A        A        A        PPP        1
A        A        A        POR        2
A        A        B        PDE        0
A        A        B        XYZ        1
A        B        A        PDE        0
A        B        A        RRR        1
A        B        A        XXX        2
A        B        A        YYY        3
A        B        A        ZZZ        4
A        B        A        AAA        5
A        B        A        BBB        6
A        B        A        CCC        7

HTH, Jason

Upvotes: 0

John Cappelletti
John Cappelletti

Reputation: 81930

Here is a simple inline approach if you don't have or want a Split/Parse UDF

Example

Select A.[Key Part 1]
      ,A.[Key Part 2]
      ,A.[Key Part 3]
      ,B.*
 From YourTable A
 Cross Apply (
                Select [Values]   = LTrim(RTrim(X2.i.value('(./text())[1]', 'varchar(max)')))
                      ,[Sequence] = Row_Number() over (Order By (Select null))-1
                From  (Select x = Cast('<x>' + replace(A.[Values],',','</x><x>')+'</x>' as xml)) X1 
                Cross Apply x.nodes('x') X2(i)
             ) B

Returns

enter image description here

EDIT - If Open to a Table-Valued Function

The Query would Look Like This

Select A.[Key Part 1]
      ,A.[Key Part 2]
      ,A.[Key Part 3]
      ,[Values] = B.RetVal
      ,[Sequence] = B.RetSeq-1
 From @YourTable A
 Cross Apply [dbo].[udf-Str-Parse-8K](A.[Values],',') B

The UDF if Interested

CREATE FUNCTION [dbo].[udf-Str-Parse-8K] (@String varchar(max),@Delimiter varchar(25))
Returns Table 
As
Return (  
    with   cte1(N)   As (Select 1 From (Values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N(N)),
           cte2(N)   As (Select Top (IsNull(DataLength(@String),0)) Row_Number() over (Order By (Select NULL)) From (Select N=1 From cte1 a,cte1 b,cte1 c,cte1 d) A ),
           cte3(N)   As (Select 1 Union All Select t.N+DataLength(@Delimiter) From cte2 t Where Substring(@String,t.N,DataLength(@Delimiter)) = @Delimiter),
           cte4(N,L) As (Select S.N,IsNull(NullIf(CharIndex(@Delimiter,@String,s.N),0)-S.N,8000) From cte3 S)

    Select RetSeq = Row_Number() over (Order By A.N)
          ,RetVal = LTrim(RTrim(Substring(@String, A.N, A.L)))
    From   cte4 A
);
--Orginal Source http://www.sqlservercentral.com/articles/Tally+Table/72993/
--Select * from [dbo].[udf-Str-Parse-8K]('Dog,Cat,House,Car',',')
--Select * from [dbo].[udf-Str-Parse-8K]('John||Cappelletti||was||here','||')

Upvotes: 4

Related Questions