Skip to main content

Return to Answer

Post Timeline

deleted 94 characters in body
Source Link
Jeff Atwood
Jeff Atwood
  • 64.1k
  • 48
  • 153
  • 153

with qry(n, names) as (select len(list.names) - len(replace(list.names, ',', '')) - 1 as n, substring(list.names, 2, len(list.names)) as names from (select ',Doc,Grumpy,Happy,Sneezy,Bashful,Sleepy,Dopey,' names) as list union all select (n - 1) as n, substring(names, 1 + charindex(',', names), len(names)) as names from qry where n > 1) select n, substring(names, 1, charindex(',', names) - 1) dwarf from qry;

 with qry(n, names) as
       (select len(list.names) - len(replace(list.names, ',', '')) - 1 as n,
               substring(list.names, 2, len(list.names)) as names
        from (select ',Doc,Grumpy,Happy,Sneezy,Bashful,Sleepy,Dopey,' names) as list
        union all
        select (n - 1) as n,
               substring(names, 1 + charindex(',', names), len(names)) as names
        from qry
        where n > 1)
 select n, substring(names, 1, charindex(',', names) - 1) dwarf
 from qry;

select n, substr(name, 1, instr(name, ',') - 1) dwarf from (select n, substr(val, 1 + instr(val, ',', 1, n)) name from (select rownum as n, list.val from (select ',Doc,Grumpy,Happy,Sneezy,Bashful,Sleepy,Dopey,' val from dual) list connect by level < length(list.val) - length(replace(list.val, ',', ''))));

 select n, substr(name, 1, instr(name, ',') - 1) dwarf
 from (select n,
             substr(val, 1 + instr(val, ',', 1, n)) name
      from (select rownum as n,
                   list.val
            from  (select ',Doc,Grumpy,Happy,Sneezy,Bashful,Sleepy,Dopey,' val
                   from dual) list
            connect by level < length(list.val) -
                               length(replace(list.val, ',', ''))));

select pivot.n, substring_index(substring_index(list.val, ',', 1 + pivot.n), ',', -1) from (select 1 as n union all select 2 as n union all select 3 as n union all select 4 as n union all select 5 as n union all select 6 as n union all select 7 as n union all select 8 as n union all select 9 as n union all select 10 as n) pivot, (select ',Doc,Grumpy,Happy,Sneezy,Bashful,Sleepy,Dopey,' val) as list where pivot.n < length(list.val) - length(replace(list.val, ',', ''));

select pivot.n,
      substring_index(substring_index(list.val, ',', 1 + pivot.n), ',', -1) from (select 1 as n
     union all
     select 2 as n
     union all
     select 3 as n
     union all
     select 4 as n
     union all
     select 5 as n
     union all
     select 6 as n
     union all
     select 7 as n
     union all
     select 8 as n
     union all
     select 9 as n
     union all
     select 10 as n) pivot,    (select ',Doc,Grumpy,Happy,Sneezy,Bashful,Sleepy,Dopey,' val) as list where pivot.n <  length(list.val) -
                   length(replace(list.val, ',', ''));

with qry(n, names) as (select len(list.names) - len(replace(list.names, ',', '')) - 1 as n, substring(list.names, 2, len(list.names)) as names from (select ',Doc,Grumpy,Happy,Sneezy,Bashful,Sleepy,Dopey,' names) as list union all select (n - 1) as n, substring(names, 1 + charindex(',', names), len(names)) as names from qry where n > 1) select n, substring(names, 1, charindex(',', names) - 1) dwarf from qry;

select n, substr(name, 1, instr(name, ',') - 1) dwarf from (select n, substr(val, 1 + instr(val, ',', 1, n)) name from (select rownum as n, list.val from (select ',Doc,Grumpy,Happy,Sneezy,Bashful,Sleepy,Dopey,' val from dual) list connect by level < length(list.val) - length(replace(list.val, ',', ''))));

select pivot.n, substring_index(substring_index(list.val, ',', 1 + pivot.n), ',', -1) from (select 1 as n union all select 2 as n union all select 3 as n union all select 4 as n union all select 5 as n union all select 6 as n union all select 7 as n union all select 8 as n union all select 9 as n union all select 10 as n) pivot, (select ',Doc,Grumpy,Happy,Sneezy,Bashful,Sleepy,Dopey,' val) as list where pivot.n < length(list.val) - length(replace(list.val, ',', ''));

 with qry(n, names) as
       (select len(list.names) - len(replace(list.names, ',', '')) - 1 as n,
               substring(list.names, 2, len(list.names)) as names
        from (select ',Doc,Grumpy,Happy,Sneezy,Bashful,Sleepy,Dopey,' names) as list
        union all
        select (n - 1) as n,
               substring(names, 1 + charindex(',', names), len(names)) as names
        from qry
        where n > 1)
 select n, substring(names, 1, charindex(',', names) - 1) dwarf
 from qry;

 select n, substr(name, 1, instr(name, ',') - 1) dwarf
 from (select n,
             substr(val, 1 + instr(val, ',', 1, n)) name
      from (select rownum as n,
                   list.val
            from  (select ',Doc,Grumpy,Happy,Sneezy,Bashful,Sleepy,Dopey,' val
                   from dual) list
            connect by level < length(list.val) -
                               length(replace(list.val, ',', ''))));

select pivot.n,
      substring_index(substring_index(list.val, ',', 1 + pivot.n), ',', -1) from (select 1 as n
     union all
     select 2 as n
     union all
     select 3 as n
     union all
     select 4 as n
     union all
     select 5 as n
     union all
     select 6 as n
     union all
     select 7 as n
     union all
     select 8 as n
     union all
     select 9 as n
     union all
     select 10 as n) pivot,    (select ',Doc,Grumpy,Happy,Sneezy,Bashful,Sleepy,Dopey,' val) as list where pivot.n <  length(list.val) -
                   length(replace(list.val, ',', ''));
Source Link
SFA
SFA

The proper way IMHO is to store the list in a character string (limited in length by what the DBMS support); the only trick is that (in order to simplify processing) I have a separator (a comma in my example) at the beginning and at the end of the string. The idea is to "normalize on the fly", turning the list into a one-column table that contains one row per value. This allows you to turn

in (ct1,ct2, ct3 ... ctn)

into an

in (select ...)

or (the solution I'd probably prefer) a regular join, if you just add a "distinct" to avoid problems with duplicate values in the list.

Unfortunately, the techniques to slice a string are fairly product-specific. Here is the SQL Server version:

with qry(n, names) as (select len(list.names) - len(replace(list.names, ',', '')) - 1 as n, substring(list.names, 2, len(list.names)) as names from (select ',Doc,Grumpy,Happy,Sneezy,Bashful,Sleepy,Dopey,' names) as list union all select (n - 1) as n, substring(names, 1 + charindex(',', names), len(names)) as names from qry where n > 1) select n, substring(names, 1, charindex(',', names) - 1) dwarf from qry;

The Oracle version:

select n, substr(name, 1, instr(name, ',') - 1) dwarf from (select n, substr(val, 1 + instr(val, ',', 1, n)) name from (select rownum as n, list.val from (select ',Doc,Grumpy,Happy,Sneezy,Bashful,Sleepy,Dopey,' val from dual) list connect by level < length(list.val) - length(replace(list.val, ',', ''))));

and the MySQL version:

select pivot.n, substring_index(substring_index(list.val, ',', 1 + pivot.n), ',', -1) from (select 1 as n union all select 2 as n union all select 3 as n union all select 4 as n union all select 5 as n union all select 6 as n union all select 7 as n union all select 8 as n union all select 9 as n union all select 10 as n) pivot, (select ',Doc,Grumpy,Happy,Sneezy,Bashful,Sleepy,Dopey,' val) as list where pivot.n < length(list.val) - length(replace(list.val, ',', ''));

(Of course, "pivot" must return as many rows as the maximum number of items we can find in the list)