DECLARE @columns NVARCHAR(MAX)
DECLARE @sql NVARCHAR(MAX)
SELECT @columns = STRING_AGG(QUOTENAME(NR), ',')
FROM L_CAPIWHOUSE
WHERE FIRMNR = 123
select @columns='[-1],'+@columns
SET @sql = '
SELECT LOGICALREF,
CODE,
DESCRIPTION,
UNIT, ' + @columns + '
FROM (
SELECT TF.LOGICALREF,
TF.CODE,
TF.NAME AS DESCRIPTION,
US.NAME AS UNIT,
WH.NR AS WAREHOUSE,
ISNULL(SUM(ST.ONHAND), 0) AS STOCK
FROM (
SELECT DISTINCT LOGICALREF, CODE, NAME, UNITSETREF
FROM LG_123_ITEMS
) AS TF
--LEFT JOIN LG_123_ITMUNITA IUA ON IUA.ITEMREF = TF.LOGICALREF
--LEFT JOIN LG_123_UNITSETL US ON US.LOGICALREF = IUA.UNITLINEREF
LEFT JOIN LG_123_UNITSETL US ON US.UNITSETREF = TF.UNITSETREF AND US.MAINUNIT = 1
CROSS JOIN (SELECT W.NR,W.FIRMNR FROM L_CAPIWHOUSE W UNION ALL SELECT -1 AS NR,123 AS FIRMNR) WH
LEFT JOIN LV_123_01_STINVTOT ST ON ST.STOCKREF = TF.LOGICALREF AND ST.INVENNO = WH.NR
WHERE WH.FIRMNR = 123
GROUP BY TF.LOGICALREF, TF.CODE, TF.NAME, US.NAME, WH.NR
) AS SourceTable
PIVOT (
MAX(STOCK)
FOR WAREHOUSE IN (' + @columns + ')
) AS PivotTable
ORDER BY LOGICALREF;'
-- 3. Dinamik SQL'i çalıştırın.
EXEC sp_executesql @sql
--Declare necessary variables
DECLARE @SQLQuery AS NVARCHAR(MAX)
DECLARE @PivotColumns AS NVARCHAR(MAX)
--Get unique values of pivot column
SELECT @PivotColumns= COALESCE(@PivotColumns + ',','') + QUOTENAME(TARIH)
FROM (SELECT DISTINCT TARIH FROM [dbo].BM_201_IK_SHAREKETLER) AS PivotExample
SELECT @PivotColumns
--Create the dynamic query with all the values for
--pivot column at runtime
SET @SQLQuery =
N'SELECT ADSOYAD,PROJE, ' + @PivotColumns + '
FROM [dbo].[BM_201_IK_SHAREKETLER]
PIVOT( COUNT(SAAT)
FOR TARIH IN (' + @PivotColumns + ')) AS P'
SELECT @SQLQuery
--Execute dynamic query
EXEC sp_executesql @SQLQuery
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX);
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(ADI)
FROM ZV_LOGO_MALZEME GROUP BY ADI
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT KODU,' + @cols + ' from
(
select KODU,ADI
from ZV_LOGO_MALZEME
) x
pivot
(
COUNT(ADI)
for ADI in (' + @cols + ')
) p
'
execute(@query)
--------------
İKİ FARKLI TABLODAN
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX);
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(DEFINITION_)
FROM LG_SLSMAN GROUP BY DEFINITION_
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT STGRPCODE,' + @cols + ' from
(
select LG_001_ITEMS.STGRPCODE,DEFINITION_
from LG_SLSMAN
CROSS JOIN LG_001_ITEMS
) x
pivot
(
MIN(DEFINITION_)
for DEFINITION_ in (' + @cols + ')
) p
'
execute(@query)
Blog of Sem 