Forum Stats

  • 3,826,400 Users
  • 2,260,641 Discussions
  • 7,896,931 Comments

Discussions

Sql Query for reporting

Jeevan Anand
Jeevan Anand Member Posts: 52 Red Ribbon
edited Jan 24, 2022 2:11PM in SQL & PL/SQL

Hi,

Please help me to retrieve the Non nullable data for each column based on the vendor_rank column, Partitioned by Pricing num.

Refer the attached screenshot:


Sample script:


CREATE TABLE TEST_RANK 

(PRICING_NUMBER NUMBER(9),

NAME VARCHAR(30),

LAST_NAME VARCHAR(30),

DEPT VARCHAR(30),

RANK NUMBER(3));


INSERT INTO TEST_RANK SELECT 91, 'State bank',NULL,NULL,1 FROM DUAL;

INSERT INTO TEST_RANK SELECT 91, 'State bank',NULL,'BANK',2 FROM DUAL;

INSERT INTO TEST_RANK SELECT 91, 'State bank of TN','Ind','BANKs',3 FROM DUAL;

INSERT INTO TEST_RANK SELECT 92, 'HDFC',NULL,'Curr',1 FROM DUAL;

INSERT INTO TEST_RANK SELECT 92, NULL,'HDFC bank','Curr',2 FROM DUAL;

INSERT INTO TEST_RANK SELECT 93, 'Citi','Bank','Bank',1 FROM DUAL;

insert into TEST_RANK SELECT 94, 'YEs','Bank',NULL,1 FROM DUAL;


SELECT * FROM TEST_RANK;

Tagged:

Best Answer

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,052 Red Diamond
    Answer ✓

    Hi, @Jeevan Anand

    Thanks for posting the sample data and results. Don't forget to explain how you get the results from the sample data. For example "I want one row of output fro every distinct value of pricing_number. In the name column, I want to show the first non-NULL name (in order by the rank column) of all the rows with that pricing_id, and the same for the last_name and dept columns."

    If that's what you want to do, here's one way to do it:

    WITH  unpivoted_data  AS
    (
    	SELECT   pricing_number, col, str
    	,	 RANK () OVER ( PARTITION BY pricing_number, col
    		   	   	ORDER BY   rnk
    		   	      ) AS rnk_rank
    	FROM	 test_rank
    	UNPIVOT (  str
    		FOR col IN (name, last_name, dept)
    		)
    )
    SELECT   *
    FROM	 unpivoted_data
    PIVOT	 (  MIN (str)
    	 FOR col IN ( 'NAME'      AS name
    	    	    , 'LAST_NAME' AS last_name
    		    , 'DEPT'	  AS dept
    		    )
    	 )
    WHERE	 rnk_rank = 1
    ;
    

    Always post your full Oracle version (e.g. 18.4.0.0.0) also. The query above works in version 11.1.0.1.0 and up.

    Jeevan Anand

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,052 Red Diamond
    Answer ✓

    Hi, @Jeevan Anand

    Thanks for posting the sample data and results. Don't forget to explain how you get the results from the sample data. For example "I want one row of output fro every distinct value of pricing_number. In the name column, I want to show the first non-NULL name (in order by the rank column) of all the rows with that pricing_id, and the same for the last_name and dept columns."

    If that's what you want to do, here's one way to do it:

    WITH  unpivoted_data  AS
    (
    	SELECT   pricing_number, col, str
    	,	 RANK () OVER ( PARTITION BY pricing_number, col
    		   	   	ORDER BY   rnk
    		   	      ) AS rnk_rank
    	FROM	 test_rank
    	UNPIVOT (  str
    		FOR col IN (name, last_name, dept)
    		)
    )
    SELECT   *
    FROM	 unpivoted_data
    PIVOT	 (  MIN (str)
    	 FOR col IN ( 'NAME'      AS name
    	    	    , 'LAST_NAME' AS last_name
    		    , 'DEPT'	  AS dept
    		    )
    	 )
    WHERE	 rnk_rank = 1
    ;
    

    Always post your full Oracle version (e.g. 18.4.0.0.0) also. The query above works in version 11.1.0.1.0 and up.

    Jeevan Anand
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,052 Red Diamond

    Hi,

    RANK is the name of a built-in function, so it's not a good column name. In the query above, I used rnk as the column name.

    The query above does not assume the values of rnk are consecutive integers, they can be any values. The string displayed will be the first non-NULL string (on order by rnk), regardless of whether the lowest rnk is 1, or 2, or 2.5, or -2.5, or anything else.

    Jeevan Anand
  • User_H3J7U
    User_H3J7U Member Posts: 1,062 Gold Trophy

    Use the first/last group function.