Using the SQL 2005 Pivot Clause

Let’s say you are querying interview table for the interview id 488



Lets say we are only interested in these 3 disease specific fields



Lets get the interview values for the above fields along with the values from the interview table



The problem as you can see is that the interview data first 3 columns get repeated. Let’s use pivot clause to get the information in a single row.

Let’s create a Common Table Expression query to get all the data for the fields we are interested in from Interview Value table


Now lets Pivot Common Table expression table so that we can get an aggregate value like Count, sum, min, max etc., of field value for given field id.



The use of the square brackets [ ] around the column names in both the SELECT and the IN in the pivot clause is required.

These are keys, because the pivot operation is treating the values in these columns as column names.

Effectively you are grouping field values by two columns InterviewID and FieldID, then pivoting field id ie., you are treating the row values of field id as column names.

All we need to do now is to join our pivot to the interview table to get the required result.



That’s all folks!

Advertisements

About Shashi Kuppa

I have 14 years of Information System experience. My background ranges from software architecture, analysis, design and development to team leadership and project management. My primary strengths include .Net system architecture, object oriented development using C#, web development using Asp.net and SQL server development, reporting and integration. I am an excellent team player, good communicator and have a proven track record for goal-oriented project leadership and management
This entry was posted in Development, Uncategorized and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s