title: Dynamic Pivot rows to Columns-SQL server
body: <p>I need to dynamically generate pivot based on columns from the table. </p>
  
  <p>Here is sample data which looks something like this.</p>
  
  <p><a href="https://i.stack.imgur.com/lgZ6p.png" rel="nofollow noreferrer">Data</a></p>
  
  <pre><code>+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  |                                                                                                                                                                                                                                                                                                    |
  +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  | +--------+----------------------+--------------+----------------------------------+---------+--------------+------+------+---------+---------+-----------------------+-----------------+------------------------------+-----------------------+-----------------------------------------+--------+ |
  | | Sr No  |    Invoice Date    |  Invoice No  |            Payer Name            |  IGMNo  | Container No | Size | Type | CHACode | CHAName |    Act Gatein Date    | Container Agent |    Container  Agent  Name    |    Importer Name    |        Activity Description        | Amount |          |
  | +--------+----------------------+--------------+----------------------------------+---------+--------------+------+------+---------+---------+-----------------------+-----------------+------------------------------+-----------------------+-----------------------------------------+--------+ |
  | |    1 | 7-1-2018 12:42:56 AM | MII180700001 | PROMEDIA TRADING PRIVATE LIMITED | 2198105 | DFSU1696812  |   20 | GB   |        |        | 6-28-2018 9:32:02 PM  | AACCT8966D    | T S LINES AGENCY            | PROMEDIATRADINGPRIVAT | Contrainer Ground Rent Charges        |    650 |          |
  | |    2 | 7-1-2018 12:42:56 AM | MII180700001 | PROMEDIA TRADING PRIVATE LIMITED | 2198105 | DFSU1696812  |   20 | GB   |        |        | 6-28-2018 9:32:02 PM  | AACCT8966D    | T S LINES AGENCY            | PROMEDIATRADINGPRIVAT | Fuel Charges                            |    850 |        |
  | |    3 | 7-1-2018 12:42:56 AM | MII180700001 | PROMEDIA TRADING PRIVATE LIMITED | 2198105 | DFSU1696812  |   20 | GB   |        |        | 6-28-2018 9:32:02 PM  | AACCT8966D    | T S LINES AGENCY            | PROMEDIATRADINGPRIVAT | Handling and  PNR Movement Charges    |   7400 |          |
  | |    4 | 7-1-2018 12:42:56 AM | MII180700001 | PROMEDIA TRADING PRIVATE LIMITED | 2198105 | DFSU1696812  |   20 | GB   |        |        | 6-28-2018 9:32:02 PM  | AACCT8966D    | T S LINES AGENCY            | PROMEDIATRADINGPRIVAT | Weighment Charges                    |    200 |           |
  | |    5 | 7-1-2018 12:42:56 AM | MII180700001 | PROMEDIA TRADING PRIVATE LIMITED | 2198105 | DFSU1699560  |   20 | GB   |        |        | 6-29-2018 12:10:52 AM | AACCT8966D    | T S LINES AGENCY            | PROMEDIATRADINGPRIVAT | Container Movement and Increase Charges |   1800 |        |
  | |    6 | 7-1-2018 12:42:56 AM | MII180700001 | PROMEDIA TRADING PRIVATE LIMITED | 2198105 | DFSU1699560  |   20 | GB   |        |        | 6-29-2018 12:10:52 AM | AACCT8966D    | T S LINES AGENCY            | PROMEDIATRADINGPRIVAT | Documentation Charges                |    250 |           |
  | |    7 | 7-1-2018 12:42:56 AM | MII180700001 | PROMEDIA TRADING PRIVATE LIMITED | 2198105 | DFSU1699560  |   20 | GB   |        |        | 6-29-2018 12:10:52 AM | AACCT8966D    | T S LINES AGENCY            | PROMEDIATRADINGPRIVAT | Fuel Charges                            |    850 |        |
  | |    8 | 7-1-2018 12:42:56 AM | MII180700001 | PROMEDIA TRADING PRIVATE LIMITED | 2198105 | DFSU1699560  |   20 | GB   |        |        | 6-29-2018 12:10:52 AM | AACCT8966D    | T S LINES AGENCY            | PROMEDIATRADINGPRIVAT | Handling and  PNR Movement Charges    |   7400 |          |
  | |    9 | 7-1-2018 12:42:56 AM | MII180700001 | PROMEDIA TRADING PRIVATE LIMITED | 2198105 | DFSU1699560  |   20 | GB   |        |        | 6-29-2018 12:10:52 AM | AACCT8966D    | T S LINES AGENCY            | PROMEDIATRADINGPRIVAT | Insurance Charges                    |    300 |           |
  | |    10 | 7-1-2018 12:42:56 AM | MII180700001 | PROMEDIA TRADING PRIVATE LIMITED | 2198105 | DFSU1699560  |   20 | GB   |        |        | 6-29-2018 12:10:52 AM | AACCT8966D    | T S LINES AGENCY            | PROMEDIATRADINGPRIVAT | Weighment Charges                    |    200 |          |
  | |    11 | 7-1-2018 12:42:56 AM | MII180700001 | PROMEDIA TRADING PRIVATE LIMITED | 2198105 | DFSU1696812  |   20 | GB   |        |        | 6-28-2018 9:32:02 PM  | AACCT8966D    | T S LINES AGENCY            | PROMEDIATRADINGPRIVAT | Container Movement and Increase Charges |   1800 |       |
  | |    12 | 7-1-2018 12:42:56 AM | MII180700001 | PROMEDIA TRADING PRIVATE LIMITED | 2198105 | DFSU1696812  |   20 | GB   |        |        | 6-28-2018 9:32:02 PM  | AACCT8966D    | T S LINES AGENCY            | PROMEDIATRADINGPRIVAT | Container Tracking Charges            |    100 |         |
  | |    13 | 7-1-2018 12:42:56 AM | MII180700001 | PROMEDIA TRADING PRIVATE LIMITED | 2198105 | DFSU1696812  |   20 | GB   |        |        | 6-28-2018 9:32:02 PM  | AACCT8966D    | T S LINES AGENCY            | PROMEDIATRADINGPRIVAT | Documentation Charges                |    250 |          |
  | |    14 | 7-1-2018 12:42:56 AM | MII180700001 | PROMEDIA TRADING PRIVATE LIMITED | 2198105 | DFSU1696812  |   20 | GB   |        |        | 6-28-2018 9:32:02 PM  | AACCT8966D    | T S LINES AGENCY            | PROMEDIATRADINGPRIVAT | Seal Charges                            |    0 |         |
  | |    15 | 7-1-2018 12:42:56 AM | MII180700001 | PROMEDIA TRADING PRIVATE LIMITED | 2198105 | DFSU1699560  |   20 | GB   |        |        | 6-29-2018 12:10:52 AM | AACCT8966D    | T S LINES AGENCY            | PROMEDIATRADINGPRIVAT | Container Tracking Charges            |    100 |         |
  | |    16 | 7-1-2018 12:42:56 AM | MII180700001 | PROMEDIA TRADING PRIVATE LIMITED | 2198105 | DFSU1699560  |   20 | GB   |        |        | 6-29-2018 12:10:52 AM | AACCT8966D    | T S LINES AGENCY            | PROMEDIATRADINGPRIVAT | Seal Charges                            |    0 |         |
  | |    17 | 7-1-2018 12:42:56 AM | MII180700001 | PROMEDIA TRADING PRIVATE LIMITED | 2198105 | DFSU1696812  |   20 | GB   |        |        | 6-28-2018 9:32:02 PM  | AACCT8966D    | T S LINES AGENCY            | PROMEDIATRADINGPRIVAT | Insurance Charges                    |    300 |          |
  | |    18 | 7-1-2018 12:50:13 AM | MII180700002 | TAEWOONG LOGISTICS PVT LTD    | 2198466 | CNSU2012015  |   20 | GB   |        |        | 6-29-2018 7:40:06 AM  | AABCH5572J    | HYUNDAI MERCHANT MARINE LINE | TAEWOONGLOGISTICSPVTL | Container Tracking Charges            |    100 |           |
  | |    19 | 7-1-2018 12:50:13 AM | MII180700002 | TAEWOONG LOGISTICS PVT LTD    | 2198466 | CNSU2012015  |   20 | GB   |        |        | 6-29-2018 7:40:06 AM  | AABCH5572J    | HYUNDAI MERCHANT MARINE LINE | TAEWOONGLOGISTICSPVTL | Seal Charges                            |    0 |           |
  | |    20 | 7-1-2018 12:50:13 AM | MII180700002 | TAEWOONG LOGISTICS PVT LTD    | 2198466 | CNSU2012015  |   20 | GB   |        |        | 6-29-2018 7:40:06 AM  | AABCH5572J    | HYUNDAI MERCHANT MARINE LINE | TAEWOONGLOGISTICSPVTL | Value Added Charges                    |   1000 |          |
  | |    21 | 7-1-2018 12:50:13 AM | MII180700002 | TAEWOONG LOGISTICS PVT LTD    | 2198466 | CNSU2012015  |   20 | GB   |        |        | 6-29-2018 7:40:06 AM  | AABCH5572J    | HYUNDAI MERCHANT MARINE LINE | TAEWOONGLOGISTICSPVTL | Handling and  PNR Movement Charges    |   6850 |           |
  | |    22 | 7-1-2018 1:55:51 AM  | MII180700003 | PROMEDIA TRADING PRIVATE LIMITED | 2198105 | FCIU4016495  |   20 | GB   |        |        | 6-29-2018 11:35:29 AM | AACCT8966D    | T S LINES AGENCY            | PROMEDIATRADINGPRIVAT | Container Movement and Increase Charges |   1800 |       |
  | |    23 | 7-1-2018 1:55:51 AM  | MII180700003 | PROMEDIA TRADING PRIVATE LIMITED | 2198105 | FCIU4016495  |   20 | GB   |        |        | 6-29-2018 11:35:29 AM | AACCT8966D    | T S LINES AGENCY            | PROMEDIATRADINGPRIVAT | Fuel Charges                            |    850 |       |
  | |    24 | 7-1-2018 1:55:51 AM  | MII180700003 | PROMEDIA TRADING PRIVATE LIMITED | 2198105 | FCIU4016495  |   20 | GB   |        |        | 6-29-2018 11:35:29 AM | AACCT8966D    | T S LINES AGENCY            | PROMEDIATRADINGPRIVAT | Insurance Charges                    |    300 |          |
  | |    25 | 7-1-2018 1:55:51 AM  | MII180700003 | PROMEDIA TRADING PRIVATE LIMITED | 2198105 | TEMU5684719  |   20 | GB   |        |        | 6-29-2018 1:00:46 AM  | AACCT8966D    | T S LINES AGENCY            | PROMEDIATRADINGPRIVAT | Container Movement and Increase Charges |   1800 |       |
  | |    26 | 7-1-2018 1:55:51 AM  | MII180700003 | PROMEDIA TRADING PRIVATE LIMITED | 2198105 | TEMU5684719  |   20 | GB   |        |        | 6-29-2018 1:00:46 AM  | AACCT8966D    | T S LINES AGENCY            | PROMEDIATRADINGPRIVAT | Container Tracking Charges            |    100 |         |
  | |    27 | 7-1-2018 1:55:51 AM  | MII180700003 | PROMEDIA TRADING PRIVATE LIMITED | 2198105 | TEMU5684719  |   20 | GB   |        |        | 6-29-2018 1:00:46 AM  | AACCT8966D    | T S LINES AGENCY            | PROMEDIATRADINGPRIVAT | Handling and  PNR Movement Charges    |   7400 |         |
  | |    28 | 7-1-2018 1:55:51 AM  | MII180700003 | PROMEDIA TRADING PRIVATE LIMITED | 2198105 | TEMU5684719  |   20 | GB   |        |        | 6-29-2018 1:00:46 AM  | AACCT8966D    | T S LINES AGENCY            | PROMEDIATRADINGPRIVAT | Insurance Charges                    |    300 |          |
  | |    29 | 7-1-2018 1:55:51 AM  | MII180700003 | PROMEDIA TRADING PRIVATE LIMITED | 2198105 | TEMU5684719  |   20 | GB   |        |        | 6-29-2018 1:00:46 AM  | AACCT8966D    | T S LINES AGENCY            | PROMEDIATRADINGPRIVAT | Seal Charges                            |    0 |         |
  | |    30 | 7-1-2018 1:55:51 AM  | MII180700003 | PROMEDIA TRADING PRIVATE LIMITED | 2198105 | FCIU4016495  |   20 | GB   |        |        | 6-29-2018 11:35:29 AM | AACCT8966D    | T S LINES AGENCY            | PROMEDIATRADINGPRIVAT | Documentation Charges                |    250 |          |
  | |    31 | 7-1-2018 1:55:51 AM  | MII180700003 | PROMEDIA TRADING PRIVATE LIMITED | 2198105 | FCIU4016495  |   20 | GB   |        |        | 6-29-2018 11:35:29 AM | AACCT8966D    | T S LINES AGENCY            | PROMEDIATRADINGPRIVAT | Handling and  PNR Movement Charges    |   7400 |         |
  | +--------+----------------------+--------------+----------------------------------+---------+--------------+------+------+---------+---------+-----------------------+-----------------+------------------------------+-----------------------+-----------------------------------------+--------+ |
  +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  </code></pre>
  
  <p>I need to pivot <strong>activity description</strong> columns which can be added dynamically into my FCL_Parent table.</p>
  
  <p>So In order to get values dynamically from that column, I used below code,</p>
  
  <pre><code> DECLARE @Columns VARCHAR(MAX) = ''
   SET @Columns = (SELECT DISTINCT Quotename([Activity Description]) + ',' 
               FROM  [dbo].[FCLParent]  
               FOR xml path('')) 
   SET @Columns = LEFT (@Columns, Len(@Columns) - 1)  
  </code></pre>
  
  <p>Below is my requirement,</p>
  
  <p><strong>I need distinct container number, with the activity description as pivot columns and amount column as values in pivot and my other required columns too.</strong> </p>
  
  <p><a href="https://i.stack.imgur.com/s6Q6e.png" rel="nofollow noreferrer">Output</a></p>
  
  <p>In order to achieve this I used below code,</p>
  
  <pre><code>  SET @sql = 'select [Payer Name],[Container No],[Size],[Type],
    [CHAName],[Act Gatein Date], [Container  Agent  Name],[Importer Name],'         + @Columns 
     + 'FROM [dbo].[FCLParent]
     pivot (max([Amount]) for [Activity Description] in (' 
         + @Columns + '))pv' 
     EXEC(@sql)
  </code></pre>
  
  <p>I am not getting the expected pivot columns. some values are missing in it. Ex. Some charges available are missing in the pivot. I'm also not sure whether the query is right or wrong. </p>
  
  <p>Thanks</p>