Sql Query for reporting

Jeevan Anand

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;

Frank Kulash

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.

Frank Kulash

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.

Frank Kulash

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.

User_H3J7U

Use the first/last group function.

Jeevan Anand

https://community.oracle.com/tech/developers/discussion/comment/16823094#Comment_16823094

Thanks a lot Frank Kulash.