这是您的示例表
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
你的结果就在这里