Tags: MSSQL

This time I decided to write informational article. This database operation used very rare in real time application however its important for us to keep our self up to date with technology. Many of you knows it very well and other don't. So this is revision for those who knows it and informational for newbie.

Note: This question was asked in interview of one of my friend. So just read it properly and may I'll help you out some day.

PIVOT
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.You can read more about on Microsoft website.

Lets come straight to the example. This is my basic table structure and its value.


We all do basic operation with simple query like below and will give you output as

SELECT
  gender,
  COUNT(*) AS 'count'
FROM
  user_master
GROUP BY
  gender

select Female as Female,Male as Male from
(select distinct gender, count(gender) as Total from user_master group by gender)as p
pivot 
(
max(Total)
for Gender in ( [Female],[Male]) 
)as pvt

Tags: MSSQL