0

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.

Chesco1997
  • 73
  • 1
  • 8
  • 1
    You just missed out the column, `worker` in your dynamic statement. If should be in both your `SELECT`s. A column won't be returned if you don't ask for it. The *easiest* way to debug a dynamic statement is the `PRINT`/`SELECT` it, resolve the problem there and then propagate the solution. If you `PRINT` your dynamic statement you'll quickly see where the column `Worker` needs to be put into the statement, and thus where it need to be put in in your dynamic statement. – Thom A Jun 01 '21 at 09:10
  • I don´t really understand what do you mean with `PRINT` my dynamic statement. Do you mean I should add `PRINT @query`? The only thing I see with this is the code I´m already using. – Chesco1997 Jun 01 '21 at 09:55
  • 1
    Literally what I say. `PRINT @query;`/`SELECT @query`; which will give you the dynamic statement you are executing. – Thom A Jun 01 '21 at 09:56
  • Thank you! It was working, I got the query that I needed changing the `SELECT @query`, but doing it was not dynamic. While I was trying to implement it in the original I got [this answer](https://stackoverflow.com/a/67787034/15915329) that already works. I´ll use these commands for future problems! – Chesco1997 Jun 01 '21 at 11:03

1 Answers1

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;
Reza Esmaeli
  • 146
  • 7