In hear I am trying to show how we can convert multiple (Three) column value in single column row.
Table :
CREATE TABLE DATA1
(
ID NUMBER,
COL1 NUMBER,
COL2 NUMBER,
COL3 NUMBER
);
INSERT INTO DATA1
(ID, COL1, COL2, COL3)
VALUES
(1, 1, 1, 2);
INSERT INTO DATA1
(ID, COL1, COL2, COL3)
VALUES
(2, 2, 1, 2);
INSERT INTO DATA1
(ID, COL1, COL2, COL3)
VALUES
(3, 5, 2, 3);
Table :
CREATE TABLE DATA1
(
ID NUMBER,
COL1 NUMBER,
COL2 NUMBER,
COL3 NUMBER
);
INSERT INTO DATA1
(ID, COL1, COL2, COL3)
VALUES
(1, 1, 1, 2);
INSERT INTO DATA1
(ID, COL1, COL2, COL3)
VALUES
(2, 2, 1, 2);
INSERT INTO DATA1
(ID, COL1, COL2, COL3)
VALUES
(3, 5, 2, 3);
SQL> select * from DATA1;
ID COL1 COL2 COL3
---------- ---------- ---------- ----------
1 1 1 2
2 2 1 2
3 5 2 3
SQL>
SQL> WITH TABLE_DATA AS (SELECT * FROM DATA1),
2 DATA
3 AS (SELECT LEVEL UQID
4 FROM DUAL
5 CONNECT BY LEVEL <= 3),
6 T_DATA
7 AS (SELECT UQID,
8 ID,
9 COL1,
10 COL2,
11 COL3
12 FROM TABLE_DATA, DATA
13 ORDER BY ID),
14 FINAL_DATA AS(
15 SELECT ID,
16 (SELECT (CASE
17 WHEN F.UQID = 1 THEN COL1
18 WHEN F.UQID = 2 THEN COL2
19 WHEN F.UQID = 3 THEN COL3
20 ELSE NULL
21 END)
22 FROM T_DATA F
23 WHERE F.ID = T.ID AND F.UQID = T.UQID)
24 ROW_VALUE
25 FROM T_DATA T)
26 SELECT * FROM FINAL_DATA;
ID ROW_VALUE
---------- ----------
1 1
1 1
1 2
2 2
2 1
2 2
3 5
3 2
3 3
9 rows selected.
SQL>
------- This query show duplicate value in multiple column ....
SQL> WITH TABLE_DATA AS (SELECT * FROM DATA1),
2 DATA
3 AS (SELECT LEVEL UQID
4 FROM DUAL
5 CONNECT BY LEVEL <= 3),
6 T_DATA
7 AS (SELECT UQID,
8 ID,
9 COL1,
10 COL2,
11 COL3
12 FROM TABLE_DATA, DATA
13 ORDER BY ID),
14 FINAL_DATA AS(
15 SELECT ID,
16 (SELECT (CASE
17 WHEN F.UQID = 1 THEN COL1
18 WHEN F.UQID = 2 THEN COL2
19 WHEN F.UQID = 3 THEN COL3
ELSE NULL
20 21 END)
22 FROM T_DATA F
23 WHERE F.ID = T.ID AND F.UQID = T.UQID)
24 ROW_VALUE
25 FROM T_DATA T)
26 SELECT ID, ROW_VALUE, COUNT(*) FROM FINAL_DATA
27 GROUP BY ID, ROW_VALUE
28 HAVING COUNT(*)>1;
ID ROW_VALUE COUNT(*)
---------- ---------- ----------
1 1 2
2 2 2
SQL>
