locked
COALESCE Function Query RRS feed

  • Question

  • Hi All,

    COALESCE Function returns the value or expression which was not null, I would like to know from which column we got the output?

    Lets say

    SORT_ORDER=Null, LAG_STATUS=Null, VOLUME_LABEL=1,IS_CODE=Null

    select COALESCE(SORT_ODER,LAG_STATUS, VOLUME_LABEL,IS_CODE) from <table_name>

    Output : 1

    Here i am interested to know about which column output was retreived?

    Thanks,

    A

    Thursday, August 31, 2017 11:05 AM

Answers

  • I can't think of an easier way than the below :(

    DECLARE @Table TABLE (SORT_ORDER INT, LAG_STATUS INT,VOLUME_LABEL INT,IS_CODE INT)
    INSERT INTO @Table
    SELECT SORT_ORDER=Null, LAG_STATUS=Null, VOLUME_LABEL=1,IS_CODE=Null
    
    SELECT * FROM @Table
    
    SELECT 
        ColValue = COALESCE(SORT_ORDER,LAG_STATUS, VOLUME_LABEL,IS_CODE) 
       ,ColName = CASE WHEN SORT_ORDER IS NOT NULL THEN 'SORT_ORDER'
    				   WHEN LAG_STATUS IS NOT NULL THEN 'LAG_STATUS'
    				   WHEN VOLUME_LABEL IS NOT NULL THEN 'VOLUME_LABEL'
    				   WHEN IS_CODE IS NOT NULL THEN 'IS_CODE' END
    from @Table

    output below


    If you use the behaviour of string concatenation with null,  you may re-write the above query as below, still not nice.

    --sample data generation
    DECLARE @Table TABLE (SORT_ORDER INT, LAG_STATUS INT,VOLUME_LABEL INT,IS_CODE INT)
    INSERT INTO @Table
    SELECT SORT_ORDER=Null, LAG_STATUS=Null, VOLUME_LABEL=1,IS_CODE=Null UNION
    SELECT SORT_ORDER=123, LAG_STATUS=Null, VOLUME_LABEL=1,IS_CODE=Null
    
    SELECT * FROM @Table
    
    --retrieve column name and value using COALESCE.
    ;WITH Q AS
    (
    	SELECT COALESCED_DATA=
    		COALESCE(
    				'SORT_ORDER:'	+ CAST(SORT_ORDER AS VARCHAR(50)), 
    				'LAG_STATUS:'	+ CAST(LAG_STATUS AS VARCHAR(50)), 
    				'VOLUME_LABEL:' + CAST(VOLUME_LABEL AS VARCHAR(50)),
    				'IS_CODE:'		+ CAST(IS_CODE AS VARCHAR(50))		
    			)
    	FROM @Table
    )
    SELECT COALESCED_DATA, 
    	   ColName=LEFT(COALESCED_DATA,CHARINDEX(':',COALESCED_DATA)-1),
    	   ColValue=RIGHT(COALESCED_DATA,LEN(COALESCED_DATA)-CHARINDEX(':',COALESCED_DATA))
    FROM Q
    

    • Edited by negus_king Thursday, August 31, 2017 12:57 PM
    • Marked as answer by Annapureddy Friday, September 1, 2017 9:36 AM
    Thursday, August 31, 2017 12:23 PM
  • There is no built in function which will tell you which column is not NULL.  You would have to use a CASE statement to determine which one is NOT NULL.

    CASE WHEN SORT_ORDER IS NOT NULL THEN 'SORT_ORDER' ELSE CASE WHEN LAG_STATUS IS NOT NULL THEN 'LAG_STATUS' ELSE CASE ....

    • Proposed as answer by Xi Jin Friday, September 1, 2017 5:53 AM
    • Marked as answer by Annapureddy Friday, September 1, 2017 9:37 AM
    Thursday, August 31, 2017 12:30 PM

All replies

  • It returns first NO Null column value

    btw

    https://www.mssqltips.com/sqlservertip/2689/deciding-between-coalesce-and-isnull-in-sql-server/


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence


    Thursday, August 31, 2017 12:08 PM
    Answerer
  • COALESCE Function returns the value or expression which was not null, I would like to know from which column we got the output?

    More specifically, COALESCE returns the first value from the specified list that is NOT NULL. So with your example data, VOLUME_LABEL is the first value that is NOT NULL so value of 1 will be returned.

    Is your question actually how to get the name of the column that returned the value?


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Thursday, August 31, 2017 12:21 PM
  • I can't think of an easier way than the below :(

    DECLARE @Table TABLE (SORT_ORDER INT, LAG_STATUS INT,VOLUME_LABEL INT,IS_CODE INT)
    INSERT INTO @Table
    SELECT SORT_ORDER=Null, LAG_STATUS=Null, VOLUME_LABEL=1,IS_CODE=Null
    
    SELECT * FROM @Table
    
    SELECT 
        ColValue = COALESCE(SORT_ORDER,LAG_STATUS, VOLUME_LABEL,IS_CODE) 
       ,ColName = CASE WHEN SORT_ORDER IS NOT NULL THEN 'SORT_ORDER'
    				   WHEN LAG_STATUS IS NOT NULL THEN 'LAG_STATUS'
    				   WHEN VOLUME_LABEL IS NOT NULL THEN 'VOLUME_LABEL'
    				   WHEN IS_CODE IS NOT NULL THEN 'IS_CODE' END
    from @Table

    output below


    If you use the behaviour of string concatenation with null,  you may re-write the above query as below, still not nice.

    --sample data generation
    DECLARE @Table TABLE (SORT_ORDER INT, LAG_STATUS INT,VOLUME_LABEL INT,IS_CODE INT)
    INSERT INTO @Table
    SELECT SORT_ORDER=Null, LAG_STATUS=Null, VOLUME_LABEL=1,IS_CODE=Null UNION
    SELECT SORT_ORDER=123, LAG_STATUS=Null, VOLUME_LABEL=1,IS_CODE=Null
    
    SELECT * FROM @Table
    
    --retrieve column name and value using COALESCE.
    ;WITH Q AS
    (
    	SELECT COALESCED_DATA=
    		COALESCE(
    				'SORT_ORDER:'	+ CAST(SORT_ORDER AS VARCHAR(50)), 
    				'LAG_STATUS:'	+ CAST(LAG_STATUS AS VARCHAR(50)), 
    				'VOLUME_LABEL:' + CAST(VOLUME_LABEL AS VARCHAR(50)),
    				'IS_CODE:'		+ CAST(IS_CODE AS VARCHAR(50))		
    			)
    	FROM @Table
    )
    SELECT COALESCED_DATA, 
    	   ColName=LEFT(COALESCED_DATA,CHARINDEX(':',COALESCED_DATA)-1),
    	   ColValue=RIGHT(COALESCED_DATA,LEN(COALESCED_DATA)-CHARINDEX(':',COALESCED_DATA))
    FROM Q
    

    • Edited by negus_king Thursday, August 31, 2017 12:57 PM
    • Marked as answer by Annapureddy Friday, September 1, 2017 9:36 AM
    Thursday, August 31, 2017 12:23 PM
  • That what COALESCE does (CASE Expression) behind the scene.

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, August 31, 2017 12:26 PM
    Answerer
  • There is no built in function which will tell you which column is not NULL.  You would have to use a CASE statement to determine which one is NOT NULL.

    CASE WHEN SORT_ORDER IS NOT NULL THEN 'SORT_ORDER' ELSE CASE WHEN LAG_STATUS IS NOT NULL THEN 'LAG_STATUS' ELSE CASE ....

    • Proposed as answer by Xi Jin Friday, September 1, 2017 5:53 AM
    • Marked as answer by Annapureddy Friday, September 1, 2017 9:37 AM
    Thursday, August 31, 2017 12:30 PM