Post reply

Split comma-delimited field for all rows in table

  • July 2, 2012 at 8:05 am

    #258739

    I'm sure I'm missing something obvious, but I just can't figure out the best way to make this happen. I have a table of which one column is a comma-delimited list of IDs. I know how to split a comma-delimited value into a rowset using one of the string-to-table solutions, but how can I do that in a SELECT query for all rows in the table.

    For example, if I have a table like:

    USER IDs

    Tom 1,2,3

    Sue 4,5,6

    I need a query to return a result set like:

    USER ID

    Tom 1

    Tom 2

    Tom 3

    Sue 4

    Sue 5

    Sue 6

    Suggestions? Thanks in advance...

  • July 2, 2012 at 8:25 am

    #1507749

    With almost 3,000 points it is hard to believe you didn't post any consumable ddl and sample data.

    Take a look at the link in my signature about splitting strings. It will provide you the answer you seek.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • July 2, 2012 at 8:27 am

    #1507750

    Thanks... I'll check out your link. BTW, this is a super-simplified example of my real problem which is for a system on a classified network. That's why the details are sketchy.

    Thanks... Steve

  • July 2, 2012 at 8:30 am

    #1507751

    milzs (7/2/2012)

    Thanks... I'll check out your link. BTW, this is a super-simplified example of my real problem which is for a system on a classified network. That's why the details are sketchy.

    Thanks... Steve

    I would never expect real data. 😛 The point is you typed up data but nobody can use it. The concept is the same wether the data is real or not.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • July 2, 2012 at 8:40 am

    #1507758

    milzs (7/2/2012)

    Thanks... I'll check out your link. BTW, this is a super-simplified example of my real problem which is for a system on a classified network. That's why the details are sketchy.

    Thanks... Steve

    Here is what I mean.

    Sample data and ddl all in one.

    ;with SampleData (UserName, IDs)

    as

    (

    select 'Tom', '1,2,3' union all

    select 'Sue', '4,5,6'

    )

    Then to make it easy to see your desired results:

    create table #Results

    (

    UserName varchar(5),

    IDs varchar(10)

    )

    insert #Results

    select 'Tom', '1' union all

    select 'Tom', '2' union all

    select 'Tom', '3' union all

    select 'Sue', '4' union all

    select 'Sue', '5' union all

    select 'Sue', '6'

    select * from #Results

    Then when seeing a post that somebody took the time to formulate nicely I might respond with something like:

    Thanks for the ddl and sample data. The following will produce your expected results based on the desired output you posted.

    select sd.UserName, s.Item

    from SampleData sd

    cross apply dbo.DelimitedSplit8K(IDs, ',') s

    You can find the code for DelimitedSplit8K in the string splitting link in my signature. You should read that article completely so you understand that function because it will be you that has to support it.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • July 2, 2012 at 8:40 am

    #1507759

    declare @t table (UserName varchar(3), ids varchar(10))

    insert into @t values ('Tom', '1,2,3')

    insert into @t values ('Sue', '4,5,6')

    select *

    from @t t

    cross apply dbo.BuildKeyTableForDelimitedString(t.ids)

  • July 2, 2012 at 9:14 am

    #1507775

    Bill Talada (7/2/2012)

    declare @t table (UserName varchar(3), ids varchar(10))

    insert into @t values ('Tom', '1,2,3')

    insert into @t values ('Sue', '4,5,6')

    select *

    from @t t

    cross apply dbo.BuildKeyTableForDelimitedString(t.ids)

    If you are going to use a user-defined function in your answer, you really should provide the code for the function as well. What good does your answer do without the function?

  • July 2, 2012 at 9:41 am

    #1507789

    Many thanks everyone. I have this working like a champ...

    Steve

  • July 2, 2012 at 10:18 am

    #1507818

    @ Lynn Pettis

    I don't remember you posting code for your 8k splitter. We all knew there was a general splitter function being used. It doesn't matter how it is implemented.

  • July 2, 2012 at 10:20 am

    #1507819

    Bill Talada (7/2/2012)

    @ Lynn Pettis

    I don't remember you posting code for your 8k splitter. We all knew there was a general splitter function being used. It doesn't matter how it is implemented.

    to come to the defense of lynn here is the quote

    You can find the code for DelimitedSplit8K in the string splitting link in my signature. You should read that article completely so you understand that function because it will be you that has to support it.

    and here is the link he was referring to

    http://qa.sqlservercentral.com/articles/Tally+Table/72993/

    For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

    For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

    Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
    Jeff Moden's Cross tab and Pivots Part 1[/url]
    Jeff Moden's Cross tab and Pivots Part 2[/url]

  • July 2, 2012 at 10:47 am

    #1507832

    Bill Talada (7/2/2012)

    @ Lynn Pettis

    I don't remember you posting code for your 8k splitter. We all knew there was a general splitter function being used. It doesn't matter how it is implemented.

    I have posted the splitter on several threads. If I don't I do tell the OP where to find it on ssc, unlike the code you posted, I have no idea where to find the function you used in the code.

  • July 2, 2012 at 10:48 am

    #1507834

    Bill Talada (7/2/2012)

    @ Lynn Pettis

    I don't remember you posting code for your 8k splitter. We all knew there was a general splitter function being used. It doesn't matter how it is implemented.

    Well it was me that posted the code using a splitter and I referenced the article where the source for the function I used is posted. I would argue that how it is implemented IS what is important here.

    Lynn did not post any code. He said that while your solution would likely work, the post was incomplete without the function code.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • July 2, 2012 at 11:40 am

    #1507869

    Bill Talada (7/2/2012)

    declare @t table (UserName varchar(3), ids varchar(10))

    insert into @t values ('Tom', '1,2,3')

    insert into @t values ('Sue', '4,5,6')

    select *

    from @t t

    cross apply dbo.BuildKeyTableForDelimitedString(t.ids)

    All other arguments notwithstanding, I'd be interested in seeing the code for your splitter, Bill. Most splitters tend to have at least one hidden performance problem and I'd sure like to take a look at yours... maybe even make a recommendation or two.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply