Saturday, 16 December 2017

How to Transform Rows into Columns from a Table in SQL Server

How to Pivot the Data using various methods in SQL Server
In the Field of Business Intelligence, Our aim is to enable the Business users with an understanding of data created and stored by business systems. We can do this by providing the Insights to the Business users to know how the business is performing.

Sample Table : Select * From Tbl_Sample



A Tabular report may not provide the correct Insights to the Business for Analysis, so that we need to transform that data into a Pivot or Cross Tab format.
We can do this using various options in SQL Server.

1) Using T-SQL Pivot Operator :

SELECT *FROM
   (
   SELECT
          [RegionName],
          [ProdName],      
          [FiscalQtr],
          [NetSales]
   FROM [dbo].[Tbl_Sample]
   ) AS PivotSource
   PIVOT(SUM([NetSales]) FOR [FiscalQtr] IN([Q1-2016],
                                                         [Q2-2016],
                                                         [Q3-2016],
                                                         [Q4-2016])) AS PivotTable
GO

Result :

2) Using XML PATH Option :
DECLARE @vColumns NVARCHAR(MAX), @vQuery NVARCHAR(MAX);

SET @vColumns = STUFF(
          (
         SELECT DISTINCT ','+QUOTENAME(S1.[FiscalQtr])
         FROM [dbo].[Tbl_Sample]
S1
         FOR XML PATH(''), TYPE).value('.', 'nvarchar(Max)'),
        1, 1, '');

SET @vQuery = 'SELECT [RegionName],[ProdName],'+@vColumns+' From (
       SELECT [RegionName],[ProdName],[FiscalQtr],[NetSales]
       FROM [dbo].[Tbl_Sample]
      ) PivotSource PIVOT (Max(NetSales) for [FiscalQtr] in ('+@vColumns+')) P';

EXECUTE (@vQuery);
GO

Result :



3) Using Dynamic SQL Query:
DECLARE @vColumns NVARCHAR(MAX), @vSQL NVARCHAR(MAX);
SET @vColumns = '';

SELECT @vColumns+=',P.'+QUOTENAME([FiscalQtr]) FROM
(
    SELECT [FiscalQtr]
    FROM [dbo].[Tbl_Sample] as S2
    GROUP BY [FiscalQtr]

) as S1;

SET @vSQL ='SELECT [RegionName],[ProdName],'+STUFF(@vColumns, 1, 1, '')+
' FROM
(
   SELECT [RegionName],[ProdName],[FiscalQtr],[NetSales]
   FROM [dbo].[Tbl_Sample]) AS PivotSource PIVOT (SUM([NetSales]) FOR [FiscalQtr] IN
    ('+STUFF(REPLACE(@vColumns, ',P.[', ',['), 1, 1, '')+')) AS P;';
Print (@vColumns)
Print (@vSQL)
EXEC sp_executesql @vSQL
GO

Result :


-----------------------------------------------------------------------------------------------------------
STRING Functions :
QUOTENAME :
The QuoteName is String Function which is used to Enclose a String with some
Quote( Eg : ,,'',{},[],<>)
Eg :
Declare @String varchar(30) = 'Sample String'

Select 'Default' as QuoteType , QUOTENAME(@String) QuotedValue
Union All Select 'Tag', QUOTENAME(@String,'<')
Union All Select 'Parentheses'  , QUOTENAME(@String,'(')
Union All Select 'CurlyBraces' , QUOTENAME(@String,'{')
Union All Select 'Brackets' , QUOTENAME(@String,'[')
Union All Select 'Tick/SingleQuotes' , QUOTENAME(@String,'''')
Union All Select 'Dot' , QUOTENAME(@String,'.')

GO

Result :
-------------------------------------------------------
REPLACE :
The REPLACE is a string Function which Replaces the each Occurrence of a string from the Main String with a new string.
Eg :
Declare @String varchar(30) = 'Some#*Sample*String'
Select REPLACE (@String,'S','abc') Replaced_String
Result :
Replaced_String
abcome#*abcample*abctring
-------------------------------------------------------
STUFF :
The STUFF is a string Function which Replaces the part of a string from the Main String with a new string, based on the Starting Position and No.of Characters to Replace.
Eg :
Declare @String varchar(30) = 'Some#*Sample*String'
Select STUFF (@String,7,3,'Dim') Stuffed_String
Result :
Stuffed_String

Some#*Dimple*String
------------------------------------------------------
CHAR INDEX :
The CHAR INDEX is a string Function which returns the Position of a SubString in the Main String based on the Starting Postion.
Eg :
Declare @String varchar(30) = 'Some#*Sample*String'
Select CHARINDEX('S',@String,3) Char_Index
Result :
Char_Index
      7
------------------------------------------------------
SUBSTRING :
The SUBSTRING is a string Function which extracts the Portion of a string from the Main String based on the Starting Position and No.of Characters to Extract.
Eg :
Declare @String varchar(30) = 'Some#*Sample*String'
Select SUBSTRING(@String,7,3) Sub_String
Result :
Sub_String
    Sam
-----------------------------------------------------------------------------------------------------------
Note :
Simple Example to Transpose Data:
 OrderDate      Segment         NetBookings
2017-12-16   Commercial        4500
2016-12-16   Commercial        5500

Transposing Data :
Select [Segment],[2017-12-16] as [CurFY],[2016-12-16] as [PrevFY]
From DimTablePivot (Sum( [NetBookings] ) 
For [FiscalYear] IN ( [2017-12-16] ,[2016-12-16] )) AS P

Result :
Segment        CurFY  PrevFY
Commercial     4500    5500


--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence Professional
--------------------------------------------------------------------------------------------------------

1 comment:

  1. Simple Example :
    OrderDate Segment NetBookings
    2017-12-16 Commercial 4500
    2016-12-16 Commercial 5500

    Transposing Data :
    Select [Segment],[2017-12-16] as [CurFY],[2016-12-16] as [PrevFY]
    From DimTable
    Pivot (Sum( [NetBookings] )
    For [FiscalYear] IN ( [2017-12-16] ,[2016-12-16] )
    ) AS P

    Result :
    Segment CurFY PrevFY
    Commercial 4500 5500

    ReplyDelete

Hi User, Thank You for Visiting My Blog. Please Post Your Feedback/Comments/Query.

Subscribe to Blog Posts by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts