declare @query Nvarchar(max)=''
DECLARE @Vahed AS NVARCHAR(MAX)
If(OBJECT_ID('tempdb..#PivotTable') Is Not Null)
Begin
Drop Table #PivotTable
End
SELECT SUM(g.tedad_Daryaft) AS tedad_Daryaft,ns.Name_Farsi_Sanad,k.Kala_FarsiDesc ,k.Kala_Code ,f.VahedeAndazegiri_Name
INTO #PivotTable
FROM WareHouse.INV_Havaleh_Master s
JOIN WareHouse.INV_DarkhasteKala_Details g ON g.Havaleh_ID = s.Havaleh_ID
JOIN WareHouse.INV_Kala k ON k.Kala_ID = g.KALA_ID
JOIN WareHouse.INV_NoeSanad ns ON ns.NoeSanad_ID = s.NoeSanad_ID
JOIN WareHouse.INV_VahedeAndazegiri f ON f.VahedeAndazegiri_ID = k.Kala_VahedeAndazegiri
WHERE s.ComAnb_ID IN(SELECT x.ComAnb_ID FROM dbo.AllAnbarInfo x WHERE x.ShowInsite=1)
AND ns.NoeSanadTafkik_ID NOT IN(7)
AND LEFT(s.Havaleh_Tarikh,4)='1401'
AND k.Kala_Code IN(SELECT DISTINCT Id FROM dbo.ToTableVarchar('1601000020,1601000021,16010000 39,1601000022,1601000040,1601000040,1601000041,160 1000039,1601000041'))
AND ns.Name_Farsi_Sanad IN(N'فروش',N'فروش پرسنل')
GROUP BY ns.Name_Farsi_Sanad,k.Kala_FarsiDesc,k.Kala_Code ,f.VahedeAndazegiri_Name
ORDER BY k.Kala_FarsiDesc
select
@Vahed = ISNULL(@Vahed,'') + CASE
WHEN ISNULL(@Vahed,'') = '' THEN
'[' + Name_Farsi_Sanad + ']' else
',[' + Name_Farsi_Sanad + ']'
end
from #PivotTable
group by Name_Farsi_Sanad
set @query='
select * from #PivotTable
p
PIVOT
(
sum(tedad_Daryaft)
FOR Name_Farsi_Sanad in (' + @Vahed + ')
) AS PivotTable_M
'
print @query
exec (@query)
قوانین ایجاد تاپیک در تالار
پاسخ با نقل قول