Replacing null values in dynamic pivot sql query

debugcn 에 게시 Dev

Khushal_Jain

enter image description here

Image transcription:

FTE/RATE CARD | 2018-01-01 | 2018-02-01 | 2018-03-01 | 2018-04-01 | 2018-05-01 | 2018-06-01 | ...
-------------------------------------------------------------------------------------------------
FTE 3         | NULL       | NULL       | NULL       | 33         | 38         | 40.5       | ...

I have a pivot sql query as below. I need to replace NULL values with Zeros in the result. I don't know where do I exactly use the IsNull or Coalesce function in the query.

DECLARE @cols AS NVARCHAR(MAX),
        @query AS NVARCHAR(MAX) 
SELECT  @cols = STUFF
(
(
SELECT distinct ',' + QUOTENAME([MONTH])
FROM   [HEADCOUNT]
WHERE  [MONTH] BETWEEN '01-012018' AND '12-01-2018'
ORDER BY ',' + QUOTENAME([MONTH])
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,''
)
SET     @query = 'SELECT [FTE/RATE CARD],' + @cols + ' FROM
(
SELECT  [MONTH],[FTE/RATE CARD],[HC]
FROM    [HEADCOUNT]
WHERE   [CC-LOC] IN ([CC-LOC]) 

) x pivot (Sum ([HC]) for [MONTH] in (' + @cols + '))p' execute(@query)

MarcinJ

You'll need to generate another column list for the select statement, and wrap individual months with ISNULL, like so:

DECLARE @cols AS NVARCHAR(MAX),
        @selectCols AS NVARCHAR(MAX),
        @query AS NVARCHAR(MAX) 

SELECT  @selectCols = STUFF
(
(
SELECT distinct ', ISNULL(' + QUOTENAME([MONTH]) + ', 0) AS ' + QUOTENAME([MONTH])
FROM   [HEADCOUNT]
WHERE  [MONTH] BETWEEN '01-012018' AND '12-01-2018'
ORDER BY ',' + QUOTENAME([MONTH])
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,''
)



SELECT  @cols = STUFF
(
(
SELECT distinct ',' + QUOTENAME([MONTH])
FROM   [HEADCOUNT]
WHERE  [MONTH] BETWEEN '01-012018' AND '12-01-2018'
ORDER BY ',' + QUOTENAME([MONTH])
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,''
)
SET     @query = 'SELECT [FTE/RATE CARD],' + @selectCols + ' FROM
(
SELECT  [MONTH],[FTE/RATE CARD],[HC]
FROM    [HEADCOUNT]
WHERE   [CC-LOC] IN ([CC-LOC]) 

) x pivot (Sum ([HC]) for [MONTH] in (' + @cols + '))p' execute(@query)

이 기사는 인터넷에서 수집됩니다. 재 인쇄 할 때 출처를 알려주십시오.

침해가 발생한 경우 연락 주시기 바랍니다[email protected] 삭제

에서 수정2021-04-5

몇 마디 만하겠습니다

0리뷰

로그인참여 후 검토

Related 관련 기사

기사

Replacing null values in dynamic pivot sql query

Replacing null values in dynamic pivot sql query

SQL Server 2012 Pivot Dynamic with Values?

SQl cross pivot query error

SQL Server Dynamic Pivot Column Names

sql server - transforming data with dynamic pivot

Transform table structure of SQL query result (pivot)

SQL query using sum(column) in pivot function

SQL aggregate multiple values in a column then Pivot

SQL rows to columns (Pivot with just bit values)

Replacing a value with NULL in a table in SQL Server using vb.net

Problem passing a datetime variable into a dynamic SQL query

Play Scala Anorm dynamic SQL for UPDATE query

Returning null values with multiple table in a query

MySQL dynamic pivot table

Replacing null with zero

Sql query getting name column null

Replacing multiple occurrences of dynamic strings?

Not able to allow null values in my sql script

Why does this SQL order null values last?

Access Pivot Query

How to use Power Query to create pivot tables with non-numerical values

How to pivot values of timeindex

SQL Query for Group by with two sorting values and count the equal values

What is wrong with my dynamic SQL Query? Using a char data type

Null values from MQL query to business/sponsored_recipient/sponsorship

MySQL Single update query, without updating non-null values

Replacing null String to empty String

Replacing an element of a list with multiple values

SQL Server : how to pivot where new columns have multiple unique values

SQL Pivot data by time