A Friend asked how to convert multi-row to one row that have multi-column with delimiter...
A Friend asked how to convert multi-row to one row that have multi-column with delimiter in DB2. Such as:
c1
----
a
b
c
==>
c2
----------
a | b | c
Actually, There are 2 different requirements on this point. The first one is that the multi-row number is limited and could be referred as a flag. Such as:
c1 c2
---- ----
10 Jan
21 Feb
88 Mar
... ...
04 Dec
The second one is that the multi-row number is unlimited or can not be referred with any character.
There's the way that uses pure SQL to implement the first requirement. Here's a sample: changing row to column
select
emp_id,
sum(case month(pay_date)
when 1 then salary
else 0
end) Jan,
sum(case month(pay_date)
when 2 then salary
else 0
end) Feb,
sum(case month(pay_date)
when 3 then salary
else 0
end) Mar,
sum(case month(pay_date)
when 4 then salary
else 0
end) Apr,
sum(case month(pay_date)
when 5 then salary
else 0
end) May,
sum(case month(pay_date)
when 6 then salary
else 0
end) Jun,
sum(case month(pay_date)
when 7 then salary
else 0
end) Jul,
sum(case month(pay_date)
when 8 then salary
else 0
end) Aug,
sum(case month(pay_date)
when 9 then salary
else 0
end) Sep,
sum(case month(pay_date)
when 10 then salary
else 0
end) Oct,
sum(case month(pay_date)
when 11 then salary
else 0
end) Nov,
sum(case month(pay_date)
when 12 then salary
else 0
end) Dec,
sum(salary) total
from emp_salary
group by emp_id
To the 2nd requirement, the only way is to write use-defined function for it. It accepts a cursor and output one row. Here's a sample in Oracle.
SELECT
a.deptno,
a.dname,
a.loc, rowtocol('SELECT DISTINCT job
FROM emp WHERE deptno = ' ||a.deptno) as jobs
FROM dept a;
CREATE OR REPLACE FUNCTION rowtocol( p_slct IN VARCHAR2, p_dlmtr IN VARCHAR2 DEFAULT ',' )
RETURN VARCHAR2 AUTHID CURRENT_USER AS ...
TYPE c_refcur IS REF CURSOR;
lc_str VARCHAR2(4000);
lc_colval VARCHAR2(4000);
c_dummy c_refcur;
l number;
BEGIN
OPEN c_dummy FOR p_slct;
LOOP
FETCH c_dummy INTO lc_colval;
EXIT WHEN c_dummy%NOTFOUND;
lc_str := lc_str || p_dlmtr || lc_colval;
END LOOP;
CLOSE c_dummy;
RETURN SUBSTR(lc_str,2);
/*
EXCEPTION
WHEN OTHERS THEN
lc_str := SQLERRM;
IF c_dummy%ISOPEN THEN
CLOSE c_dummy;
END IF;
RETURN lc_str;
*/
END;
Update 2008-08-01
A little update on the topic.
What if when the function SUM() is not allowed for the data type? See below example:
with tmp(c1,c2,c3) as (
values (1,'a',1), (1,'b',2)
)
select * from tmp
@
C1 C2 C3
----------- -- -----------
1 a 1
1 b 2
2 record(s) selected.
The answer is we could use MAX(), MIN() like below:
select
c1,
max(case c3 when 1 then c2 end),
max(case c3 when 2 then c2 end)
from tmp group by c1@
C1 2 3
----------- - -
1 a b
1 record(s) selected.
