Home » SQL & PL/SQL » SQL & PL/SQL » display data in new column
display data in new column [message #677250] Fri, 06 September 2019 06:37 Go to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
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 #677271 is a reply to message #677258] Mon, 09 September 2019 03:26 Go to previous messageGo to next message
getideas456@gmail.com
Messages: 3
Registered: September 2019
Junior Member
Thanks for quick reply.


I am having resultset of key(Y) and non-key(N)values.

Requirement is to display key values of changeNo in one column and non-key values in new column.
There can be one key and multiple non-key values and vice-versa.


Solomon,

Query is giving an error. RN is invalid identifier for T2.




Re: display data in new column [message #677274 is a reply to message #677271] Mon, 09 September 2019 10:18 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You didn't answer to my question.
You didn't provide what is asked.

Re: display data in new column [message #677281 is a reply to message #677271] Mon, 09 September 2019 13:09 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
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.
Re: display data in new column [message #677293 is a reply to message #677281] Tue, 10 September 2019 06:25 Go to previous message
getideas456@gmail.com
Messages: 3
Registered: September 2019
Junior Member
SY,

Thanks a lot. My query is resolved.

Michel,

Point noted. I will send complete details whenever I post any query.
Previous Topic: ORA-00001: unique constraint On Merge Insert
Next Topic: How To Show Column Value separated with colon as well as another column vlaue
Goto Forum:
  


Current Time: Thu Mar 28 07:11:04 CDT 2024