Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2002
    Location
    Sunnyvale, CA USA
    Posts
    78

    Unanswered: Creating multiple rows from a field with a list

    OK. I'm being sent some data with a varchar field called my_elements that contains a 'list' of data items e.g. "4950,1,0%4954,2,0%4955,1,1"
    I want to separate this into three records with a my_element field:
    "4950,1,0"
    "4954,2,0"
    "4955,1,1"
    How can I do this in SQL? Here's a template for what I want:

    create table my_table
    (
    my_id int,
    my_elements varchar(8000)
    )
    insert into my_table values (1,'4950,1,0%4954,2,0%4955,1,1')

    -- Now I need some SQL to produce to create rows like these:
    -- 1,'4950,1,0'
    -- 1,'4954,2,0'
    -- 1,'4955,1,1'
    --
    -- Or even better, as 4 numeric fields per row:
    -- 1,4950,1,0
    -- 1,4954,2,0
    -- 1,4955,1,1

    Also, I'd like to aviod using cursors if I can.

    Any help appreciated. Thanks!
    Last edited by andyabel; 06-04-03 at 14:57.

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    do you have a fixed or variable number of data items?
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    OK So this example Sucks But Hey

    It Works for your example

    SELECT my_id,SUBSTRING(my_elements,0,9) FROM my_table
    UNION
    SELECT my_id,SUBSTRING(my_elements,10,8) FROM my_table
    UNION
    SELECT my_id,SUBSTRING(my_elements,19,9) FROM my_table

    tehe

    GW
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  4. #4
    Join Date
    Apr 2002
    Location
    Sunnyvale, CA USA
    Posts
    78
    Originally posted by Paul Young
    do you have a fixed or variable number of data items?
    Yeah, sorry, I forgot to point out, a variable number of data items

  5. #5
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    mmmm

    wonder if we could work out the entire length of the field ie. total number of seperate records

    Then

    Iterate through a loop using local variables to build a dynamic SQL Statement incrementing the substring position as we go and adding the UNIONS then execute that.

    It's a thought

    Is the physical length of the Data Items Consistent ?
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  6. #6
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    or:

    Code:
    if object_id('TEMPDB..#my_table') is not null drop table #my_table
    
    create table #my_table (
      my_id int
    , my_elements varchar(8000))
    
    insert into #my_table values (1,'4950,1,0%4954,2,0%4955,1,1')
    insert into #my_table values (2,'4850,1,0%4854,2,0%4855,1,1')
    insert into #my_table values (3,'4750,1,0%4754,2,0%4755,1,1')
    insert into #my_table values (4,'4650,1,0%4654,2,0%4655,1,1')
    insert into #my_table values (5,'4550,1,0%4554,2,0%4555,1,1')
    
    declare @Tbl table(my_id int, my_element1 int, my_element2 int, my_element3 int)
    declare @my_id int, @my_elements varchar(8000)
          , @RecordSeperator char(1), @ItemSeperator char(1)
          , @my_element varchar(12)
          , @RecordPosition int, @ItemPosition int, @LastRecordPosition int, @LastItemPosition int
          , @Int1 int, @Int2 int, @Int3 int, @Int4 int
    
    select @RecordSeperator = '%'
         , @ItemSeperator   = ','
    
    select @my_id = min(my_id) from #my_table
    while (@my_id is not null) begin
      select @my_elements = my_elements from #my_table where my_id = @my_id
    
      set @LastRecordPosition = 1
      set @RecordPosition = charindex(@RecordSeperator, @my_elements, @LastRecordPosition)
      while (@RecordPosition > 0) begin
        set @my_element = substring(@my_elements,@LastRecordPosition,@RecordPosition-@LastRecordPosition)
    
        set @LastItemPosition = 1
        set @ItemPosition = charindex(@ItemSeperator, @my_element, @LastItemPosition)
        set @Int1 = cast(substring(@my_element,@LastItemPosition,@ItemPosition-@LastItemPosition) as int)
    
        set @LastItemPosition = @ItemPosition + 1
        set @ItemPosition = charindex(@ItemSeperator, @my_element, @LastItemPosition)
        set @Int2 = cast(substring(@my_element,@LastItemPosition,@ItemPosition-@LastItemPosition) as int)
    
        set @LastItemPosition = @ItemPosition + 1
        set @ItemPosition = charindex(@ItemSeperator, @my_element, @LastItemPosition)
        set @Int3 = cast(substring(@my_element,@LastItemPosition,len(@my_element)) as int)
    
        raiserror('ID: %d  ''%s''  %d - %d - %d.',0,1,@my_id,@my_element,@Int1,@Int2,@Int3)
    
        insert into @Tbl values(@my_id, @Int1, @Int2, @Int3)
    
        set @LastRecordPosition = @RecordPosition + 1
        set @RecordPosition = charindex(@RecordSeperator, @my_elements, @LastRecordPosition)
        
      end    
    
      select @my_id = min(my_id) from #my_table where my_id > @my_id
    end
    
    select * from @Tbl
    Paul Young
    (Knowledge is power! Get some!)

  7. #7
    Join Date
    Apr 2002
    Location
    Sunnyvale, CA USA
    Posts
    78
    Wow! Looks good! Thank you Paul.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •