SQL Server - Convert Rows into Columns and save data in a Table

I have used this method in one of my projects and thought it might be useful for somebody with similar requirement.

Let us assume, I have a table named dbo.Books, and it has a column named price and category. I want to extract the Max. (or the Sum) Price in each Category using a single Query and save the result in a table for future reference.

Therefore, create the dbo.Books table first.

Now, to convert the rows into columns, I’ll use the PIVOT method like this.

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

Output

We have the result and the data and we know the columns structure. So, lets create our table based on the result.

CREATE TABLE tempT1
	(Computers VARCHAR(10), Science VARCHAR(10), 
		Programming VARCHAR (10), Business VARCHAR (10))

Query the table, although its empty.

SELECT *FROM tempT1

Finally, here’s the query to save the data (the PIVOT result) in the temporary (or any) table.

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

Again, query the table to see the data.

SELECT *FROM tempT1

You can use the saved data for any future reference. Simply, repeat the INSERT query to store (save) new data in the temporary table. For example, lets now save the SUM of the prices in each category.

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

SELECT *FROM tempT1

Note: You can do the entire process, which I have explained above, using a Stored Procedure.

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

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

1) How to find and remove Duplicate rows in a Table using SQL Server ROW_NUMBER() and CTE: 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) How to convert Rows into Columns using SQL Server PIVOT OR how to use PIVOT in SQL Server: 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) Insert Multiple rows with a Single INSERT Statement using SQL Server Table Value Constructor: 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) How to Convert Month Number in a Date to Month Name in SQL Server: 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) SQL Server CHARINDEX Function with Examples: 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 →