NettieEckenrode
asked on
SYNTAX ERROR WITH IN CLAUSE IN PIVOT QUERY Msg 102, Level 15, State 1, Line 24
declare @MyCol as varchar(250)
set @MyCol = (SELECT R2.MYMONTH + ', '
FROM SALES_TABLE_Col R2
FOR XML PATH(''))
SELECT
SALE_LINE, SALES_PART, SALES_PART_DESC, 'MYSORT' AS SORT_TYPE, OVERDUE, ' + @MyCol + ' FUTUREDUE, PENDING
FROM
( SELECT SALE_LINE, SALES_PART, SALES_PART_DESC, MYMONTH, SUM(SALES_AMT) AS SALES_AMT
FROM
(SELECT SALE_LINE, SALES_PART, SALES_PART_DESC,
CASE WHEN SALES_DATE < GETDATE() AND STATUS <> 'H' THEN 'OVERDUE'
WHEN SALES_DATE > [ENDING_DATE] AND STATUS <> 'H' THEN 'FUTUREDUE'
WHEN STATUS = 'H' OR SALES_DATE IS NULL THEN 'PENDING'
ELSE MYMONTH END AS MYMONTH,
SALES_AMT
FROM SALES_TABLE
) MYSALES
GROUP BY SALE_LINE, SALES_PART, SALES_PART_DESC, MYMONTH
) AS SourceTable
PIVOT
(SUM(SALES_AMT)
FOR [MYMONTH] IN ( OVERDUE, ' + @MyCol + ' FUTUREDUE, PENDING)
) AS PivotTable
set @MyCol = (SELECT R2.MYMONTH + ', '
FROM SALES_TABLE_Col R2
FOR XML PATH(''))
SELECT
SALE_LINE, SALES_PART, SALES_PART_DESC, 'MYSORT' AS SORT_TYPE, OVERDUE, ' + @MyCol + ' FUTUREDUE, PENDING
FROM
( SELECT SALE_LINE, SALES_PART, SALES_PART_DESC, MYMONTH, SUM(SALES_AMT) AS SALES_AMT
FROM
(SELECT SALE_LINE, SALES_PART, SALES_PART_DESC,
CASE WHEN SALES_DATE < GETDATE() AND STATUS <> 'H' THEN 'OVERDUE'
WHEN SALES_DATE > [ENDING_DATE] AND STATUS <> 'H' THEN 'FUTUREDUE'
WHEN STATUS = 'H' OR SALES_DATE IS NULL THEN 'PENDING'
ELSE MYMONTH END AS MYMONTH,
SALES_AMT
FROM SALES_TABLE
) MYSALES
GROUP BY SALE_LINE, SALES_PART, SALES_PART_DESC, MYMONTH
) AS SourceTable
PIVOT
(SUM(SALES_AMT)
FOR [MYMONTH] IN ( OVERDUE, ' + @MyCol + ' FUTUREDUE, PENDING)
) AS PivotTable
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
declare @sql varchar(8000) = '
SELECT
to
declare @sql varchar(8000)
set @sql = ' select
Thanks so much.