Changing Rows to Columns Using PIVOT - SQL Server
While working with a logic, I got a chance to work on 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.
Now we will check the original table data and aggregated data using Pivot. So we will run both scripts for the same.
You can review here and see how The PIVOT is working. Let me share your experience with PIVOT operation.
Before going ahead to run the script of Pivot, we will create a database and table objects.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
| CREATE DATABASE DEMOGOUSE DEMOGO-- Creating table for demoIF (object_id('TblPivot','U') > 0)DROP TABLE TblPivotCREATE TABLE TblPivot(ItemCode int,ItemName varchar(100),ItemColour varchar(50))GO-- Inerting some sample recordsINSERT INTO TblPivotSELECT 1,'Samsung Mobile','Red'UNION ALLSELECT 2,'Nokia Mobile','Blue'UNION ALLSELECT 3,'Nokia Mobile','Green'UNION ALLSELECT 4,'Motorola Mobile','Red'UNION ALLSELECT 5,'Samsung Mobile','Green'UNION ALLSELECT 2,'Nokia Mobile','Blue'UNION ALLSELECT 1,'Samsung Mobile','Red'UNION ALLSELECT 2,'Nokia Mobile','Blue'GO |
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
| -- Getting table dataSELECTItemCode, ItemName, ItemColourfrom TblPivotGO-- Getting aggregated data using Pivot and converted rows to columnSELECT* FROM ( SELECT ItemCode, ItemName, ItemColour FROM TblPivot ) AS PPIVOT( Count(ItemName) FOR ItemColour IN (Red, Blue, Green)) AS pvGO |
You can review here and see how The PIVOT is working. Let me share your experience with PIVOT operation.
