sql-server add tag
user16947 (imported from SE)

My table is like this:

    Sitecode    Month  Amount
    --------    -----  ------
    XX          Jan     1000
    XX          Jan     3000
    XX          Apr     3000
    XX          Apr     1000

What I want is, to show the result something like this:

    Sitecode    MonthJAN   MonthAPR
    --------    --------   --------
    XX          4000       4000
Top Answer
taryn (imported from SE)
As others have said this is known as a [`PIVOT`][1]. There are several ways in which you can transform your data from rows into columns of data. 

If you know the values ahead of time, then you can hard-code the values. Prior to the `PIVOT` function you would use an aggregate function with a `CASE` statement.

**Aggregate/CASE Version:**

    select sitecode,
      sum(case when [month] = 'Jan' then amount else 0 end) MonthJan,
      sum(case when [month] = 'Apr' then amount else 0 end) MonthApr
    from yourtable
    group by sitecode;

See [SQL Fiddle with Demo][2].

The `PIVOT` function was made available in SQL Server 2005, so if you are using that version or a newer one then you can apply that to your data.

**Static PIVOT:** 

    select *
      select sitecode,
      from yourtable
    ) src
      for month in (Jan, Apr)
    ) piv;

See [SQL Fiddle with Demo][3]

The above two versions work great if you know the values ahead of time. If not, then you will use dynamic sql to create the result. 

**Dynamic PIVOT:**

        @query  AS NVARCHAR(MAX)
    select @cols = STUFF((SELECT distinct ',' + QUOTENAME(month) 
                        from yourtable
                FOR XML PATH(''), TYPE
                ).value('.', 'NVARCHAR(MAX)') 
    set @query = 'SELECT sitecode,' + @cols + ' from 
                    select sitecode, [month], amount
                    from yourtable
                ) x
                    for month in (' + @cols + ')
                ) p '

See [SQL Fiddle with Demo][4]

All 3 versions of this will return the same result:

    | SITECODE |  JAN |  APR |
    |       XX | 4000 | 4000 |

  [1]: http://msdn.microsoft.com/en-us/library/ms177410%28v=sql.105%29.aspx
  [2]: http://sqlfiddle.com/#!3/73b2f/4
  [3]: http://sqlfiddle.com/#!3/07d60/2
  [4]: http://sqlfiddle.com/#!3/07d60/1

This room is for discussion about this question.

Once logged in you can direct comments to any contributor here.

Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.