SQL Loop Through Columns and Insert Results into Temp Table

Question

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

CarHierarchy seems like a deeply misguided design for a table. Data in a single row ought to be facts that are related to each other. data shouldn't be embedded in metadata as column names. What happens when there aren't equal numbers of each type of car? — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Jun 15, 2016 at 6:56

Community · Accepted Answer · 2021-01-18 12:32:17Z

3

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:

An Alternative (Better?) Method to UNPIVOT (SQL Spackle) by Dwain Camps

edited Jan 18, 2021 at 12:32

CommunityBot

11 silver badge

answered Jun 15, 2016 at 6:19

Felix Pamittan

31.9k7 gold badges43 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Loop Through Columns and Insert Results into Temp Table

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related