Convert Rows to columns using 'Pivot' in SQL Server

SQL Server has a PIVOT relational operator to turn the unique values of a specified column from multiple rows into multiple column values in the output (cross-tab), effectively rotating a table. It also allows performing aggregations, wherever required, for column values that are expected in the final output

The objective of this article is to demonstrate different SQL Server T-SQL options that could be utilised in order to transpose repeating rows of data into a single row with repeating columns as depicted in Table 2. Some of the T-SQL options that will be demonstrated will use very few lines of code to successfully transpose Table 1 into Table 2 but may not necessary be optimal in terms query execution.

Here we have explain how to convert column to rows in sql server.

Step:1 Create Table 1

Step:2 Create table 2

Step 3: Using a T-SQL Pivot function is one of the simplest method for transposing rows into columns. Script 1 shows how a Pivot function can be utilised.

Select * from Emp ee inner join (
SELECT p.[1] As GST, p.[2] as SGST, p.[3] as ISGST,OrderId
FROM
(
SELECT p.OrderId,p.TaxAmount,p.TaxId
FROM dbo.Taxes AS p
INNER JOIN dbo.Emp AS o
ON p.OrderId = o.OrderId
) AS j
PIVOT
(
SUM(taxamount) FOR taxid IN ([1],[2],[3])
) AS p) as op on op.OrderId=ee.OrderId

Step 4 : The results of executing Script 1 are shown in Figure 1

Step 5 : Now pivot using single table

Select pvv.OrderId,
pvv.[1] as gst,
pvv.[2] as sgst,
pvv.[3] as igst
from (select OrderId,TaxId,TaxAmount from taxes) tx
pivot(sum(taxamount) for taxid in ([1],[2],[3])) pvv

Step 6 : The results of executing Script 2 are shown in Figure 2