我有兩個很好用的查詢，但我需要合并這兩個結果，但到目前為止還不能。就好像我需要調整次要結果，但它超出了我的知識范圍。
第一個查詢收集所有詳細資訊（為簡潔起見，省略了許多列）。如果備用代碼可用，則第二個獲取備用賬單代碼以填充到任何 NULL 或 EMPTY MisCptID 欄位中。
因此，如果 MisCptID_01 和 MisCptID_02 有資料，它們將保持原樣，但 3 - 6 需要使用備用 CPT 的可用串列。如果沒有替代的，則它們保持 NULL 或 EMPTY。
第一次查詢的結果（示例）：
ID Location MisCptID_01 MisCptID_02 MisCptID_03 MisCptID_04 MisCptID_05 MisCptID_06
AXXXXXXXX9443 OXXXXXOR 43248 43239 NULL NULL NULL NULL
AXXXXXXXX1163 EXXXXXED 43249 43247 43239 NULL NULL NULL
AXXXXXXXX1765 OXXXXXOR 43251 45385 45382 45384 45380 45381
第二個查詢的結果包含備用代碼（示例）：
ID AlternateCodeCode StandardAmount
AXXXXXXXX9443 80048 49.00
AXXXXXXXX9443 81001 38.00
AXXXXXXXX9443 76000 0.00
AXXXXXXXX1163 84703 80.50
AXXXXXXXX1163 82040 21.70
AXXXXXXXX1163 83036 49.00
AXXXXXXXX1163 85014 10.90
AXXXXXXXX1163 85018 10.90
AXXXXXXXX1163 86901 18.10
AXXXXXXXX1765 88305 185.00
AXXXXXXXX1765 88311 32.00
AXXXXXXXX1765 93005 125.00
AXXXXXXXX1765 80048 49.00
AXXXXXXXX1765 85027 10.80
合并查詢結果的期望結果：
ID Location MisCptID_01 MisCptID_02 MisCptID_03 MisCptID_04 MisCptID_05 MisCptID_06
AXXXXXXXX9443 OXXXXXOR 43248 43239 80048 81001 76000 NULL
AXXXXXXXX1163 EXXXXXED 43249 43247 43239 84703 83036 86901
AXXXXXXXX1765 OXXXXXOR 43251 45385 45382 45384 45380 45381
uj5u.com熱心網友回復：
您可以取消旋轉第一個查詢，將結果與第二個查詢合并，然后再次將它們旋轉回來，如下所示：
-- sample data:
DECLARE @ResultsOfFirstQuery TABLE (
ID VARCHAR(20) NOT NULL,
Location VARCHAR(10) NOT NULL,
MisCptID_01 INT NULL,
MisCptID_02 INT NULL,
MisCptID_03 INT NULL,
MisCptID_04 INT NULL,
MisCptID_05 INT NULL,
MisCptID_06 INT NULL
)
INSERT INTO @ResultsOfFirstQuery (ID, Location, MisCptID_01, MisCptID_02, MisCptID_03, MisCptID_04, MisCptID_05, MisCptID_06) VALUES
('AXXXXXXXX9443','OXXXXXOR',43248,43239,NULL ,NULL ,NULL ,NULL ),
('AXXXXXXXX1163','EXXXXXED',43249,43247,43239,NULL ,NULL ,NULL ),
('AXXXXXXXX1765','OXXXXXOR',43251,45385,45382,45384,45380,45381)
DECLARE @ResultsOfSecondQuery TABLE (
ID VARCHAR(20) NOT NULL,
AlternateCodeCode INT NOT NULL,
StandardAmount NUMERIC(10,2) NOT NULL
)
INSERT INTO @ResultsOfSecondQuery (ID, AlternateCodeCode, StandardAmount) VALUES
('AXXXXXXXX9443',80048,49.00 ),
('AXXXXXXXX9443',81001,38.00 ),
('AXXXXXXXX9443',76000,0.00 ),
('AXXXXXXXX1163',84703,80.50 ),
('AXXXXXXXX1163',82040,21.70 ),
('AXXXXXXXX1163',83036,49.00 ),
('AXXXXXXXX1163',85014,10.90 ),
('AXXXXXXXX1163',85018,10.90 ),
('AXXXXXXXX1163',86901,18.10 ),
('AXXXXXXXX1765',88305,185.00),
('AXXXXXXXX1765',88311,32.00 ),
('AXXXXXXXX1765',93005,125.00),
('AXXXXXXXX1765',80048,49.00 ),
('AXXXXXXXX1765',85027,10.80 )
-- query:
SELECT p.ID, q1.Location, p.[1] AS MisCptID_01, p.[2] AS MisCptID_02, p.[3] AS MisCptID_03, p.[4] AS MisCptID_04, p.[5] AS MisCptID_05, p.[6] AS MisCptID_06
FROM (
SELECT x.ID, x.Code, ROW_NUMBER() OVER (PARTITION BY x.ID ORDER BY x.SourceQuery, x.Position) AS Position
FROM (
SELECT u.ID, u.Code, 1 AS SourceQuery, RIGHT(u.ColumnName,2) AS Position
FROM (
SELECT ID, MisCptID_01, MisCptID_02, MisCptID_03, MisCptID_04, MisCptID_05, MisCptID_06
FROM @ResultsOfFirstQuery
) q
UNPIVOT (Code FOR ColumnName IN (MisCptID_01, MisCptID_02, MisCptID_03, MisCptID_04, MisCptID_05, MisCptID_06)) u
UNION ALL
SELECT ID, AlternateCodeCode, 2 AS SourceQuery, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID) AS Position
FROM @ResultsOfSecondQuery
) x
) y
PIVOT (MAX(Code) FOR Position IN ([1],[2],[3],[4],[5],[6])) p
INNER JOIN @ResultsOfFirstQuery q1 ON q1.ID = p.ID
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/485779.html