Last time I have written
about pivoting and the next after that post describes for dynamic
columns which used for pivoting. We have seen the example and the same
example implemented with dynamic columns. You can read both posts which
are following.
1. SQL Server - Changing Rows to Columns Using PIVOT
2. Changing Rows to Columns Using PIVOT - Dynamic columns for Pivoting in SQL Server
In an earlier post I have applied pivoting on one column name ItemColour but here I would like to introduce pivoting on more than one column. So let us jump on example and implement it for multiple columns. Here below you can find the script to create the required objects for demo.
These are the just sample records in
the demo. Now here we are applying pivoting on multiple columns named
ItemColour and ItemName. Following queries you can use for the same. So
let’s run it and check result set.
1. SQL Server - Changing Rows to Columns Using PIVOT
2. Changing Rows to Columns Using PIVOT - Dynamic columns for Pivoting in SQL Server
In an earlier post I have applied pivoting on one column name ItemColour but here I would like to introduce pivoting on more than one column. So let us jump on example and implement it for multiple columns. Here below you can find the script to create the required objects for demo.
-- Creating table for demo IF (object_id('TblPivot','U') > 0) DROP TABLE TblPivot CREATE TABLE TblPivot ( Seq int not null identity(1,1), ItemCode int, ItemModel varchar(25), ItemName varchar(100), ItemColour varchar(50) ) GO -- Inerting some sample records INSERT INTO TblPivot ( ItemCode, ItemModel, ItemName, ItemColour ) SELECT 1,'S1024','Samsung Mobile','Red' UNION ALL SELECT 2,'N1465','Nokia Mobile','Blue' UNION ALL SELECT 3,'N1689','Nokia Mobile','Green' UNION ALL SELECT 4,'M1642','Motorola Mobile','Red' UNION ALL SELECT 5,'S2358','Samsung Mobile','Green' UNION ALL SELECT 2,'N2376','Nokia Mobile','Blue' UNION ALL SELECT 1,'S3245','Samsung Mobile','Red' UNION ALL SELECT 2,'N3421','Nokia Mobile','Blue' GO
SELECT Seq, ItemCode, ItemModel, ItemColour, ItemName FROM TblPivot -- Applying pivoting on multiple columns SELECT * FROM ( SELECT Seq, ItemCode, ItemModel, ItemName, ItemColour FROM TblPivot ) AS P -- For ItemColour PIVOT ( Count(ItemCode) FOR ItemColour IN ([Red], [Blue], [Green]) ) AS pv1 -- For ItemName PIVOT ( Count(ItemModel) FOR ItemName IN ([Samsung Mobile], [Nokia Mobile], [Motorola Mobile]) ) AS pv2 GO
You can also implement the above script dynamic as previous post. Let me know your comments if you have something for pivoting.
Comments
Post a Comment