【解決方法】null の処理、ピボットでの動的列

[ad_1]

こんにちは、合計列でピボットしようとしているデータセットに null があるため、合計列が null として出力されます。私のコードは次のとおりです

DECLARE @cols       NVARCHAR(MAX)=''
DECLARE @query      NVARCHAR(MAX)=''
DECLARE @COLS_SUM   NVARCHAR(MAX)=''

--Preparing columns for Pivot
SELECT @cols = @cols + QUOTENAME(finmonth) + ',' 
FROM (SELECT DISTINCT finmonth FROM [DATASOURCE] ) AS tmp
SELECT @cols = SUBSTRING(@cols, 0, LEN(@cols))

--Preparing sum of columns for Totals Horizontal
SELECT @COLS_SUM = @COLS_SUM + QUOTENAME(finmonth) + '+' 
FROM (SELECT DISTINCT finmonth FROM [[DATASOURCE] ) AS tmp
SELECT @COLS_SUM = ','+ SUBSTRING(@COLS_SUM, 0, LEN(@COLS_SUM)) +' AS TOTAL'


SET @query = 
            'SELECT *'+@COLS_SUM+'  INTO #TAB FROM 
            (
                    SELECT var1, var2, var3, finmonth, sum(Actual_Activity) as [activity]
  FROM [[DATASOURCE]
  where [FinYear] = ''2018''
    group by  var1, var2, var3, finmonth
            ) src
            PIVOT 
            (
                   SUM([activity])  FOR finmonth IN (' + @cols + ')
            ) piv

            SELECT * FROM #TAB
            

            '

execute(@query)

問題の原因となるダミーデータの例を次に示します。したがって、1 つの Var1 (以下の var1=a) には finmonth=2 のデータがありますが、別の Var1 にはありません。 Var1 = b は、今月のデータがないため、finmonth = 2 に対して null になります。

var1	var2	var3	finmonth	Actual_activity
a	b	c	1	20
a	b	c	1	30
a	b	c	1	40
a	b	c	2	10
a	b	c	2	20
a	b	c	2	30
b	b	c	1	1
b	b	c	1	2
b	b	c	1	3

結果テーブルは次のようになりますが、2 行目の合計は 6 になるはずです

r1	var2	var3	1	2	Total
a	b	c	90	60	150
b	b	c	6	Null	Null

私が試したこと：

クエリでメイングループで isnull を使用してみましたが、効果がないようです

解決策 1
解決策 2

解決策 1

SQL ステートメントの静的バージョンは次のようになります。

SQL

SELECT var1, var2, var3, [1], [2], COALESCE([1], 0) + COALESCE([2], 0) AS Total
FROM (
    SELECT var1, var2, var3, finmonth, Actual_Activity
    FROM YOUR_TABLE
    WHERE FinYear = 2018) AS DT
PIVOT(SUM(Actual_Activity) FOR finmonth IN ([1], [2])) AS PVT

ここで、動的バージョンに「変換」する必要があります。
避けたい場合 NULLが入っています [1] と [2] cols、使用できます COALESCE それらの周りの方法。

ノート： FinYear と finmonth 数値フィールドでなければなりません!

[EDIT]

正しいデータを取得したい場合 @COLS_SUM 変数、これを確認してください：

SQL

SELECT @COLS_SUM = @COLS_SUM + 'COALESCE(' + QUOTENAME(finmonth) + ',0)+' 
FROM (SELECT DISTINCT finmonth FROM YOUR_TABLE ) AS tmp
SELECT @COLS_SUM = ','+ SUBSTRING(@COLS_SUM, 0, LEN(@COLS_SUM)) +' AS TOTAL'

解決策 2

CREATE プロシージャテスト
@whs を nvarchar(max) として、
@pln を nvarchar(max) として、
@brand as nvarchar(max),
@name as nvarchar(max),
@category を nvarchar(max) として、
@subcategory as nvarchar(max)
なので
始める

— 余分な結果セットを防ぐために、SET NOCOUNT ON が追加されました。
— SELECT ステートメントを妨害しています。
NOCOUNT をオンに設定します。
@column nvarchar(max) を宣言します
@Query nvarchar(max) を宣言する
@price nvarchar(max) を宣言します
@last nvarchar(max) を宣言する
@with nvarchar(10) を宣言
DECLARE @colsWithNoNulls NVARCHAR(MAX)

IF @サブカテゴリ > 0
始める
@with=”and” を設定
終わり
それ以外
始める
@with=”or” を設定
終わり

set @column= STUFF((Select ‘],[‘+isnull(whsname,0) from OWHS WS CROSS APPLY string_split(@whs, ‘,’) as UI WHERE WS.WhsCode = UI.value FOR XML PATH(”)),1,2,”) +’]’
set @price= STUFF((Select ‘],[‘+isnull(ListName,0) from OPLN PN CROSS APPLY string_split(ISNULL(@pln,0), ‘,’) as UX WHERE ISNULL(PN.ListNum,0) = ISNULL(UX.value,0) FOR XML PATH(”)),1,2,”) +’]’

set @Query=’select * from (select w.ItemCode,m.ItemName ,b.ItmsGrpNam ItemGroupName,isnull(CAST(m.usertext AS NVARCHAR(Max)),””) SpaceOrType
,m.OnHand InStock,m.IsCommited コミット済み,m.OnOrder 注文済み
,(m.OnHand-m.IsCommited+m.OnOrder) Available,n.ListName ListName,c.Price priceL,s.WhsName,sum(w.OnHand) 在庫

からw
m.itemcode=w.itemcode で OITM m を右結合
s.WhsCode=w.WhsCode で OWHS を内部結合します。
内部結合 OITB b on b.ItmsGrpCod=m.ItmsGrpCod
m.ItemCode = c.ItemCode で左結合 ITM1 c
c.priceList = n.listNum で左結合 OPLN n
左結合 [@CATEGORY] h on m.U_Category = h.Code
左結合 [@SUBCATEGORY] i on h.Code = i.U_mainCategory
b.ItmsGrpCod = ”’+@brand+”’ または ”%’+@name+’%” のような w.ItemCode または ”%’+@name+’%” のような m.ItemName または h.Code =””‘+@category+”’ ‘+@with+’ i.Code=””‘+@subcategory+”’
w.ItemCode,m.itemName,b.ItmsGrpNam,m.U_Category,m.U_SubCategory,CAST(m.usertext AS NVARCHAR(max)),s.WhsName,m.OnHand,m.IsCommited,c.price,でグループ化m.OnOrder,n.ListName) fg
PIVOT (sum(priceL) for
ListName in (‘+ISNULL(@price,0)+’)) as piv
PIVOT (sum(Stock) for
(‘+@column+’)) の Whsname as piv’
実行 (@クエリ)
終わり

[ad_2]