CODEWITHSUNDEEP
sqlsql-serverstringstored-proceduresunpivot
marko
marko

Reputation: 517

Stored procedure to unpivot

I have a table with these columns:

surname  name  birthdate  id_doc  role   01         02       ... 50
Vardy    Jack  19870215    1234   'emp'  20200110   20200527     20200610

Where fields from 01 to 50 are dates. I need to have a table like this:

surname  name  birthdate  id_doc  role   title_code   data
Vardy    Jack  19870215    1234   'emp'     01        20200110   
Vardy    Jack  19870215    1234   'emp'     02        20200527     
....

Where title code should be the column name from 01 to 50, data should be the value of that column in mytable

I did this stored procedure by looking in older questions:

CREATE PROCEDURE proc
AS
    BEGIN
    DECLARE @UnpivotList NVARCHAR(MAX) = N'';
    SELECT @UnpivotList = CONCAT(@UnpivotList, ',(''', c.name, ''', fe.', c.name, ')')
    FROM 
       sys.columns c
    WHERE 
       c.object_id = OBJECT_ID('mytable')
       AND c.column_id > 5;

    DECLARE @sql NVARCHAR(MAX) = CONCAT(N'
    SELECT 
       fe.surname,
       fe.name,
       fe.birthdate,
       fe.id_doc,
       fe.role,
       ul.title_code,
       ul.Data
    FROM 
       mytable fe
       CROSS APPLY ( VALUES ', STUFF(@UnpivotList, 1, 1, ''), N') ul (title_code, Data)');
    EXEC sys.sp_executesql @sql;
END

but I'm getting a syntax error near '.01'. Could someone help me please?

Upvotes: 2

Views: 343

Answers (1)

GMB
GMB

Reputation: 222582

The problem is with the column names: since they start with a digit, you need to quote the identifiers - in SQL Server, you need to use square brackets. You can take care of that this in the first query.

SELECT @UnpivotList = CONCAT(@UnpivotList, ',(''', c.name, ''', fe.[', c.name, '])')
FROM sys.columns c                                               --^    here    ^ --
WHERE 
   c.object_id = OBJECT_ID('mytable')
   AND c.column_id > 5;

Upvotes: 2

Related Questions