| Transpose and dynamic column depending on result [message #600276] |
Mon, 04 November 2013 10:54  |
rasi_85
Messages: 9
Registered: May 2008
|
Junior Member |
|
|
Hi,
I am having a table structure like below.
city amt1 tx_date
-----------------------------------------------------
Blr 10000 20050101
Delhi 25000 20050101
Blr 10000 20050102
Blr 2100 20050103
DELHI ...... 20050104
...... ....... ........
i have to place the data in following manner.
city 20050101 20050102 20050103 20050104 ...........etc
-------------------------------------------------------
Blr 10000 10000 2100
Delhi 25000 0 0
Depending on the no. of distinct dates in table 1 i have to make those many columns in table 2. I m trying to write a Query in SQL. If its not possible in SQL give me PL/SQL procedure.
Thanks and Regards,
Rasi_85
|
|
|
|
|
|
|
|
|
|
| Re: Transpose and dynamic column depending on result [message #600283 is a reply to message #600281] |
Mon, 04 November 2013 11:16  |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
rasi_85 wrote on Mon, 04 November 2013 22:42I tried PIVOT will not apply this scenario..
BlackSwan wrote on Mon, 04 November 2013 22:29Please read and follow the forum guidelines, to enable us to help you:
http://www.orafaq.com/forum/t/88153/0/
Please post what you tried. It might just help you/anyone. Please use code tags while you do so.
It would be easy for anybody to replicate your issue if you provide the DDLs and necessary insert statements.
If the pivoted values are not to be displayed as different columns then you could simply do this -
SQL> with data as (
2 select 'Blr' city, 10000 amtl, to_date(20050101,'yyyymmdd') tx_date from du
al union all
3 select 'Delhi' city,25000 amtl, to_date(20050101,'yyyymmdd') tx_date from d
ual union all
4 select 'Blr' city,10000 amtl, to_date(20050102,'yyyymmdd') tx_date from dua
l union all
5 select 'Blr' city,2100 amtl, to_date(20050103,'yyyymmdd') tx_date from dual
union all
6 select 'Delhi' city,0 amtl, to_date(20050104,'yyyymmdd') tx_date from dual
union all
7 select 'Delhi' city,0 amtl, to_date(20050105,'yyyymmdd') tx_date from dual)
8 SELECT city, LISTAGG(amtl, ' ') WITHIN GROUP (ORDER BY tx_date) as pivot_test
9 from data
10 group by city;
CITY PIVOT_TEST
---- ---------------------------------------------------------------------------
Blr 10000 10000 2100
Delhi 25000 0 0
2 rows selected
[Updated on: Mon, 04 November 2013 12:00] Report message to a moderator |
|
|
|
| Re: Transpose and dynamic column depending on result [message #600285 is a reply to message #600276] |
Mon, 04 November 2013 11:58 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
And what will you do with such query results? You wouldn't know number od columns upfront, so you will have to use DBMS_SQL to fetch. Most likely you are trying to write a report by using tool like SQL*Plus whi8ch is not a reporting tool. If you do use SQL*Plus you could generate PIVOT statement in SQL*Plus by using substitution variables. SOmething like:
SCOTT@orcl > SELECT *
2 FROM tbl
3 /
CITY AMT TX_DATE
----- ---------- ----------
Blr 10000 20050101
Delhi 25000 20050101
Blr 10000 20050102
Blr 2100 20050103
DELHI 9999 20050104
SCOTT@orcl > COLUMN list NEW_VALUE list NOPRINT
SCOTT@orcl > WITH t AS (
2 SELECT DISTINCT tx_date
3 FROM tbl
4 )
5 SELECT LISTAGG(tx_date,',') WITHIN GROUP(ORDER BY tx_date) list
6 FROM t
7 /
SCOTT@orcl > select *
2 from tbl
3 pivot(
4 max(amt) for tx_date in (&&list)
5 )
6 /
old 4: max(amt) for tx_date in (&&list)
new 4: max(amt) for tx_date in (20050101,20050102,20050103,20050104)
CITY 20050101 20050102 20050103 20050104
----- ---------- ---------- ---------- ----------
Blr 10000 10000 2100
DELHI 9999
Delhi 25000
SCOTT@orcl >
SY.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
