Merging queries-CodePudding

I have two queries that work great but I need to merge the two results and have so far been unable to. It is almost as if I need to pivot the secondary results but it goes way above my knowledge.

The first query gathers all the details (many columns have been omitted for brevity). The second grabs alternate bill codes to be filled into any NULL or EMPTY MisCptID fields if an alternate code is available.

So if MisCptID_01 and MisCptID_02 have data they are left as is but then 3 - 6 need to use the available list of CPTs from the alternate ones. If there are no alternate ones then they stay NULL or EMPTY.

Results from first query (Sample):

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

Results from second Query containing alternate codes (Sample):

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

Desired Results for merging the query results:

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

CodePudding user response：

You can unpivot the first query, union the results with the second query and then pivot them back again, like this:

-- 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