How to Pivot the Data in SQL Server

SQL Server Pivot Operator Syntax with Examples
The Pivoting is an operation that transforms the data from rows to a state of columns to pivot the data used for different reporting needs to summarize and analyze the data. The Pivot helps to you fit the certain amount of data in less space.It helps to analyze the data presented as the intersection of dimensions represented by rows and columns. 
Syntax:
SELECT <col-list>
FROM <source> PIVOT (<aggre-func>(<aggre-col>)
FOR <spreading-col> IN (<spreading-col-elements>)) as PVT

Example :
USE [TAMATAM]
GO
SELECT [Sales_Region] ,[Sales_Period],[Cust_Segment],[NetSales]
  FROM [dbo].[SalesTable]
GO

Sample Source Data used for Pivot Operation :

Sales_Region	Sales_Period	Cust_Segment	NetSales
East	201501	Automobile	7276
East	201502	Electronics	2402
East	201503	Pharma	7097
East	201504	Construction	6521
East	201505	Banking	9966
East	201506	Energy	2917
East	201507	Automobile	5220
East	201508	Electronics	6304
East	201509	Pharma	4060
East	201510	Construction	7098
East	201511	Banking	5688
East	201512	Energy	4433
West	201501	Automobile	7627
West	201502	Electronics	2340
West	201503	Pharma	3530
West	201504	Construction	5594
West	201505	Banking	8356
West	201506	Energy	7172
West	201507	Automobile	2546
West	201508	Electronics	5645
West	201509	Pharma	9049
West	201510	Construction	5443
West	201511	Banking	7313
West	201512	Energy	6694
South	201501	Automobile	5573
South	201502	Electronics	7319
South	201503	Pharma	7628
South	201504	Construction	5158
South	201505	Banking	8457
South	201506	Energy	9548
South	201507	Automobile	7640
South	201508	Electronics	2481
South	201509	Pharma	2300
South	201510	Construction	3198
South	201511	Banking	8492
South	201512	Energy	5331
North	201501	Automobile	7862
North	201502	Electronics	3915
North	201503	Pharma	1977
North	201504	Construction	9125
North	201505	Banking	7499
North	201506	Energy	6685
North	201507	Automobile	5030
North	201508	Electronics	2922
North	201509	Pharma	7804
North	201510	Construction	4147
North	201511	Banking	5375
North	201512	Energy	4471

Pivot model-1: [NetSales] summary by [Cust_Segment] for [Sales_Region]:

SELECT [Cust_Segment],[East],[West],[South],[North]

 FROM

    (    SELECT [Sales_Region] ,[Cust_Segment],[NetSales] 

           FROM [dbo].[SalesTable] 

    ) T1

PIVOT ( SUM(NetSales) 

        FOR [Sales_Region] IN ( "East","West", "South","North")

        ) as PVT 

Order by [Cust_Segment]

Go

------------------------------------- OR -----------------------------

SELECT [Cust_Segment],

   SUM([East]) [East], SUM([West]) [West], SUM([South]) [South], 

    SUM([North]) [North]

FROM (

            SELECT [Sales_Region] ,[Cust_Segment],[NetSales] 

            FROM [dbo].[SalesTable] 

            ) T1

PIVOT ( SUM (NetSales) 

    FOR [Sales_Region] IN ( "East","West", "South","North")

    ) as PVT

Group by [Cust_Segment]

Go

Output:

Pivot model-2: [NetSales] summary by [Cust_Segment] for [Sales_Period]:

SELECT [Cust_Segment],

      SUM([201501])[201501],SUM([201502]) [201502],SUM([201503]) [201503],

        SUM([201504]) [201504],

      SUM([201505])[201505],SUM([201506]) [201506],SUM([201507]) [201507],

        SUM([201508]) [201508],

      SUM([201509])[201509],SUM([201510]) [201510],SUM([201511]) [201511],

        SUM([201512]) [201512]

  FROM(

                SELECT [Cust_Segment],[Sales_Period],[NetSales] 

                FROM [dbo].[SalesTable] 

            ) T1

PIVOT ( SUM(NetSales) 

    FOR [Sales_Period] IN ([201501],[201502],[201503],[201504],[201505],[201506], 

                [201507], [201508], [201509], [201510], [201511], [201512])

    ) as PVT 

Group by [Cust_Segment]

Go

Output :

-------------------------------------------------------------------------------------------------------- 

Thanks, TAMATAM ; Business Intelligence & Analytics Professional 

--------------------------------------------------------------------------------------------------------

How to Concatenate Multiple Rows within a Single Row in SQL Server

SQL Server STUFF ()  function and  FOR XML PATH () clause to Concatenate multiple Rows in a single Row
In SQL Server, we can concatenate Multiple rows within a single row as a Text using the STUFF ()  function and For XML PATH () clause as follows : 

Select DISTINCT [Dept_Id],[Dept_Name],SUM(NetSales)[NetSales],
STUFF(
(SELECT ';'+[Emp_Name] AS [text()] from [Sales_Dtls] T2
WHERE T1.Dept_Id=T2.Dept_Id
FOR XML PATH(''))
,1,1,'' ) [Sales_Team]
FROM [Sales_Dtls] T1 Group by [Dept_Id],Dept_Name

Example :
Suppose we have a Sales Table data as follows :
USE [TAMATAM]
GO

SELECT [Emp_Id]
      ,[Emp_Name]
      ,[Gender]
      ,[Job_Id]
      ,[Dept_Id]
      ,[Dept_Name]
  [NetSales]
  FROM [dbo].[Sales_Dtls]

GO

From the above Sales table if you want to see the Concatenated list of all the Sales people by Department, who generated that Sales, we do it as follows :

SELECT    DISTINCT  [Dept_Id],[Dept_Name],SUM(NetSales)[NetSales],
            STUFF((    SELECT ',' + TBL_Sub.[Emp_Name]
                             FROM [Sales_Dtls] TBL_Sub
                        WHERE
TBL_Sub.[Dept_Id] = Tbl_Main.[Dept_Id]
                        FOR XML PATH('')), 1, 1, '' )
                             AS [Sales_Team]
     FROM  [Sales_Dtls] Tbl_Main Group by [Dept_Id],Dept_Name

Output :
The concatenated list of Sales Team in a Single row separated by semicolon(;) as follows :

-------------------------------------------------------------------------------------------------------- 

Thanks, TAMATAM ; Business Intelligence & Analytics Professional 

--------------------------------------------------------------------------------------------------------