Advanced convert rows to columns (pivot) in SQL Server

Question

I have this table:

   ---------------------------------------
   | Id | worker | workStation   | amount 
   ---------------------------------------
   | 1  | John   | Suspension    |  5
   | 2  | John   | Wheels        |  8
   | 3  | Peter  | Wheels        |  1
   | 4  | Peter  | Engines       |  2
   ---------------------------------------

And I need a query that shows:

   -------------------------------------------
   | worker  | Suspension | Wheels | Engines
   -------------------------------------------
   | John    |     5      |   8    |   NULL
   | Peter   |     NULL   |   1    |   2
   -------------------------------------------

With the help of Efficiently convert rows to columns in sql server and https://learn.microsoft.com/es-es/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-ver15 (mainly with the first one) I've achieved:

   ---------------------------------
   | Suspension |  Wheels| Engines
   ---------------------------------
   |     5      |   8    |   NULL
   |     NULL   |   1    |   2
   ---------------------------------

It´s almost what I need, but I´m still missing the column to know the worker name.

I need the query to pivot with unknown number of workStations. My code is:

DECLARE @cols AS NVARCHAR(MAX),
@query  AS NVARCHAR(MAX)

select @cols =  STUFF((SELECT ',' + QUOTENAME(workStation)
                    FROM TableName
                    group by workStation
                    ORDER BY workStation
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = N'SELECT ' + @cols + N' from 
             (
                SELECT amount, worker, workStation
                FROM TableName
            ) x
            pivot 
            (
                max(amount)
                for workStationin (' + @cols + N')
            ) p '

exec sp_executesql @query;

How can I get this missing column? Thank you so much in advance.

Answer 1

You can select the worker and if you don't want a duplicate row you can Group by the worker

DECLARE @cols AS NVARCHAR(MAX),
@query  AS NVARCHAR(MAX)

select @cols =  STUFF((SELECT ',' + QUOTENAME(workStation)
                FROM TableName
                group by workStation
                ORDER BY workStation
        FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)') 
    ,1,1,'')

set @query = N'SELECT worker,' + @cols + N' from 
         (
            SELECT amount, worker, workStation
            FROM TableName
        ) x
        pivot 
        (
            max(amount)
            for workStationin (' + @cols + N')
        ) p '

exec sp_executesql @query;