title: Get ROWS as COLUMNS (SQL Server dynamic PIVOT query)
body: <p>I'm using MS SQL 2008 R2, have three tables with following schema:</p>
  
  <p>Table 1: Contains workshift info for each worker</p>
  
  <pre><code>CREATE TABLE workshift (
  [ws_id] [bigint] NOT NULL,
  [start_date] [datetime] NOT NULL,
  [end_date] [datetime] NOT NULL,
  [worker_id] [bigint] NOT NULL
  )
  
  INSERT INTO workshift VALUES (1, '2012-08-20 08:30:00', '2012-08-20 14:30:00', 1)
  INSERT INTO workshift VALUES (2, '2012-08-20 14:30:00', '2012-08-20 22:30:00', 2)
  </code></pre>
  
  <p>Table 2: Contains monetary denominations</p>
  
  <pre><code>CREATE TABLE currency_denom (
  [cd_id] [decimal](7, 2) NOT NULL,
  [name] [nchar](100) NOT NULL
  )
  
  INSERT INTO currency_denom VALUES (1, '100.00')
  INSERT INTO currency_denom VALUES (2, '50.00')
  INSERT INTO currency_denom VALUES (3, '20.00')
  INSERT INTO currency_denom VALUES (4, '10.00')
  INSERT INTO currency_denom VALUES (5, '5.00')
  INSERT INTO currency_denom VALUES (6, '1.00')
  </code></pre>
  
  <p>Table 3: Contains the quantity of each denomination the worker has received in every workshift</p>
  
  <pre><code>CREATE TABLE currency_by_workshift (
  [cd_id] [decimal](7, 2) NOT NULL,
  [ws_id] [bigint] NOT NULL,
  [qty] [int] NOT NULL
  )
  
  INSERT INTO currency_by_workshift VALUES (1, 1, 1)
  INSERT INTO currency_by_workshift VALUES (2, 1, 2)
  INSERT INTO currency_by_workshift VALUES (3, 1, 2)
  INSERT INTO currency_by_workshift VALUES (2, 2, 3)
  INSERT INTO currency_by_workshift VALUES (4, 2, 4)
  INSERT INTO currency_by_workshift VALUES (5, 2, 2)
  </code></pre>
  
  <p>I need to get the currency_by_workshift values in columns instead of rows, along with the workshift values, that is:</p>
  
  <pre><code>workshift |     workshift       |     workshift       | 100.00 | 50.00 | 20.00 | 10.00 | 5.00 | 1.00 
    ws_id   |     start_date      |     end_date        |        |       |       |       |      | 
  
      1     | 2012-08-20 08:30:00 | 2012-08-20 14:30:00 |    1   |   2   |   2   |   0   |   0  |   0
      2     | 2012-08-20 14:30:00 | 2012-08-20 22:30:00 |    0   |   2   |   0   |   4   |   2  |   0
  </code></pre>
  
  <p>I'm not able to use a case to count quantities for each currency denomination because they are configurable, if a new denomination is added, the query should be modified. Same applies if using PIVOT function, or I'm wrong?</p>
  
  <p>How can I get the info that way?</p>