c# unpivot columns in rows

Question

I have these columns:

1. ID
2. Name
3. address
4. a
5. b
6. c
7. d
8. e
9. f

I need to unpivot columns from a to f to have:

ID Name Address Res

CREATE PROCEDURE [dbo].[sProcedure]
AS
    
    BEGIN
    DECLARE @UnpivotList NVARCHAR(MAX) = N'';
    SELECT @UnpivotList = CONCAT(@UnpivotList, ',(''fe.[', c.name, '])')
    FROM 
       sys.columns c
    WHERE 
       c.object_id = OBJECT_ID('_myTable')
       AND c.column_id >3;


    DECLARE @sql NVARCHAR(MAX) = CONCAT(N'
    SELECT 
       fe.ID,
       fe.Name,
       fe.Address,
       ul.res
    INTO
       newTable
    FROM 
      myTable fe
       CROSS APPLY ( VALUES ', STUFF(@UnpivotList, 1, 1, ''), N') ul (res)'
       );
    EXEC sys.sp_executesql @sql;
END 

but it isn't working, could somebody help me please?

Answer 1

Here is an option that will "dynamically" unpivot your data without actually using dynamic SQL

If <2016 ... there is an XML approach

Example

Declare @YourTable Table ([ID] varchar(50),[Name] varchar(50),[address] varchar(50),[a] varchar(50),[b] varchar(50),[c] varchar(50),[d] varchar(50),[e] varchar(50),[f] varchar(50))  Insert Into @YourTable Values 
 (1,'Jane','123 Main','val1','val2','val3','val4','val5','val6')
 
Select A.ID
      ,A.Name
      ,A.Address
      ,C.* 
 From  @YourTable A
 Cross Apply ( Select A.* for JSON Path,Without_Array_Wrapper) B(JSONData)
 Cross Apply (
                Select [Key]
                      ,[Value]
                 From OpenJson(JSONData) 
                 Where [Key] not in ('ID','Name','address')   ---<< Optional
             )  C

Returns

ID  Name    Address     Key   Value
1   Jane    123 Main    a     val1
1   Jane    123 Main    b     val2
1   Jane    123 Main    c     val3
1   Jane    123 Main    d     val4
1   Jane    123 Main    e     val5
1   Jane    123 Main    f     val6