| display data in new column [message #677250] |
Fri, 06 September 2019 06:37  |
 |
getideas456@gmail.com
Messages: 3
Registered: September 2019
|
Junior Member |
|
|
I have this data
changeNo key mod_field
==================================
1906 Y S1
1906 Y A1
1906 N R1
1906 N V1
1906 N T1
1907 Y C1
1907 Y Y1
1907 N U1
Expected result -- Key values in one column and non-key values in new_column
changeNo mod_field new_column
==================================
1906 S1 R1
1906 A1 V1
1906 T1
1907 C1 U1
1907 Y1
|
|
|
|
| Re: display data in new column [message #677251 is a reply to message #677250] |
Fri, 06 September 2019 07:46  |
 |
Michel Cadot
Messages: 68417
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Align the columns in result.
Also always post your Oracle version, with 4 decimals, as solution depends on it.
With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
For example, why the 2 first result lines are:
1906 S1 R1
1906 A1 V1
and not:
1906 S1 V1
1906 A1 R1
or
1906 S1 T1
1906 A1 V1
or...
Same thing for 1907.
[Updated on: Fri, 06 September 2019 07:49] Report message to a moderator |
|
|
|
| Re: display data in new column [message #677258 is a reply to message #677250] |
Fri, 06 September 2019 09:05 |
Solomon Yakobson
Messages: 3211
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
As Michel pointed out your data isn't deterministic. So which N will be paired with which Y is undefined. You'd need additional column to sequence each CHANGENO, KEY combination. Other than that:
WITH T1 AS (
SELECT TBL.*,
ROW_NUMBER() OVER(PARTITION BY CHANGENO,KEY ORDER BY 1) RN -- replace ORDER BY 1 with ORDER BY SEQ when you add sequence column SEQ
FROM TBL
),
T2 AS (
SELECT * FROM T1 WHERE KEY = 'N'
),
T3 AS (
SELECT * FROM T1 WHERE KEY = 'Y'
)
SELECT NVL(T2.CHANGENO,T3.CHANGENO) CHANGENO,
T2.MOD_FIELD,
T3.MOD_FIELD NEW_COLUMN
FROM T2
FULL JOIN
T3
ON T3.CHANGENO = T2.CHANGENO
AND T3.RN = T2.RN
ORDER BY T2.CHANGENO,
T2.RN
/
CHANGENO MOD_FIELD NEW_COLUMN
---------- ---------- ----------
1906 R1 S1
1906 V1 A1
1906 T1
1907 U1 C1
1907 Y1
SQL>
SY.
|
|
|
|
|
|
|
|
| Re: display data in new column [message #677281 is a reply to message #677271] |
Mon, 09 September 2019 13:09 |
Solomon Yakobson
Messages: 3211
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
getideas456@gmail.com wrote on Mon, 09 September 2019 04:26
Query is giving an error. RN is invalid identifier for T2.
It means you changed something and didn't post it so we can't tell what is wrong:
SQL> CREATE TABLE TBL
2 AS
3 SELECT 1906 changeNo,'Y' key,'S1' mod_field FROM DUAL UNION ALL
4 SELECT 1906,'Y','A1' FROM DUAL UNION ALL
5 SELECT 1906,'N','R1' FROM DUAL UNION ALL
6 SELECT 1906,'N','V1' FROM DUAL UNION ALL
7 SELECT 1906,'N','T1' FROM DUAL UNION ALL
8 SELECT 1907,'Y','C1' FROM DUAL UNION ALL
9 SELECT 1907,'Y','Y1' FROM DUAL UNION ALL
10 SELECT 1907,'N','U1' FROM DUAL
11 /
Table created.
SQL> WITH T1 AS (
2 SELECT TBL.*,
3 ROW_NUMBER() OVER(PARTITION BY CHANGENO,KEY ORDER BY 1) RN -- replace ORDER BY 1 with ORDER BY SEQ when you add sequence column SEQ
4 FROM TBL
5 ),
6 T2 AS (
7 SELECT * FROM T1 WHERE KEY = 'N'
8 ),
9 T3 AS (
10 SELECT * FROM T1 WHERE KEY = 'Y'
11 )
12 SELECT NVL(T2.CHANGENO,T3.CHANGENO) CHANGENO,
13 T2.MOD_FIELD,
14 T3.MOD_FIELD NEW_COLUMN
15 FROM T2
16 FULL JOIN
17 T3
18 ON T3.CHANGENO = T2.CHANGENO
19 AND T3.RN = T2.RN
20 ORDER BY T2.CHANGENO,
21 T2.RN
22 /
CHANGENO MOD_FIELD NEW_COLUMN
---------- ---------- ----------
1906 R1 S1
1906 V1 A1
1906 T1
1907 U1 C1
1907 Y1
SQL>
SY.
|
|
|
|
|
|
] 
Search
Help
Members
Register
Login
Home
