SQL Server How to get a text ("N/A") for my pivoted data where values do not exist against pivoted column?

r7s23pms 于 2023-10-15 发布在其他

关注(0)|答案(1)|浏览(114)

Please find sample input tables named as Table1 and Table2.

Table1 :
| id | Dates | Sales |
| ------------ | ------------ | ------------ |
| 1 | 18 Sept'23 | 445.23 |
| 1 | 20 Sept'23 | 470.33 |
| 1 | 22 Sept'23 | 500.33 |

Table2 :

id	Dates
1	18 Sept'23
1	19 Sept'23
1	20 Sept'23
1	21 Sept'23
1	22 Sept'23
1	23 Sept'23
1	24 Sept'23

Desired output :

id	18 Sept'23	19 Sept'23	20 Sept'23	21 Sept'23	22 Sept'23	23 Sept'23	24 Sept'23
1	445.23	N/A	470.33	N/A	500.33	N/A	N/A

Declare @PvtQry nvarchar(max)
Declare @Qry nvarchar(max)

SELECT @PvtQry = COALESCE(@PvtQry+',','') + QUOTENAME([Dates]) FROM Table2 WHERE id = 1 Order by Dates Desc

Set @Qry = 'Select id,'+@PvtQry+' INTO ##Tbl_Temp
        FROM (Select id,Dates,Sales as SalesValue FROM Table1) AS Source
        PIVOT (Max (isnull(Cast(SalesValue as nvarchar(100)),''N/A'') FOR [Dates] IN ('+@PvtQry+') AS Pvt'
EXEC sp_executesql @Qry
SELECT * FROM ##Tbl_Temp

Error Message: Msg 102, Level 15, State 1, Line 3 Incorrect syntax near '('.

sql-server

来源：https://stackoverflow.com/questions/77163758/how-to-get-a-text-n-a-for-my-pivoted-data-where-values-do-not-exist-against

1条答案

按热度按时间

c9qzyr3d1#

The T-SQL generated in @qry is not valid, so I refactored it to work.

I moved the code to insert N/A from the PIVOT to the SELECT clause, the following is a sample query.

WITH source AS (
    SELECT t2.id, dates = FORMAT(t2.dates, 'dd MMM yy'), t1.sales
    FROM Table2 t2
    JOIN Table1 t1
        ON t2.id = t1.id
        AND t2.dates = t1.dates
)
Select id,[24 Sep 23] = IsNull(FORMAT([24 Sep 23], '0.00'), 'N/A'),[23 Sep 23] = IsNull(FORMAT([23 Sep 23], '0.00'), 'N/A'),[22 Sep 23] = IsNull(FORMAT([22 Sep 23], '0.00'), 'N/A'),[21 Sep 23] = IsNull(FORMAT([21 Sep 23], '0.00'), 'N/A'),[20 Sep 23] = IsNull(FORMAT([20 Sep 23], '0.00'), 'N/A'),[19 Sep 23] = IsNull(FORMAT([19 Sep 23], '0.00'), 'N/A'),[18 Sep 23] = IsNull(FORMAT([18 Sep 23], '0.00'), 'N/A')
FROM source
PIVOT (
    Max(sales) 
    FOR dates
    IN ([24 Sep 23],[23 Sep 23],[22 Sep 23],[21 Sep 23],[20 Sep 23],[19 Sep 23],[18 Sep 23]) 
    ) AS Pvt

To generate this code dynamically you need to use an additional variable to use in the SELECT clause, that cast the sales value to a string and manage the null value, in the following way:

DECLARE @dateFormat NVARCHAR(MAX) = 'dd MMM yy';
DECLARE @PvtQryForSelect nvarchar(max)
SELECT @PvtQryForSelect = COALESCE(@PvtQryForSelect+',','') +
    CONCAT(QUOTENAME(FORMAT([Dates],@dateFormat)),' = IsNull(FORMAT(', QUOTENAME(FORMAT([Dates], @dateFormat)), ', ''0.00''), ''N/A'')') FROM Table2 WHERE id = 1 Order by Dates Desc;

赞(0）回复(0）举报 2023-10-15

我来回答

SQL Server How to get a text ("N/A") for my pivoted data where values do not exist against pivoted column?

1条答案

相关问题

热门标签

最新问答