Rows into columns
Hi All,
Need to convert rows into cols. and also need to put couple of "|" if
no record exsit for any given condition in below SQL.
---------------------------------------------------------------------
select pcl_num, max(decode(imp_cls, "DW", trim(imp_cls) || "|"|| trim(imp_cnd) || "|" || trim(qua_sty) || "|" || imp_ara || "|"
|| con_dte || "|" || trim(con_mat) || "|" || num_flo," ")) || "|"
|| max(decode(imp_cls, "GAR", trim(imp_cls) || "|" || trim(imp_cnd)
|| "|" || trim(qua_sty) || "|" || imp_ara || "|" || con_dte || "|"
|| trim(con_mat) || "|" || num_flo, " ")) as data
from auprimpr
where pcl_num in
(488739)
--(10)
group by pcl_num
----------------------------------------------------------------------
1. output from above query if pcl_num = 488739
pcl_num 488739
data DW|4|3|268.40|24/05/2005|BV|2|GAR|4|3|41.00|24/05/2005|BK|1
2. output from above query if pcl_num = 10
pcl_num 10
data DW|3|3|167.78|01/01/1980|BV|1|
-----------------------------------------------------------------------
Requirement:
Output 1 is perfectly ok as pcl_num 488739 has got data for both
conditions but output 2 need to include couple of "|" since data does
not exist for max(decode(imp_cls, "GAR").
Above example uses only two conditions but I need to use 6 -7 similar
conditions in one concatenation string above.
Can some one please help me out ?
TIA
I think the problem is when some of the elements are NULL, so try wrapping them
as follows:
NVL(expression, "")
--
Regards,
Doug Lawry
www.douglawry.webhop.org
<hariog@yahoo.com> wrote in message
news:1140054200.586755.226750@o13g2000cwo.googlegroups.com...
> Hi All,
>
> Need to convert rows into cols. and also need to put couple of "|" if
> no record exsit for any given condition in below SQL.
>
> ---------------------------------------------------------------------
> select pcl_num, max(decode(imp_cls, "DW", trim(imp_cls) || "|"> || trim(imp_cnd) || "|" || trim(qua_sty) || "|" || imp_ara || "|"
> || con_dte || "|" || trim(con_mat) || "|" || num_flo," ")) || "|"
> || max(decode(imp_cls, "GAR", trim(imp_cls) || "|" || trim(imp_cnd)
> || "|" || trim(qua_sty) || "|" || imp_ara || "|" || con_dte || "|"
> || trim(con_mat) || "|" || num_flo, " ")) as data
> from auprimpr
> where pcl_num in
> (488739)
> --(10)
> group by pcl_num
> ----------------------------------------------------------------------
>
> 1. output from above query if pcl_num = 488739
>
> pcl_num 488739
> data DW|4|3|268.40|24/05/2005|BV|2|GAR|4|3|41.00|24/05/2005|BK|1
>
>
> 2. output from above query if pcl_num = 10
>
> pcl_num 10
> data DW|3|3|167.78|01/01/1980|BV|1|
>
> -----------------------------------------------------------------------
>
> Requirement:
>
> Output 1 is perfectly ok as pcl_num 488739 has got data for both
> conditions but output 2 need to include couple of "|" since data does
> not exist for max(decode(imp_cls, "GAR").
>
> Above example uses only two conditions but I need to use 6 -7 similar
> conditions in one concatenation string above.
>
> Can some one please help me out ?
>
> TIA
>