Oracle SQL columns to rows and vise versa

Question

Need your help guys, I have searched on internet but did not find solution

My Table :

                    GSTNO   HOME_GST    DELAY   DEFAULT
                    GST1        Y         N        N
                    GST2        N         Y        N
                    GST3        N         Y        N
                    GST4        N         N        Y



                    Desired Table :

                    GSTNO           GST1    GST2    GST3    GST4
                    HOME_GST         Y       N       N       N
                    DELAY            N       Y       Y       N
                    DEFAULT          N       N       N       Y

GST is not limited to 4 only (I have just put an example), it can be 10 or 15 or more it is dynamic — Intekhab Alam, Jun 10 '22 at 15:39
Rows to columns: [`PIVOT`](https://www.techonthenet.com/oracle/pivot.php). — padaleiana, Jun 10 '22 at 15:43
with demo as ( select 1 as col1, 2 as col2, 3 as col3, 4 as col4 from dual ) select * from demo UNPIVOT INCLUDE NULLS ( colvalue for col in (col1, col2, col3, col4) ); — Intekhab Alam, Jun 10 '22 at 15:48
I have tried this query(replaced my table and columns), but i am getting only two columns — Intekhab Alam, Jun 10 '22 at 15:49

score 0 · Answer 1 · answered Jun 10 '22 at 20:57

0

UNPIVOT and then PIVOT:

SELECT *
FROM   table_name
UNPIVOT (
  value FOR key IN (home_gst, delay, "DEFAULT")
)
PIVOT (
  MAX(value)
  FOR gstno IN (
    'GST1' AS gst1,
    'GST2' AS gst2,
    'GST3' AS gst3,
    'GST4' AS gst4
  )
)

Which, for the sample data:

CREATE TABLE table_name (GSTNO, HOME_GST, DELAY, "DEFAULT") AS
SELECT 'GST1', 'Y', 'N', 'N' FROM DUAL UNION ALL
SELECT 'GST2', 'N', 'Y', 'N' FROM DUAL UNION ALL
SELECT 'GST3', 'N', 'Y', 'N' FROM DUAL UNION ALL
SELECT 'GST4', 'N', 'N', 'Y' FROM DUAL;

Outputs:

KEY GST1 GST2 GST3 GST4

HOME_GST Y N N N

DELAY N Y Y N

DEFAULT N N N Y

db<>fiddle here

answered Jun 10 '22 at 20:57

MT0

143,790
11
59
117

but it is not fixed that only 4 GST number (4 rows) are there.. this can be any number of row, it can be 10 or 20 or more – Intekhab Alam Jun 11 '22 at 14:09
@IntekhabAlam If there are an unknown number of rows (GST values) you are transposing then the question is impossible to solve in SQL as an SQL statement needs to have a known, fixed number of input and output columns; if you either do not know the number of inputs or the number of outputs then you cannot use a normal SQL query. It might be possible (but very hard) to solve using dynamic SQL but usually the problem of transposing rows and columns would be performed in a third-party application (Java, C#, PHP, Javascript, etc.). – MT0 Jun 11 '22 at 19:57
Ok then i will handle this in Java. Thanks buddy.. – Intekhab Alam Jun 13 '22 at 05:17

Oracle SQL columns to rows and vise versa

1 Answers1