Alkippe Nikephoros Alkippe Nikephoros - 5 months ago 13
SQL Question

SQL Loop Through Columns and Insert Results into Temp Table

I have a list of Car IDs in a temp #CarIDs

CREATE TABLE #CarIDs(
[CARID] [nvarchar] (60) NULL,
[Type] [nvarchar] (20) NULL,
[Flag] [nvarchar] (30) NULL) GO

INSERT INTO #CarIDs (CARID, Type, Flag) VALUES ('1111','',''), ('2222','',''), ('3333','',''), ('4444','',''), ('5555','',''), ('6666','','')


Which gives me
SELECT * FROM #CarIDs


+-------+-------+--------+
| CARID | Type | Flag |
+-------+-------+--------+
| 1111 | | |
| 2222 | | |
| 3333 | | |
| 4444 | | |
| 5555 | | |
| 6666 | | |
+-------+-------+--------+


How do I loop through the below table (CarHierarchy) to find out what model type is each CARID then insert into the temp table?

+-------+-------+-------+
| Jeep |Holden | Ford |
+-------+-------+-------+
| 1111 |2222 | 3333 |
| 4444 |6666 | 5555 |
+-------+-------+-------+


I expect the results to be #CarIDs:

+-------+-------+
| CARID | Type |
+-------+-------+
| 1111 |Jeep |
| 2222 |Holden |
| 3333 |Ford |
| 4444 |Jeep |
| 5555 |Ford |
| 6666 |Holden |
+------+--------+

Answer

You need to UNPIVOT first the #CarHierarchy table and then do a JOIN on the #CarIDs table to get the correct type:

-- Unpivot the #CarHierarchy table using CROSS APPLY
WITH CteCarHierarchy AS(
    SELECT *
    FROM #CarHierarchy
    CROSS APPLY( VALUES
        (Jeep, 'Jeep'),
        (Holden, 'Holden'),
        (Ford, 'Ford')
    )t (CARID, Type)
)
SELECT
    ci.CARID,
    Type = cch.Type,
    ci.Flag
FROM #CarIDs ci
INNER JOIN CteCarHierarchy cch
    ON cch.CARID = ci.CARID

The UPDATE statement:

ONLINE DEMO

WITH CteCarHierarchy AS(
    SELECT *
    FROM #CarHierarchy
    CROSS APPLY( VALUES
        (Jeep, 'Jeep'),
        (Holden, 'Holden'),
        (Ford, 'Ford')
    )t (CARID, Type)
)
UPDATE ci
    SET ci.Type = cch.Type
FROM #CarIDs ci
INNER JOIN CteCarHierarchy cch
    ON cch.CARID = ci.CARID

Reference: