mysql 动态日期_sql – 如何动态地使用日期作为列进行转动

这是您的示例表

SELECT * INTO #Names

FROM

(

SELECT 1 ID,'ITEM1' NAME

UNION ALL

SELECT 2 ID,'ITEM2' NAME

)TAB

SELECT * INTO #Stockdates

FROM

(

SELECT 1 ID,1 NAMEID,8 STOCK,'2-1-2014 ' [DATE]

UNION ALL

SELECT 2 ID,2 NAMEID,2 STOCK,'4-1-2014 ' [DATE]

)TAB

将连接数据放入一个temperory表

SELECT N.NAME,S.[DATE],S.STOCK

INTO #TABLE

FROM #NAMES N

JOIN #Stockdates S ON N.ID=S.NAMEID

获取枢轴列

DECLARE @cols NVARCHAR (MAX)

SELECT @cols = COALESCE (@cols + ',[' + CONVERT(NVARCHAR,[DATE],106) + ']','[' + CONVERT(NVARCHAR,106) + ']')

FROM (SELECT DISTINCT [DATE] FROM #TABLE) PV

ORDER BY [DATE]

现在转动它

DECLARE @query NVARCHAR(MAX)

SET @query = '

SELECT * FROM

(

SELECT * FROM #TABLE

) x

PIVOT

(

SUM(STOCK)

FOR [DATE] IN (' + @cols + ')

) p

'

EXEC SP_EXECUTEsql @query

你的结果就在这里