Mahmoud ElMansy: knowledge meant to be free

The Magic of T-SQL Pivot

Pivot is commonly used in reports to enhance the view for the selected data and make better format for the selected data
Now Let us discus it in more details
The syntax for PIVOT as in Microsoft MSDN

SELECT <non-pivoted column>,
[FIRST pivoted COLUMN] AS <COLUMN name>,
[SECOND pivoted COLUMN] AS <COLUMN name>,
...
[LAST pivoted COLUMN] AS <COLUMN name>
FROM
(<SELECT query that produces the data>)
AS <alias FOR the SOURCE query>
PIVOT
(
<aggregation function>(<COLUMN being aggregated>)
FOR
[<COLUMN that contains the VALUES that will become COLUMN headers>]
IN ( [FIRST pivoted COLUMN], [SECOND pivoted COLUMN],
... [LAST pivoted COLUMN])
) AS <alias FOR the pivot table>
<optional ORDER BY clause>;

Now we will talk about how and when to use it
First, Pivot is used with aggregate functions
Pivot uses an existing column in the select statement and instead of showing it in the selected row it show it as table column like


Example: Create a test table

USE [Test]
GO
CREATE TABLE [dbo].[Items](
    [ID] [INT] IDENTITY(1,1) NOT NULL,
    [ItemName] [VARCHAR](256) NULL,
 CONSTRAINT [PK_Items] PRIMARY KEY CLUSTERED
(
    [ID] ASC
)
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[InvoiceItems](
    [ID] [INT] IDENTITY(1,1) NOT NULL,
    [Item_ID] [INT] NULL,
    [Casher] [VARCHAR](64) NULL,
    [Amount] [INT] NULL,
    [Price] [INT] NULL,
    [InvoiceDate] [DATE] NULL,
 CONSTRAINT [PK_InvoiceItems] PRIMARY KEY CLUSTERED
(
    [ID] ASC
)
) ON [PRIMARY]
GO

Data insertion

--|--------------------------------------------------------------------------------
--| [InvoiceItems] -
--|--------------------------------------------------------------------------------

--|--------------------------------------------------------------------------------
--| [InvoiceItems] - Backs up all the data from a table into a SQL script.
--|--------------------------------------------------------------------------------
BEGIN TRANSACTION
    SET IDENTITY_INSERT [InvoiceItems] ON

    INSERT INTO [InvoiceItems]
    ([ID], [Item_ID], [Casher], [Amount], [Price], [InvoiceDate])
    VALUES
    (1, 1, 'Casher_1', 1, 3, 2011-05-06);

    INSERT INTO [InvoiceItems]
    ([ID], [Item_ID], [Casher], [Amount], [Price], [InvoiceDate])
    VALUES
    (2, 2, 'Casher_2', 5, 15, 2011-05-06);

    INSERT INTO [InvoiceItems]
    ([ID], [Item_ID], [Casher], [Amount], [Price], [InvoiceDate])
    VALUES
    (3, 3, 'Casher_3', 10, 30, 2011-05-06);

    INSERT INTO [InvoiceItems]
    ([ID], [Item_ID], [Casher], [Amount], [Price], [InvoiceDate])
    VALUES
    (4, 1, 'Casher_1', 1, 3, 2011-05-01);

    INSERT INTO [InvoiceItems]
    ([ID], [Item_ID], [Casher], [Amount], [Price], [InvoiceDate])
    VALUES
    (5, 2, 'Casher_2', 5, 15, 2011-05-01);

    INSERT INTO [InvoiceItems]
    ([ID], [Item_ID], [Casher], [Amount], [Price], [InvoiceDate])
    VALUES
    (6, 1, 'Casher_1', 10, 30, 2011-05-11);

    INSERT INTO [InvoiceItems]
    ([ID], [Item_ID], [Casher], [Amount], [Price], [InvoiceDate])
    VALUES
    (7, 3, 'Casher_3', 1, 3, 2011-05-11);

    INSERT INTO [InvoiceItems]
    ([ID], [Item_ID], [Casher], [Amount], [Price], [InvoiceDate])
    VALUES
    (8, 2, 'Casher_1', 5, 15, 2011-05-28);

    INSERT INTO [InvoiceItems]
    ([ID], [Item_ID], [Casher], [Amount], [Price], [InvoiceDate])
    VALUES
    (9, 3, 'Casher_3', 10, 30, 2011-05-28);

    INSERT INTO [InvoiceItems]
    ([ID], [Item_ID], [Casher], [Amount], [Price], [InvoiceDate])
    VALUES
    (10, 1, 'Casher_1', 1, 3, 2011-05-15);

    INSERT INTO [InvoiceItems]
    ([ID], [Item_ID], [Casher], [Amount], [Price], [InvoiceDate])
    VALUES
    (11, 2, 'Casher_2', 5, 15, 2011-05-17);

    INSERT INTO [InvoiceItems]
    ([ID], [Item_ID], [Casher], [Amount], [Price], [InvoiceDate])
    VALUES
    (12, 3, 'Casher_3', 10, 30, 2011-05-23);
    SET IDENTITY_INSERT [InvoiceItems] OFF

IF @@ERROR <> 0 ROLLBACK TRANSACTION;
ELSE COMMIT TRANSACTION;
GO
--|--------------------------------------------------------------------------------

--|--------------------------------------------------------------------------------
--| [Items] - Backs up all the data from a table into a SQL script.
--|--------------------------------------------------------------------------------
BEGIN TRANSACTION
    SET IDENTITY_INSERT [Items] ON

    INSERT INTO [Items]
    ([ID], [ItemName])
    VALUES
    (1, 'Item_1');

    INSERT INTO [Items]
    ([ID], [ItemName])
    VALUES
    (2, 'Item_2');

    INSERT INTO [Items]
    ([ID], [ItemName])
    VALUES
    (3, 'Item_3');
    SET IDENTITY_INSERT [Items] OFF

IF @@ERROR <> 0 ROLLBACK TRANSACTION;
ELSE COMMIT TRANSACTION;
GO
--|--------------------------------------------------------------------------------

Let us begin with our example
Select data from table

SELECT it.ItemName,inv.Casher,inv.Amount  FROM  dbo.InvoiceItems inv
INNER JOIN dbo.Items it ON inv.Item_ID=it.ID


simple select statement gets item name ,cashier and amount
this view is not the best practice hard to see and we want to add some business to it
now if I asked u that I want to view this data for all cashiers and sum the amount for each item what will u do? it is hard question but it can by easily done by pivot
sql statement would be like this

SELECT * FROM
(
SELECT it.ItemName,inv.Casher,inv.Amount  FROM  dbo.InvoiceItems inv INNER JOIN dbo.Items it
ON inv.Item_ID=it.ID
)  OrgTable
PIVOT
(
SUM(Amount)
FOR Casher IN ([Casher_1],[Casher_2],[Casher_3])
)PivotTable


We can represent it as
ItemName Casher_1 Casher_2 Casher_3
Item_1 Sum(amount) for all item_1 seleted in the OrgTable for Casher_1 Sum(amount) for all item_1 seleted in the OrgTable for Casher_2 Sum(amount) for all item_1 seleted in the OrgTable for Casher_3
Itenm_2 Sum(amount) for all item_2 seleted in the OrgTable for Casher_1 Sum(amount) for all item_1 seleted in the OrgTable for Casher_2 Sum(amount) for all item_2 seleted in the OrgTable for Casher_3
Item_3 Sum(amount) for all item_3 seleted in the OrgTable for Casher_1 Sum(amount) for all item_1 seleted in the OrgTable for Casher_2 Sum(amount) for all item_3 seleted in the OrgTable for Casher_3

Now here is some tricks
If you don’t need the Null value that appears in the nonexisting

SELECT ItemName,isnull([Casher_1],0) [Casher_1] ,isnull([Casher_2],0) [Casher_2],isnull([Casher_3],0) [Casher_2] FROM
(
SELECT it.ItemName,inv.Casher,inv.Amount  FROM  dbo.InvoiceItems inv INNER JOIN dbo.Items it
ON inv.Item_ID=it.ID
)  OrgTable
PIVOT
(
SUM(Amount)
FOR Casher IN ([Casher_1],[Casher_2],[Casher_3])
)PivotTable

Dynamic PIVOT – PIVOT without specifying columns
How to Make Dynamic Pivot?

DECLARE @COLUMNS VARCHAR(8000)
SELECT @COLUMNS = COALESCE(@COLUMNS + ',[' + CAST(Casher AS VARCHAR) + ']',
'[' + CAST(Casher AS VARCHAR)+ ']')
FROM dbo.InvoiceItems
GROUP BY Casher
 SELECT @COLUMNS
DECLARE @query VARCHAR(8000)
SET @query = '
select * from
(
select it.ItemName,inv.Casher,inv.Amount  from  dbo.InvoiceItems inv inner join dbo.Items it
on inv.Item_ID=it.ID
)  OrgTable
PIVOT
(
sum(Amount)
for Casher in ('
+@COLUMNS+')
)PivotTable
'

 EXECUTE (@query)