PIVOT relational operator can be used  to change a table-valued expression into another table. PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output, and performs aggregations where they are required on any remaining column values that are wanted in the final output.

Official syntax:

SELECT <non-pivoted column>,  
    [first pivoted column] AS <column name>,  
    [second pivoted column] AS <column name>,  
    ...  
    [last pivoted column] AS <column name>  
FROM  
    (<SELECT query that produces the data>)   
    AS <alias for the source query>  
PIVOT  
(  
    <aggregation function>(<column being aggregated>)  
FOR   
[<column that contains the values that will become column headers>]   
    IN ( [first pivoted column], [second pivoted column],  
    ... [last pivoted column])  
) AS <alias for the pivot table>  
<optional ORDER BY clause>;

Example:
(produce column output)
select vendorid,incomeday,avg(incomeamount/1.0) from dailyincome group by vendorid,incomeday order by vendorid,incomeday

(produce tabular output)
select * from dailyincome
pivot(
    avg(incomeamount) for incomeday in([MON],[TUE],[wed],[thu],[fri],[sat],[sun])) as avgimcomeperday 


Here is the table structure:

create table DailyIncome(VendorId nvarchar(10), IncomeDay nvarchar(10), IncomeAmount int)

insert into DailyIncome values ('X1', 'FRI', 100)

insert into DailyIncome values ('X2', 'MON', 300)

insert into DailyIncome values ('X3', 'SUN', 400)
Advertisements