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, 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 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 DimTable Pivot (Sum( [NetBookings] ) 
For [FiscalYear] IN ( [2017-12-16] ,[2016-12-16] )) AS P

Result :
Segment        CurFY  PrevFY
Commercial     4500    5500

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