CODEWITHSUNDEEP
sqlsql-server-2008t-sql
Mirodil
Mirodil

Reputation: 2329

How can get null column after UNPIVOT?

I have got the following query:

WITH data AS( 
    SELECT * FROM partstat WHERE id=4
)
SELECT id, AVG(Value) AS Average
FROM (
    SELECT id, 
    AVG(column_1) as column_1, 
    AVG(column_2) as column_2, 
    AVG(column_3) as column_3 
    FROM data
    GROUP BY id
) as pvt
UNPIVOT (Value FOR V IN (column_1,column_2,column_3)) AS u
GROUP BY id

if column_1,column_2 and column_3 (or one of this columns) have values then i get result as the following:

id, Average

4, 5.12631578947368

if column_1,column_2 and column_3 have NULL values then the query does not return any rows as the following:

id, Average

my question is how can i get as the following result if columns contents NULL values?

id, Average

4, NULL

Upvotes: 1

Views: 5743

Answers (2)

Ivan Golović
Ivan Golović

Reputation: 8832

Here is an example without UNPIVOT:

DECLARE @partstat TABLE (id INT, column_1 DECIMAL(18, 2), column_2 DECIMAL(18, 2), column_3 DECIMAL(18, 2))
INSERT @partstat VALUES 
(5, 12.3, 1, 2)
,(5, 2, 5, 5)
,(5, 2, 2, 2)
,(4, 2, 2, 2)
,(4, 4, 4, 4)
,(4, 21, NULL, NULL)
,(6, 1, NULL, NULL)
,(6, 1, NULL, NULL)
,(7, NULL, NULL, NULL)
,(7, NULL, NULL, NULL)
,(7, NULL, NULL, NULL)
,(7, NULL, NULL, NULL)
,(7, NULL, NULL, NULL)


;WITH data AS( 
    SELECT * FROM @partstat
)
SELECT  

        pvt.id,
        (ISNULL(pvt.column_1, 0) + ISNULL(pvt.column_2, 0) + ISNULL(pvt.column_3, 0))/ 
        NULLIF(
            CASE WHEN pvt.column_1 IS NULL THEN 0 ELSE 1 END +
            CASE WHEN pvt.column_2 IS NULL THEN 0 ELSE 1 END +
            CASE WHEN pvt.column_3 IS NULL THEN 0 ELSE 1 END
        , 0)
        AS Average
FROM (
    SELECT id, 
    AVG(column_1) as column_1, 
    AVG(column_2) as column_2, 
    AVG(column_3) as column_3 
    FROM data
    GROUP BY id
) as pvt

Upvotes: 1

Charleh
Charleh

Reputation: 14002

Have you tried using COALESCE or ISNULL?

e.g.

ISNULL(AVG(column_1), 0) as column_1,

This does mean that you will get 0 as the result instead of 'NULL' though - do you need null when they are all NULL?

Edit:

Also, is there any need for an unpivot? Since you are specifying all 3 columns, why not just do:

SELECT BankID, (column_1 + column_2 + column_3) / 3 FROM partstat
WHERE bankid = 4

This gives you the same results but with the NULL

Of course this is assuming you have 1 row per bankid

Edit:

UNPIVOT isn't supposed to be used like this as far as I can see - I'd unpivot first then try the AVG... let me have a go...

Edit:

Ah I take that back, it is just a problem with NULLs - other posts suggest ISNULL or COALESCE to eliminate the nulls, you could use a placeholder value like -1 which could work e.g.

SELECT bankid, AVG(CASE WHEN value = -1 THEN NULL ELSE value END) AS Average 
FROM ( 
    SELECT bankid,  
    isnull(AVG(column_1), -1) as column_1 ,
    AVG(Column_2) as column_2 ,
    Avg(column_3) as column_3 
    FROM data     
    group by bankid
) as pvt 
UNPIVOT (Value FOR o in (column_1, column_2, column_3)) as u
GROUP BY bankid

You need to ensure this will work though as if you have a value in column2/3 then column_1 will no longer = -1. It might be worth doing a case to see if they are all NULL in which case replacing the 1st null with -1

Upvotes: 1

Related Questions