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 int,   Nummer varchar(10));
INSERT vorgang values('Jane', 'neu', 9121, '857685');
INSERT vorgang values('John', 'neu', 9122, '857686');
2 rows affected

WITH CTE AS
        (SELECT Name1, Status, CAST(Kundennummer AS VARCHAR(10)) as 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
9121

Nummer
857685

Name1
John

Status
neu

Kundennummer
9122

Nummer
857686


WITH CTE AS  -- mal mit einem falschen Typ aufgerufen, das geht schief
        (SELECT Name1, Status, CAST(Kundennummer AS VARCHAR(11)) as Kundennummer, Nummer 
           FROM vorgang)
  SELECT [Key],Value
    FROM CTE  
 UNPIVOT(Value FOR [Key] IN           
        (Name1, Status, Kundennummer, Nummer) ) AS unpvt  
Msg 8167 Level 16 State 1 Line 7
The type of column "Kundennummer" conflicts with the type of other columns specified in the UNPIVOT list.

WITH CTE AS -- einheitliche Typen, (die nicht dem Original entsprechen müssen) 
            -- das Statement läuft
        (SELECT CAST(Name1 AS VARCHAR(11)) as Name1, 
                CAST(Status AS VARCHAR(11)) as Status, 
                CAST(Kundennummer AS VARCHAR(11)) as Kundennummer, 
                CAST(Nummer AS VARCHAR(11)) as 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
9121

Nummer
857685

Name1
John

Status
neu

Kundennummer
9122

Nummer
857686


SELECT ID,[Key],Value
  FROM(SELECT Name1, Status,  CAST(Kundennummer AS VARCHAR(10)) as 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


9121
Name1
Jane

9121
Status
neu

9121
Kundennummer
9121

9121
Nummer
857685

9122
Name1
John

9122
Status
neu

9122
Kundennummer
9122

9122
Nummer
857686
Key	Value
Name1	Jane
Status	neu
Kundennummer	9121
Nummer	857685
Name1	John
Status	neu
Kundennummer	9122
Nummer	857686