I’m currently writing an SQL Script for Work. I want to store multiple Values in a temp_table with specific Columns. Later in the script I want to refer to the columns with a case. I Also want to refer to those columens with a specific row and i don’t know how.

Thats what i have for now:

CREATE TABLE ##temp_table (NUMBER3 integer, NUMBER4 integer, NUMBER5 integer, NUMBER6 integer, NUMBER7 integer, NUMBER8 integer, NUMBER9 integer);

INSERT INTO ##temp_table (NUMBER3, NUMBER4, NUMBER5, NUMBER6, NUMBER7, NUMBER8, NUMBER9)
VALUES ('10003','10004','10005','10006','10007','10008','10009'),
('20003','20004','20005','20006','20007','20008','20009'),
('30003','30004','30005','30006','30007','30008','30009'),
('40003','40004','40005','40006','40007','40008','40009'),
('50003','50004','50005','50006','50007','50008','50009'),
('60003','60004','60005','60006','60007','60008','60009'),
('70003','70004','70005','70006','70007','70008','70009'),
('80003','80004','80005','80006','80007','80008','80009');

-- SELECT * FROM ##temp_table -- View the Table
--

SELECT A.MEMBER,
B.GENDER,
B.BIRTHDATE,
...
...
...
C.MEMBER_NR,
CASE 
    WHEN C.MEMBER_NR = t.ANZAHL3 THEN '3'
    WHEN C.MEMBER_NR = t.ANZAHL4 THEN '4'
    WHEN C.MEMBER_NR = t.ANZAHL5 THEN '5'
    WHEN C.MEMBER_NR = t.ANZAHL6 THEN '6'
    WHEN C.MEMBER_NR = t.ANZAHL7 THEN '7'
    WHEN C.MEMBER_NR = t.ANZAHL8 THEN '8'
    WHEN C.MEMBER_NR = t.ANZAHL9 THEN '9'
    ELSE NULL
END AS NUMBER_MEMBERS,

Now a Dummy-Code

CASE -- Dummy-Code
    WHEN C.MEMBER_NR = t.ANZAHL3 in Row 3 THEN 'New'
    WHEN C.MEMBER_NR = t.ANZAHL3 in Row 5 THEN 'Old'
    ELSE NULL
END AS MEMBERS_Status,
...
...
--

FROM ....
    
CROSS JOIN ##temp_table AS t
    
WHERE ...

DROP TABLE ##temp_table;