SQL, Split by 1 column and duplicate other columns

Question

Good day!

Maybe you can help me, or tell me if what I want to do is impossible or totally wrong...

I was trying to create a sqlfiddle but it seems the page is down at the moment.

(SQL Server 2008) I have a table, lets say that it has 3 columns, but the person who designed it didn't normalize, so one column holds multiple values, it's something like this:

IdCol    Col1    Col2    Col3
  1       a1      b1     a, b, c
  2       a2      b2     d, e, f

As you can see, Col3 holds multiple values separated by ","

what I want to achieve, is to create a view (can't modify the table because they won't allow me to modify the application) that is something similar to this:

NewIdCol     IdCol    Col1     Col2     Col3
1            1        a1       b1       a
2            1        a1       b1       b
3            1        a1       b1       c
4            2        a2       b2       d
5            2        a2       b2       e
6            2        a2       b2       f

So the final result has Col3 values split into a different row and every other column's value copied. (the actual table has about 20 columns, and 2 of those columns hold multiple values, so I would need to do it for both columns)

At first I thought it would be easy... but then I hit a block on how to split that string... first I thought about using a split function, but then I didn't know how to join it back with the rest of the columns...

Thanks in advance.

Ideally you will want to stop storing multiple values in a single column. This violates 1NF. In the meantime look at this article. http://sqlperformance.com/2012/07/t-sql-queries/split-strings — Sean Lange, Mar 05 '15 at 15:41
Possible duplicate with: http://stackoverflow.com/q/10581772/4584335 take a closer look to the answer with the `Split` function, it's exactly designed for what you're asking for. — Rubik, Mar 05 '15 at 15:52

score 3 · Accepted Answer · answered Mar 05 '15 at 15:54

You need to have a function for splitting comma-delimited strings into separate rows. Then you call the function like this:

SELECT
    NewIdCol = ROW_NUMBER() OVER(ORDER BY t.IdCol, x.ItemNumber),
    t.IdCol,
    t.Col1,
    t.Col2,
    x.Item
FROM Test t
CROSS APPLY [dbo].[DelimitedSplit8K](t.Col3, ',') x

Here is the DelimitedSplit8K function by Jeff Moden.

CREATE FUNCTION [dbo].[DelimitedSplit8K](
    @pString NVARCHAR(4000), @pDelimiter NCHAR(1)
)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
WITH E1(N) AS (
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
)
,E2(N) AS (SELECT 1 FROM E1 a, E1 b)
,E4(N) AS (SELECT 1 FROM E2 a, E2 b)
,cteTally(N) AS(
    SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
)
,cteStart(N1) AS(
    SELECT 1 UNION ALL 
    SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@pString,t.N,1) = @pDelimiter
),
cteLen(N1,L1) AS(
SELECT 
    s.N1,
    ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000)
FROM cteStart s
)
SELECT 
    ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),
    Item       = SUBSTRING(@pString, l.N1, l.L1)
FROM cteLen l
;

Awesome!, cross apply... never thought of that, also, thank you for the split function, way better than what we have here. — figus, Mar 05 '15 at 16:37

SQL, Split by 1 column and duplicate other columns

1 Answers1