I tried to convert the (null) values with 0 (zeros) output in PIVOT function but have no sucess.
Below is the table and the syntax i've tried"
SELECT
CLASS,
[AZ],
[CA],
[TX]
FROM #TEMP
PIVOT (SUM(DATA)
FOR STATE IN ([AZ], [CA], [TX])) AS PVT
ORDER BY CLASS
CLASS AZ CA TX
RICE 10 4 (null)
COIN 30 3 2
VEGIE (null) (null) 9
I tried to use the ISNULL
but did not work.
PIVOT SUM(ISNULL(DATA,0)) AS QTY
Could someone please look into its syntax error? Thanks a lot!
This question is related to
sql
sql-server
pivot
I have encountered similar problem.
The root cause is that (use your scenario for my case), in the #temp table, there is no record for
a. CLASS=RICE and STATE=TX
b. CLASS=VEGIE and (STATE=AZ or STATE=CA)
So, when MSSQL does pivot for no record, MSSQL always shows NULL for MAX, SUM, ... (aggregate functions)
None of above solutions (IsNull([AZ], 0)) works for me. But I do get ideas from these solutions. Thanks.
Sorry, it really depends on the #TEMP table. I can only provide some suggestions.
1. Make sure #TEMP table have records for below condition, even Data is null.
a. CLASS=RICE and STATE=TX
b. CLASS=VEGIE and (STATE=AZ or STATE=CA)
You may need to use cartesian product: select A.*, B.* from A, B
2. In the select query for #temp, if you need to join any table with WHERE, then would better put where inside another sub select query. (Goal is 1.)
3. Use isnull(DATA, 0) in #TEMP table.
4. Before pivot, make sure you have achieved Goal 1.
I can't give an answer to the orginal question, since there is no enough info for #temp table. I have pasted my code as example here, hope this will help others.
SELECT * FROM (_x000D_
SELECT eeee.id as enterprise_id_x000D_
, eeee.name AS enterprise_name_x000D_
, eeee.indicator_name_x000D_
, CONVERT(varchar(12) , isnull(eid.[date],'2019-12-01') , 23) AS data_date_x000D_
, isnull(eid.value,0) AS indicator_value_x000D_
FROM (select ei.id as indicator_id, ei.name as indicator_name, e.* FROM tbl_enterprise_indicator ei, tbl_enterprise e) eeee _x000D_
LEFT JOIN (select * from tbl_enterprise_indicator_data WHERE [date]='2020-01-01') eid_x000D_
ON eeee.id = eid.enterprise_id and eeee.indicator_id = enterprise_indicator_id_x000D_
) AS P _x000D_
PIVOT _x000D_
(_x000D_
SUM(P.indicator_value) FOR P.indicator_name IN(TX,CA)_x000D_
) AS T
_x000D_
Sometimes it's better to think like a parser, like T-SQL parser. While executing the statement, parser does not have any value in Pivot section and you can't have any check expression in that section. By the way, you can simply use this:
SELECT CLASS
, IsNull([AZ], 0)
, IsNull([CA], 0)
, IsNull([TX], 0)
FROM #TEMP
PIVOT (
SUM(DATA)
FOR STATE IN (
[AZ]
, [CA]
, [TX]
)
) AS PVT
ORDER BY CLASS
You have to account for all values in the pivot set. you can accomplish this using a cartesian product.
select pivoted.*
from (
select cartesian.key1, cartesian.key2, isnull(relationship.[value],'nullvalue') as [value]
from (
select k1.key1, k2.key2
from ( select distinct key1 from relationship) k1
,( select distinct key2 from relationship) k2
) cartesian
left outer join relationship on relationship.key1 = cartesian.key1 and relationship.key2 = carterisan.key2
) data
pivot (
max(data.value) for ([key2_v1], [key2_v2], [key2_v3], ...)
) pivoted
If you have a situation where you are using dynamic columns in your pivot statement you could use the following:
DECLARE @cols NVARCHAR(MAX)
DECLARE @colsWithNoNulls NVARCHAR(MAX)
DECLARE @query NVARCHAR(MAX)
SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(Name)
FROM Hospital
WHERE Active = 1 AND StateId IS NOT NULL
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
SET @colsWithNoNulls = STUFF(
(
SELECT distinct ',ISNULL(' + QUOTENAME(Name) + ', ''No'') ' + QUOTENAME(Name)
FROM Hospital
WHERE Active = 1 AND StateId IS NOT NULL
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
EXEC ('
SELECT Clinician, ' + @colsWithNoNulls + '
FROM
(
SELECT DISTINCT p.FullName AS Clinician, h.Name, CASE WHEN phl.personhospitalloginid IS NOT NULL THEN ''Yes'' ELSE ''No'' END AS HasLogin
FROM Person p
INNER JOIN personlicense pl ON pl.personid = p.personid
INNER JOIN LicenseType lt on lt.licensetypeid = pl.licensetypeid
INNER JOIN licensetypegroup ltg ON ltg.licensetypegroupid = lt.licensetypegroupid
INNER JOIN Hospital h ON h.StateId = pl.StateId
LEFT JOIN PersonHospitalLogin phl ON phl.personid = p.personid AND phl.HospitalId = h.hospitalid
WHERE ltg.Name = ''RN'' AND
pl.licenseactivestatusid = 2 AND
h.Active = 1 AND
h.StateId IS NOT NULL
) AS Results
PIVOT
(
MAX(HasLogin)
FOR Name IN (' + @cols + ')
) p
')
To modify the results under pivot, you can put the columns in the selected fields and then modify them accordingly. May be you can use DECODE for the columns you have built using pivot function.
You cannot place the IsNull()
until after the data is selected so you will place the IsNull()
around the final value in the SELECT
:
SELECT CLASS,
IsNull([AZ], 0) as [AZ],
IsNull([CA], 0) as [CA],
IsNull([TX], 0) as [TX]
FROM #TEMP
PIVOT
(
SUM(DATA)
FOR STATE IN ([AZ], [CA], [TX])
) AS PVT
ORDER BY CLASS
Source: Stackoverflow.com