SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select @@version;


(No column name)


Microsoft SQL Server 2022 (RTM) - 16.0.1000.6 (X64) 
	Oct  8 2022 05:58:25 
	Copyright (C) 2022 Microsoft Corporation
	Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)



create table vorgang(Name1 varchar(10),   Status varchar(10),      
                 Kundennummer varchar(10),   Nummer varchar(10));
INSERT vorgang values('Jane', 'neu', 'AFE9121', 857685);
INSERT vorgang values('John', 'neu', 'AFE9122', 857686);
2 rows affected

WITH CTE AS
        (SELECT Name1, Status, Kundennummer, Nummer 
           FROM vorgang)
  SELECT [Key],Value
    FROM CTE  
 UNPIVOT(Value FOR [Key] IN           
        (Name1, Status, Kundennummer, Nummer) ) AS unpvt  


Key
Value


Name1
Jane

Status
neu

Kundennummer
AFE9121

Nummer
857685

Name1
John

Status
neu

Kundennummer
AFE9122

Nummer
857686


SELECT ID,[Key],Value
  FROM(SELECT Name1, Status,  Kundennummer, Nummer,
              Kundennummer as ID         -- extra ID für Kontrolle in der Ausgabe
         FROM vorgang ) d  
UNPIVOT(Value FOR [Key] IN           
       (Name1, Status, Kundennummer, Nummer) ) AS unpvt  


ID
Key
Value


AFE9121
Name1
Jane

AFE9121
Status
neu

AFE9121
Kundennummer
AFE9121

AFE9121
Nummer
857685

AFE9122
Name1
John

AFE9122
Status
neu

AFE9122
Kundennummer
AFE9122

AFE9122
Nummer
857686

Key	Value
Name1	Jane
Status	neu
Kundennummer	AFE9121
Nummer	857685
Name1	John
Status	neu
Kundennummer	AFE9122
Nummer	857686