You Asked
I have a table that has the following columns:
RegionID | RegionName | State | Metro | SizeRank | Feb_1997 | Mar_1997 | Apr_1997 |.....Jul2016
2688 | Charleston | SC | Charleston | 163 | 21.54 | 6.75 | 29.36 | .. etc.
My question is how to unpivot these date columns to rows in the following format:
RegionID | RegionName | State | Metro |SizeRank | Year | Month | Amount
2688 | Charleston | SC | Charleston | 163 | 1997 | Feb | 21.54
2777 | Berkeley | SC | Charleston | 513 | 1997 | Mar | 6.75
... | .... | .. | .......... | ... | 2016 | ... | ...
Currently, I have this inline view but am trying to figure out a way to do this dynamically without having to union all 238 columns..
Any suggestions would be much appreciated.
select FEB97.REGIONID,
FEB97.REGIONNAME,
FEB97.STATE,
FEB97.METRO,
FEB97.SIZERANK,
SUBSTR(FEB97.MONTH_YEAR,1,3) AS MONTH,
SUBSTR(FEB97.MONTH_YEAR,5,4) AS YEAR,
FEB97.AMT
FROM (SELECT REGIONID,
REGIONNAME,
STATE,
METRO,
SIZERANK,
FEB_1997 as AMT,
(SELECT column_name FROM user_tab_columns where table_name = 'SAMPLE' AND COLUMN_NAME = 'FEB_1997') AS MONTH_YEAR
FROM SAMPLE) FEB97
UNION ALL
SELECT MAR97.REGIONID,
MAR97.REGIONNAME,
MAR97.STATE,
MAR97.METRO,
MAR97.SIZERANK,
SUBSTR(MAR97.MONTH_YEAR,1,3) AS MONTH,
SUBSTR(MAR97.MONTH_YEAR,5,4) AS YEAR,
MAR97.AMT
FROM (SELECT REGIONID,
REGIONNAME,
STATE,
METRO,
SIZERANK,
MAR_1997 as AMT,
(SELECT column_name FROM user_tab_columns where table_name = 'SAMPLE' AND COLUMN_NAME = 'MAR_1997') AS MONTH_YEAR
FROM SAMPLE) MAR97
and Connor said...
To get the row data, you need to explcitly list the columns, for example
SQL> drop table t purge;
Table dropped.
SQL>
SQL> create table t (
2 x int, y int,
3 jan2016 int,
4 feb2016 int,
5 mar2016 int,
6 apr2016 int );
Table created.
SQL>
SQL> insert into t
2 select rownum, rownum,
3 dbms_random.value(1,10),
4 dbms_random.value(1,10),
5 dbms_random.value(1,10),
6 dbms_random.value(1,10)
7 from dual
8 connect by level <= 10;
10 rows created.
SQL>
SQL> select * from t;
X Y JAN2016 FEB2016 MAR2016 APR2016
---------- ---------- ---------- ---------- ---------- ----------
1 1 6 4 2 7
2 2 6 3 1 2
3 3 8 4 2 4
4 4 2 6 6 5
5 5 4 4 6 3
6 6 7 3 2 3
7 7 1 4 7 5
8 8 6 2 2 3
9 9 3 5 7 6
10 10 6 5 2 5
10 rows selected.
SQL>
SQL> select *
2 from t
3 unpivot (c1 for dte in (jan2016 as date '2016-01-01', feb2016 as date '2016-02-01', mar2016 as date '2016-03-01', apr2016 as date '2016-04-01')) ;
X Y DTE C1
---------- ---------- --------- ----------
1 1 01-JAN-16 6
1 1 01-FEB-16 4
1 1 01-MAR-16 2
1 1 01-APR-16 7
2 2 01-JAN-16 6
2 2 01-FEB-16 3
2 2 01-MAR-16 1
2 2 01-APR-16 2
3 3 01-JAN-16 8
3 3 01-FEB-16 4
3 3 01-MAR-16 2
3 3 01-APR-16 4
4 4 01-JAN-16 2
4 4 01-FEB-16 6
4 4 01-MAR-16 6
4 4 01-APR-16 5
5 5 01-JAN-16 4
5 5 01-FEB-16 4
5 5 01-MAR-16 6
5 5 01-APR-16 3
6 6 01-JAN-16 7
6 6 01-FEB-16 3
6 6 01-MAR-16 2
6 6 01-APR-16 3
7 7 01-JAN-16 1
7 7 01-FEB-16 4
7 7 01-MAR-16 7
7 7 01-APR-16 5
8 8 01-JAN-16 6
8 8 01-FEB-16 2
8 8 01-MAR-16 2
8 8 01-APR-16 3
9 9 01-JAN-16 3
9 9 01-FEB-16 5
9 9 01-MAR-16 7
9 9 01-APR-16 6
10 10 01-JAN-16 6
10 10 01-FEB-16 5
10 10 01-MAR-16 2
10 10 01-APR-16 5
40 rows selected.
SQL>
SQL>
If you want to dynamically convert the columns, take a look at the little tool AMIS put together
https://technology.amis.nl/2006/05/24/dynamic-sql-pivoting-stealing-antons-thunder/ Hope this helps
Rating
(2 ratings)
Is this answer out of date? If it is, please let us know via a Comment