Ever wondered how you can convert data from rows to columns in SQL Server. We are talking about an SQL query, which will transform records from multiple rows into columns. Using SQL Server PIVOT, we can efficiently rotate a table’s data to show a summarized result.

Usually, we PIVOT a table using unique values extracted from a column and show the result in multiple columns.

Let's see an example.

As usual, I'll use the table for the demo. The table has five columns and few rows in it. Created the table and insert the records in it.

Also, check in this blog.

PIVOT Using Aggregate function

I'll PIVOT (meaning, Turn) the Books table to get the max price for each distinct category. The T-SQL aggregate MAX() function will return the maximum value from a list of values in a column.

SELECT Computers, Science, Programming, Business FROM ( SELECT Price, Category FROM dbo.Books ) Books PIVOT ( MAX (Price) FOR Category IN (Computers, Science, Programming, Business) ) Result;

The output will look like,

Computers

125.60 Science

210.40 Programming

56.00 Business

150.70

Similarly, we can get the total price for each category using PIVOT and SUM() function.

SELECT Computers, Science, Programming, Business FROM ( SELECT Price, Category FROM dbo.Books ) Books PIVOT ( SUM (Price) FOR Category IN (Computers, Science, Programming, Business) ) Result;

Output

Computers

187.80 Science

759.05 Programming

56.00 Business

150.70

The queries above have one similarity (other than using PIVOT), that it uses pre-defined values as column name (Computer, Science etc.). We looked for unique values in Category column and used the list in our query as columns.

Now, an obvious question that comes to our mind. How do we PIVOT a table if we do not know the values for our columns? It is not always wise to hardcore values as columns. Since, categories might change in the Books table or we add new categories to the list.

Also Read:

PIVOT using Dynamically extracted Columns

This may also be called as Dynamic PIVOT, where values for columns are extracted dynamically using an SQL query, at runtime.

We will first extract all the Distinct categories from the table and store the result in a variable. Each value is separate by a comma.

DECLARE @Category AS VARCHAR(MAX) SELECT @Category = COALESCE (@Category + ', ', '') + CAST(Category AS VARCHAR(20)) FROM (SELECT DISTINCT Category FROM dbo.Books) Books SELECT @Category Categories

Output

Category Business, Computers, Programming, Science

We got the values for our columns, all separate by a comma. Using the above result, we will now create the Dynamic PIVOT. The syntax remains the same as we did in Static PIVOT, except that we will add the variable @Columns inside the query and execute it as we execute a Stored Procedure.

DECLARE @Category AS VARCHAR(MAX) SELECT @Category = COALESCE (@Category + ', ', '') + CAST(Category AS VARCHAR(20)) FROM (SELECT DISTINCT Category FROM dbo.Books) Books DECLARE @DynamicPIVOT AS VARCHAR(MAX) SELECT @DynamicPIVOT = 'SELECT ' + @Category + ' FROM ( SELECT Price, Category FROM dbo.Books ) Books PIVOT ( MAX (Price) FOR Category IN (' + @Category + ') ) Result;' EXEC (@DynamicPIVOT)

Output

Business

150.70 Computers

125.60 Programming

56.00 Science

210.40

Conclusion

You must use SQL Server 2005 and above to use PIVOT in your queries or procedures. SQL Server PIVOT simplifies the process of transforming data from rows to columns, where we use the row’s values as column headers. PIVOT usually works with aggregate functions, such as MAX(), SUM() etc.

Thanks for reading. ☺

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

Here's a list of Top 5 popular SQL Server posts.

1) : Duplicate rows in tables can be very annoying for DBA’s and programmers, as it raises many uncomfortable questions about the authenticity of data in a database. The matter gets worse when company auditors complain about irregularities in the balance sheet etc.

2) : Ever wondered how you can convert data from rows to columns in SQL Server. We are talking about an SQL query, which will transform records from multiple rows into columns. Using SQL Server PIVOT, we can efficiently rotate a table’s data to show a summarized result.

3) : While managing an Inventory management System for an organization, I have came across a situation where I had to perform bulk upload on a table in SQL Server. Bulk upload requires inserting multiple rows of data in a table.

4) : Let us assume I have a Sales table with sales data for each month. Every day sales is stored in the table with columns such as date, quantity, price etc. I want to get the total sales for every month. Now since I have a column with “date” data type, I want to convert the month number in the date to Month Name (like February, March etc.). Find out how this is done in SQL Server.

5) : The primary use of an SQL Server CHARINDEX function is to find the first or starting location of an expression or characters in a given string. To make it simple, it is like searching a specified character or characters in a string.

← PreviousNext →