Pivot in sql Or Pivot Example Or Convert rows to columns in sql server Or Display row to column in sql server

Step 1:- Create temp table

CREATE TABLE #Books(

       BookID int IDENTITY(1,1) NOT NULL,

       BookName varchar(50) NULL,

       Category varchar(50) NULL,

       Price numeric(18, 2) NULL,

       Price_Range varchar(20) NULL,

       PRIMARY KEY CLUSTERED ( BookID ASC )

)

Step 2:- Add few rows to the table

INSERT INTO #Books (BookName, Category, Price, Price_Range)VALUES('Computer Architecture', 'Computers', 125.6, '100-150')

INSERT INTO #Books (BookName, Category, Price, Price_Range)VALUES('Advanced Composite Materials', 'Science', 172.56, '150-200')

INSERT INTO #Books (BookName, Category, Price, Price_Range)VALUES('Asp.Net 4 Blue Book', 'Programming', 56.00, '50-100')

INSERT INTO #Books (BookName, Category, Price, Price_Range)VALUES('Stategies Unplugged', 'Science', 99.99, '50-100')

INSERT INTO #Books (BookName, Category, Price, Price_Range)VALUES('Teaching Science', 'Science', 164.10, '150-200')

INSERT INTO #Books (BookName, Category, Price, Price_Range)VALUES('Challenging Times', 'Business', 150.70, '150-200')

INSERT INTO #Books (BookName, Category, Price, Price_Range)VALUES('Circuit Bending', 'Science', 112.00, '100-150')

INSERT INTO #Books (BookName, Category, Price, Price_Range)VALUES('Popular Science', 'Science', 210.40, '200-250')

INSERT INTO #Books (BookName, Category, Price, Price_Range)VALUES('ADOBE Premiere', 'Computers', 62.20, '50-100')

SELECT * FROM #Books

Step 3:- Walk through PIVOT STRUCTURE

SELECT <non-pivoted column>,

    first pivoted column AS <column name>,

    second pivoted column AS <column name>,

    ...

    last pivoted column AS <column name>

FROM

    (<SELECT query that produces the data>)

    AS <alias for the source query>

PIVOT

(

    <aggregation function>(<column being aggregated>)

FOR

<column that contains the values that will become column headers>

    IN ( first pivoted column, second pivoted column,

    ... last pivoted column)

) AS <alias for the pivot table>

<optional ORDER BY clause>;

      

Step 4:- Query for get the max price of each Category Using group by you will get the Row wise record

select Category,max(Price) Price

from #Books

group by Category

Now, if you want to display Row wise records as a column, you need to write PIVOT query

SELECT Computers, Science, Programming, Business

FROM (

  SELECT Price, Category FROM #Books

) Books

PIVOT (

  MAX(Price) FOR Category IN (Computers, Science, Programming, Business)

) Result;

Using Dynamic PIVOT Query

DECLARE @Category AS VARCHAR(MAX)

SELECT @Category =

       COALESCE(@Category + ', ', '') + CAST(Category AS VARCHAR(20))

       FROM (SELECT DISTINCT Category FROM #Books) Books

DECLARE @DynamicPIVOT AS VARCHAR(MAX)

SELECT @DynamicPIVOT = 'SELECT ' + @Category +

' FROM (

       SELECT Price, Category FROM #Books

) Books

PIVOT (

       MAX(Price) FOR Category IN (' + @Category + ')

) Result;'

EXEC (@DynamicPIVOT)

Step 5:- Query for get the Sum price of each Category Using group by you will get the Row wise record

select Category,sum(Price) Price

from #Books

group by Category

Now, if you want to display Row wise records as a column, you need to write PIVOT query

SELECT Computers, Science, Programming, Business

FROM (

  SELECT Price, Category FROM #Books

) Books

PIVOT (

  SUM(Price) FOR Category IN (Computers, Science, Programming, Business)

) Result;

Using Dynamic PIVOT Query

DECLARE @Category AS VARCHAR(MAX)

SELECT @Category =

       COALESCE(@Category + ', ', '') + CAST(Category AS VARCHAR(20))

       FROM (SELECT DISTINCT Category FROM #Books) Books

DECLARE @DynamicPIVOT AS VARCHAR(MAX)

SELECT @DynamicPIVOT = 'SELECT ' + @Category +

' FROM (

       SELECT Price, Category FROM #Books

) Books

PIVOT (

       SUM(Price) FOR Category IN (' + @Category + ')

) Result;'

EXEC (@DynamicPIVOT)

Note:-

You cannot use those columns in the select statement, those are participating in pivoting.

In above example we are using the Category column in pivot, so we cannot use it in select statement.