Pivoting on multiple columns - SQL Server by serverku

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.

-- 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

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.

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.