Sometimes, table fields contain lists (like lists of telephone numbers).
This is a non-normalized table design, of course, but often chosen for efficiency and/or simplicity, since the normalized design would require an additional table.
Well, it could be useful to have that normalized table available anyhow, e.g. through a VIEW on the (non-normalized) table with list entries, or through a common table expression (CTE), aka a "private view".
In a sense, this is the opposite of "aggregate concatenation" as discussed in
http://www.dbforums.com/db2/1615607-script-library-post6323330.html (up in this thread).
The query below will "de"catenate (i.e., split over multiple lines) the fields in column "v", based on the separator ",". This is done using a recursive CTE.
Just to help understand the query: the table
Code:
items.g items.v
10 a,b
20 b,c,d
will be transformed into
Code:
g v
10 a
10 b
20 b
20 c
20 d
Essentially, the CTE "norm" does it all, so you may plug in the two CTEs (t and norm) in front of your query which requires the "normalized" design. Or use the full query in a CREATE VIEW.
Code:
WITH t(g, v, aux) AS
(SELECT g, NULLIF('',''), v FROM items
UNION ALL
SELECT g,
CASE posstr(aux,',')
WHEN 0 THEN aux
WHEN NULLIF(0,0) THEN aux
ELSE substr(aux, 1, posstr(aux,',')-1) END,
CASE posstr(aux,',')
WHEN 0 THEN NULLIF('','')
ELSE substr(aux, posstr(aux,',')+1) END
FROM t
WHERE aux IS NOT NULL
),
norm(g, v) AS
(SELECT g, v FROM t
WHERE v IS NOT NULL
)
SELECT * FROM norm
(Note the "NULLIF"; alternative would have been a CAST(NULL AS VARCHAR(1)) or so.)