22,207
社区成员
发帖
与我相关
我的任务
分享
内牙 数量 球碗 数量 平螺母 数量 FItemID
---------------- ---- --------- ------- ---------------- ---- -------
B.QZ.AM.TL.00024 1 P.CY.QW.00002 1 P.BJ.PL.00008 1 2010
B.QT.AM.TL.00022 1 P.CY.QW.00001 1 NULL 2010
M.QZ.AM.TL.00001 1 NULL NULL 2010
P.CY.SG.00054 2 NULL NULL 2011
我不知要怎么加?
WITH test(FNumber,FSeries,FItemID)
AS
(
SELECT 'M.QZ.AM.TL.00001',N'内牙','2010' UNION ALL
SELECT 'B.QZ.AM.TL.00024',N'内牙','2010' UNION ALL
SELECT 'B.QT.AM.TL.00022',N'内牙','2010' UNION ALL
SELECT 'P.BJ.PL.00008',N'平螺母','2010' UNION ALL
SELECT 'P.CY.QW.00001',N'球碗','2010' UNION ALL
SELECT 'P.CY.QW.00002',N'球碗','2010' UNION ALL
SELECT 'P.CY.SG.00054',N'内牙','2011'
)
,test1 AS
(
SELECT ROW_NUMBER() OVER(PARTITION BY FSeries ORDER BY GETDATE()) AS ID
,*
FROM test
)
--SELECT * FROM test1
SELECT [内牙],[球碗],[平螺母],FItemID
FROM test1 AS A
PIVOT (MAX(FNumber) FOR FSeries IN([内牙],[球碗],[平螺母]))p