5

Say I have a query that returns the following

ID       SomeValue
1        a,b,c,d
2        e,f,g

Id like to return this as follows:

ID       SomeValue
1        a
1        b
1        c
1        d
2        e
2        f
2        g

I already have a UDF calls Split that will accept a string and a delimter and return it as a table with a single column called [Value]. Given this, How shoudl the SQL look to achieve this?

Stewart Alan
  • 1,521
  • 5
  • 23
  • 45
  • possible duplicate of [Tsql split string](http://stackoverflow.com/questions/10914576/tsql-split-string) – Karl Kieninger Jun 18 '15 at 16:23
  • @AlvinThompson I believe it has actually been several brazillion times ;-). – Solomon Rutzky Jun 18 '15 at 18:09
  • Actually that question doesn't quite answer this one. I already have a function that splits the string. What I wanted is a single query that would return the results from one table split into a second table. Anyway, answer below did the trick – Stewart Alan Jun 19 '15 at 09:00

3 Answers3

9

Alternatively, you could use XML like so:

DECLARE @yourTable TABLE(ID INT,SomeValue VARCHAR(25));

INSERT INTO @yourTable
VALUES  (1,'a,b,c,d'),
        (2,'e,f,g');

WITH CTE
AS
(
    SELECT  ID,
            [xml_val] = CAST('<t>' + REPLACE(SomeValue,',','</t><t>') + '</t>' AS XML)
    FROM @yourTable
)

SELECT  ID,
        [SomeValue] = col.value('.','VARCHAR(100)')
FROM CTE
CROSS APPLY [xml_val].nodes('/t') CA(col)
Stephan
  • 5,891
  • 1
  • 16
  • 24
  • 1
    This is the simplest example of using XML for this I've seen among all the various answers around this site so far. Thanks! – 4AM Apr 22 '16 at 18:30
3

You use cross apply. Something like this:

select t.id, s.val as SomeValue
from table t cross apply
     dbo.split(SomeValue, ',') as s(val);
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • could you share the "dbo.split" function you used ? – B3S Jun 20 '17 at 10:51
  • @B3S . . . SQL Server offers one in 2016. Otherwise, you can get them easily by Googling "SQL Server split". – Gordon Linoff Jun 20 '17 at 12:09
  • well, the correct sys function to use is `STRING_SPLIT` and not `dbo.split`. https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql – GoldBishop Oct 26 '17 at 01:31
  • yeah `dbo.split` is usually a user defined function, in most of the implementations. Just putting my 2-cents that in 2016, there is a system function that does what we have had to implement. – GoldBishop Oct 26 '17 at 02:08
0

I know this is an older post but I wanted to add my solution so that I can find it in the future. I had to make a slight tweak to Stephan's Solution to account for values that do NOT contain a delimiter:

DECLARE @yourTable TABLE(ID INT,SomeValue VARCHAR(25));

INSERT INTO @yourTable
VALUES  (1,'a,b,c,d'),
        (2,'e'),
        (3,'f'),
        (4,'g,h,i');

WITH CTE
AS
(
    SELECT  ID,
            [xml_val] = CAST('<t>' +
                                CASE WHEN  CHARINDEX(',', SomeValue) > 0
                                    THEN REPLACE(SomeValue,',','</t><t>') 
                                    ELSE SomeValue
                                END + '</t>' AS XML)
    FROM @yourTable
)

SELECT  ID,
        [SomeValue] = col.value('.','VARCHAR(100)')
FROM CTE
CROSS APPLY [xml_val].nodes('/t') CA(col)
Community
  • 1
  • 1
Mark Kram
  • 5,672
  • 7
  • 51
  • 70
  • Thanks for looking at this Mark, but did you try testing my solution for values without delimiters? You'll notice that my solution accounts for that by adding at '' and '' in the CAST expression so it will work regardless of there being any delimiters or not. Yours will work, but I believe it adds unnecessary work – Stephan Mar 25 '17 at 23:37
  • Hmmm, I was getting an error (more than likely a data issue now I think of it) and I was pressed for time do I just need to make this work for me. I will try it again next week and let you know. – Mark Kram Mar 26 '17 at 00:43