CREATE TABLE #T (MON VARCHAR(3)) DECLARE @T TABLE(MON VARCHAR(3)) DECLARE @SN VARCHAR(2000) DECLARE @sql varchar(3000) SET @sql='' INSERT INTO #T SELECT DISTINCT LEFT(datename(m,recvdate),3) from ta;WITH S(M) AS( SELECT MON FROM #T FOR XML raw,TYPE) SELECT @SN=LEFT(REPLACE(REPLACE(CONVERT(VARCHAR(MAX),M),'<row MON="','['),'"/>','],'),LEN(REPLACE(REPLACE(CONVERT(VARCHAR(MAX),M),'<row MON="','['),'"/>','],'))-1) FROM S SET @sql='SELECT item,'+@SN+' FROM (SELECT item,qty,LEFT(datename(m,recvdate),3) AS [month] FROM ta) sourcepivot PIVOT( SUM(sourcepivot.qty) FOR [month] IN (SELECT MON FROM #T) ) AS pivottable' EXEC (@sql)还是报错 Msg 156, Level 15, State 1, Line 4 Incorrect syntax near the keyword 'SELECT'. Msg 102, Level 15, State 1, Line 4 Incorrect syntax near ')'.
先把exec 换成print 看看SQL串串
SELECT item,[Aug],[Jul],[Jun],[Oct],[Sep] FROM (SELECT item,qty,LEFT(datename(m,recvdate),3) AS [month] FROM ta) sourcepivot PIVOT( SUM(sourcepivot.qty) FOR [month] IN (SELECT MON FROM #T) ) AS pivottable上面就是print的结果,看起来应该没什么问题啊
你的语句问题还不少 (1)表变量的就不说了,楼上的已经解释很好了(2)动态sql中的pivot也有问题 试下这下改: CREATE TABLE ta (item VARCHAR(3),qty INT,recvdate DATETIME) INSERT INTO ta SELECT 'a',30,'2008-06-01 00:00:00.000' UNION ALL SELECT 'a',3,'2008-08-01 00:00:00.000' UNION ALL SELECT 'b',20,'2008-09-01 00:00:00.000' UNION ALL SELECT 'b',40,'2008-07-01 00:00:00.000' UNION ALL SELECT 'c',2,'2008-06-21 00:00:00.000' UNION ALL SELECT 'c',87,'2008-10-01 00:00:00.000'CREATE TABLE #T (MON VARCHAR(3)) DECLARE @SN VARCHAR(2000) DECLARE @sql varchar(3000) SET @sql='' INSERT INTO #t (MON) SELECT DISTINCT LEFT(datename(m,recvdate),3) from ta ; WITH S(M) AS( SELECT MON FROM #t FOR XML raw,TYPE ) SELECT @SN=LEFT(REPLACE(REPLACE(CONVERT(VARCHAR(MAX),M),'<row MON="','['),'"/>','],'),LEN(REPLACE(REPLACE(CONVERT(VARCHAR(MAX),M),'<row MON="','['),'"/>','],'))-1) FROM S SET @sql='SELECT item,'+@SN+' FROM (SELECT item,qty,LEFT(datename(m,recvdate),3) AS [month] FROM ta) sourcepivot PIVOT( SUM(sourcepivot.qty) FOR [month] IN ('+@SN+') ) AS pivottable' EXEC ( @sql)DROP TABLE #t
CREATE TABLE #T (MON VARCHAR(3))
DECLARE @T TABLE(MON VARCHAR(3))
DECLARE @SN VARCHAR(2000)
DECLARE @sql varchar(3000)
SET @sql=''
INSERT INTO #T SELECT DISTINCT LEFT(datename(m,recvdate),3) from ta;WITH S(M) AS(
SELECT MON FROM #T FOR XML raw,TYPE)
SELECT @SN=LEFT(REPLACE(REPLACE(CONVERT(VARCHAR(MAX),M),'<row MON="','['),'"/>','],'),LEN(REPLACE(REPLACE(CONVERT(VARCHAR(MAX),M),'<row MON="','['),'"/>','],'))-1) FROM S
SET @sql='SELECT item,'+@SN+' FROM (SELECT item,qty,LEFT(datename(m,recvdate),3) AS [month] FROM ta) sourcepivot
PIVOT(
SUM(sourcepivot.qty)
FOR [month] IN (SELECT MON FROM #T)
) AS pivottable'
EXEC (@sql)还是报错
Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'SELECT'.
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near ')'.
PIVOT(
SUM(sourcepivot.qty)
FOR [month] IN (SELECT MON FROM #T)
) AS pivottable上面就是print的结果,看起来应该没什么问题啊
(1)表变量的就不说了,楼上的已经解释很好了(2)动态sql中的pivot也有问题
试下这下改:
CREATE TABLE ta (item VARCHAR(3),qty INT,recvdate DATETIME)
INSERT INTO ta
SELECT 'a',30,'2008-06-01 00:00:00.000' UNION ALL
SELECT 'a',3,'2008-08-01 00:00:00.000' UNION ALL
SELECT 'b',20,'2008-09-01 00:00:00.000' UNION ALL
SELECT 'b',40,'2008-07-01 00:00:00.000' UNION ALL
SELECT 'c',2,'2008-06-21 00:00:00.000' UNION ALL
SELECT 'c',87,'2008-10-01 00:00:00.000'CREATE TABLE #T (MON VARCHAR(3))
DECLARE @SN VARCHAR(2000)
DECLARE @sql varchar(3000)
SET @sql=''
INSERT INTO #t (MON) SELECT DISTINCT LEFT(datename(m,recvdate),3) from ta
;
WITH S(M) AS(
SELECT MON FROM #t FOR XML raw,TYPE
)
SELECT @SN=LEFT(REPLACE(REPLACE(CONVERT(VARCHAR(MAX),M),'<row MON="','['),'"/>','],'),LEN(REPLACE(REPLACE(CONVERT(VARCHAR(MAX),M),'<row MON="','['),'"/>','],'))-1) FROM S
SET @sql='SELECT item,'+@SN+' FROM (SELECT item,qty,LEFT(datename(m,recvdate),3) AS [month] FROM ta) sourcepivot
PIVOT(
SUM(sourcepivot.qty)
FOR [month] IN ('+@SN+')
) AS pivottable'
EXEC ( @sql)DROP TABLE #t