在寻找优化的解决方案时，考虑到源表将具有成千上万的记录。
源表：
CREATE TABLE [dbo].[StageINV](
[InvNo] [varchar](50) NULL,
[Cost1] [varchar](50) NULL,
[AM1] [money] NULL,
[Cost2] [varchar](50) NULL,
[AM2] [money] NULL,
[Cost3] [varchar](50) NULL,
[AM3] [money] NULL,
[Cost4] [varchar](50) NULL,
[AM4] [money] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[StageINV] ([InvNo], [Cost1], [AM1], [Cost2], [AM2], [Cost3], [AM3], [Cost4], [AM4]) VALUES (N'3435', N'12345', 4000.0000, N'23456', 600.0000, NULL, NULL, NULL, NULL)
INSERT [dbo].[StageINV] ([InvNo], [Cost1], [AM1], [Cost2], [AM2], [Cost3], [AM3], [Cost4], [AM4]) VALUES (N'3467', N'54545', 2000.0000, NULL, NULL, NULL, NULL, NULL, NULL)
INSERT [dbo].[StageINV] ([InvNo], [Cost1], [AM1], [Cost2], [AM2], [Cost3], [AM3], [Cost4], [AM4]) VALUES (N'3477', N'23456', 18000.0000, N'21414', 67800.0000, N'21567', 34500.0000, NULL, NULL)
INSERT [dbo].[StageINV] ([InvNo], [Cost1], [AM1], [Cost2], [AM2], [Cost3], [AM3], [Cost4], [AM4]) VALUES (N'3488', N'75698', 9000.0000, N'23235', 9800.0000, N'23434', 8967.0000, N'33455', 45445.0000)
INSERT [dbo].[StageINV] ([InvNo], [Cost1], [AM1], [Cost2], [AM2], [Cost3], [AM3], [Cost4], [AM4]) VALUES (N'3499', N'45678', 75.0000, NULL, NULL, NULL, NULL, NULL, NULL)
输出表：
CREATE TABLE [dbo].[MainInv](
[InvNo] [varchar](50) NOT NULL,
[Cost#] [varchar](50) NOT NULL,
[AM#] [money] NOT NULL
) ON [PRIMARY]
数据应如下所示（提供插入脚本）
GO
INSERT [dbo].[MainInv] ([InvNo], [Cost#], [AM#]) VALUES (N'1-3435', N'12345', 4000.0000)
INSERT [dbo].[MainInv] ([InvNo], [Cost#], [AM#]) VALUES (N'2-3435', N'23456', 600.0000)
INSERT [dbo].[MainInv] ([InvNo], [Cost#], [AM#]) VALUES (N'3467', N'54545', 2000.0000)
INSERT [dbo].[MainInv] ([InvNo], [Cost#], [AM#]) VALUES (N'1-3477', N'23456', 18000.0000)
INSERT [dbo].[MainInv] ([InvNo], [Cost#], [AM#]) VALUES (N'2-3477', N'21414', 67800.0000)
INSERT [dbo].[MainInv] ([InvNo], [Cost#], [AM#]) VALUES (N'3-3477', N'21567', 34500.0000)
INSERT [dbo].[MainInv] ([InvNo], [Cost#], [AM#]) VALUES (N'1-3488', N'75698', 9000.0000)
INSERT [dbo].[MainInv] ([InvNo], [Cost#], [AM#]) VALUES (N'2-3488', N'23235', 9800.0000)
INSERT [dbo].[MainInv] ([InvNo], [Cost#], [AM#]) VALUES (N'3-3488', N'23434', 8967.0000)
INSERT [dbo].[MainInv] ([InvNo], [Cost#], [AM#]) VALUES (N'4-3488', N'33455', 45445.0000)
INSERT [dbo].[MainInv] ([InvNo], [Cost#], [AM#]) VALUES (N'3499', N'45678', 75.0000)
它可以很简单
INSERT [dbo].[MainInv]
([InvNo], [Cost#], [AM#])
SELECT *, ROW_NUMBER() OVER(PARTITION BY InvNo ORDER BY [AM#]) AS myID
FROM
(
SELECT Inv1, Cost1, AM1
FROM dbo.StageInv
UNION ALL
SELECT Inv1, Cost2, AM2
FROM dbo.StageInv
UNION ALL
SELECT Inv1, Cost3, AM3
FROM dbo.StageInv
UNION ALL
SELECT Inv1, Cost4, AM4
FROM dbo.StageInv
) A
或者你可以使用 unpivot
SELECT [InvNo], [Cost#], [AM#]
,ROW_NUMBER() OVER(PARTITION BY InvNo ORDER BY [AM#]) as myID
FROM
(SELECT [InvNo], [Cost1], [AM1], [Cost2], [AM2], [Cost3], [AM3], [Cost4], [AM4]
FROM dbo.StageInv
) SRC
UNPIVOT
( [AM#] FOR AMS IN (AM1, AM2, AM3)
) C
UNPIVOT
( [Cost#] FOR CostS IN (Cost1, Cost2, Cost3)
) M
WHERE RIGHT(AmS, 1) = RIGHT(CostS, 1)
如果可以编辑目标表结构，建议在目标表中使用行标识符。因此，将来在您必须进行故障排除时，您可以轻松地找到数据来自哪一列。（是来自Inv1还是Inv2或Inv3）。$ 0.02
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句