Changing Rows to Columns Using PIVOT - Dynamic columns for Pivoting in SQL Server by serverku

We have seen example of Changing Rows to Columns Using PIVOT. Hope you liked that post and you enjoyed a lot also. As we know that we need to hard-code the values for pivoting which we have made for color values. For the values updates in table or whenever values changed in the table for the column which we use in pivot then we have to update the script as per that otherwise it won't work or do not give correct information. Also, I got the same comment from a user in the post which you can reach from the above link.

I am going to explain the same scenario and the resolution for the same, but you need to read original post properly. So requesting you to read it first if you did not read. So whenever we add new color values in the table, the same pivot query does not return information about the new values. So let us add new value and prepare the query and run.

-- Inserting one more record in existing table created in earlier post
INSERT INTO TblPivot
(
 ItemCode,
 ItemName,
 ItemColour
)
SELECT 
 6,
 'Samsung Mobile',
 'Silver'

After adding above new value, the pivot query which used in earlier post does not return right result set. Here's the solution for such type of issue we need to make the query with dynamic columns as following, We have got distinct values and embedded as comma separated using ‘[‘ and ‘]’. To make dynamic we can use COALESCE or FOR XML PATH.

DECLARE @ColourColumn varchar(200)
DECLARE @sql varchar(1000)

CREATE TABLE #Colours
(
Colour varchar(500)
)

INSERT INTO #Colours (Colour)
SELECT 
 DISTINCT '[' + ItemColour + ']' 
FROM TblPivot

-- Creating Column Names for Pivot
SELECT @ColourColumn = COALESCE(@ColourColumn + ',', '') +
Colour
FROM #Colours

/*
--OR
SET @ColourColumn = (SELECT  SUBSTRING(
 (SELECT DISTINCT ',' + Colour
  FROM #Colours
  FOR XML PATH('')),2,200000) 
 )
*/

DROP TABLE #Colours

SET @sql = 
'
 SELECT
 *
  FROM
  (
  SELECT 
   ItemCode, 
   ItemName, 
   ItemColour
  FROM TblPivot
  ) AS P
 PIVOT
 (
   Count(ItemName) FOR ItemColour IN ('+@ColourColumn+')
 ) AS pv
'
EXEC (@sql)

I want you to share your ideas for such issues which you faced and the resolutions for the same.

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.

Changing Rows to Columns Using PIVOT - SQL Server by serverku

During working with one logic, I got a chance to work with PIVOT operation. Sometime we need do require row data as a column in our custom logic, then we can use some temp table and then populate aggregate data in a 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 a column.

Before going ahead to run the script of Pivot, we will create a 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 scripts 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.