Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Towards the innovative SQL ideas
Browse by Tags · View All
sql server 125
sql 124
ms sql server 119
ms sql 118
database 109
tsql 81
#SQL Server 78
t-sql 75
#sql 71
sql server general 67

Archive · View All
April 2011 14
July 2011 12
May 2011 12
August 2011 11
June 2011 10
September 2011 8
December 2011 6
November 2011 6
September 2013 5
June 2013 5

SQL Server - Changing Rows to Columns Using PIVOT

Feb 18 2012 12:00AM by Paresh Prajapati   

During working with one logic, i got chance to work with PIVOT operation. Sometime we need do require rowdata as column in our custom logic, then we can use some temp table and then populate aggregate data in temp table. But With PIVOT we can do it very easily. Let me prepare small example and explain as how how can we use PIVOT and get row data as column.

Before go ahead to run the script of Pivot, we will create database and table objects.
CREATE DATABASE DEMO
GO

USE DEMO
GO

-- Creating table for demo
IF (object_id('TblPivot','U') > 0)
DROP TABLE TblPivot

CREATE TABLE TblPivot
(
ItemCode int,
ItemName varchar(100),
ItemColour varchar(50)
)

GO

-- Inerting some sample records

INSERT INTO TblPivot
SELECT 1,'Samsung Mobile','Red'
UNION ALL
SELECT 2,'Nokia Mobile','Blue'
UNION ALL
SELECT 3,'Nokia Mobile','Green'
UNION ALL
SELECT 4,'Motorola Mobile','Red'
UNION ALL
SELECT 5,'Samsung Mobile','Green'
UNION ALL
SELECT 2,'Nokia Mobile','Blue'
UNION ALL
SELECT 1,'Samsung Mobile','Red'
UNION ALL
SELECT 2,'Nokia Mobile','Blue'

GO

Now we will check the original table data and aggregated data using Pivot. So we will run both script for the same.
-- Getting table data
SELECT 
ItemCode, 
ItemName, 
ItemColour
from TblPivot
GO

-- Getting agreegated data using Pivot and converted rows to column
SELECT
*
 FROM
 (
    SELECT 
 ItemCode, 
 ItemName, 
 ItemColour
    FROM TblPivot
 ) AS P
PIVOT
(
  Count(ItemName) FOR ItemColour IN ([Red], [Blue], [Green])
) AS pv

GO

You can review here and see how the PIVOT is working. Let me share your experience with PIVOT operation.

Tags: sql, sql server 2008, sql server 2005, tsql, sql server, ms sql, ms sql server, t-sql, sql server denali, #SQL Server, #sql, sql server 2011, database, sql server general, SQL Scripts, pivot,


Paresh Prajapati
6 · 23% · 7485
9
 
0
Lifesaver
 
 
0
Learned
 
0
Incorrect



Submit

2  Comments  

  • This only works if the only valid values in your table are [Red], [Blue] and [Green]. As soon as you add [Yellow], it breaks. The problem with PIVOT is that you have to know the values beforehand and hard-code them into the query.

    commented on Feb 25 2012 8:57AM
    Marc Jellinek
    95 · 2% · 586

Your Comment


Sign Up or Login to post a comment.

"SQL Server - Changing Rows to Columns Using PIVOT" rated 5 out of 5 by 9 readers
SQL Server - Changing Rows to Columns Using PIVOT , 5.0 out of 5 based on 9 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]