Converting last N columns to rows in sql server

sumit7034

Hi My sql query result is

Store month sale purchase
A Jan 2000 150

I want my result like

Store month Expense value
A Jan Sale 2000
A Jan Purchase 150

Please help me how to get this result using sql query. Thanks in advance

Mycroft Holmes

Use 2 queries with a UNION between them.

Never underestimate the power of human stupidity RAH

sumit7034

Could you tell me the approach of using 2 queries with a UNION. Or tell me the syntex how to apply union to get the result. Thanks

Mycroft Holmes

Seriously - you do not have BOL or google where you are!

Select House, 'Sale', Sale as Value
from Table
union
Select House, 'Purchase', Purchase as Value
from Table

Never underestimate the power of human stupidity RAH

Pravin Patil Mumbai

You can use UNION operator for this. 1. http://www.w3schools.com/sql/sql_union.asp[^] 2. http://www.sql-tutorial.com/sql-union-sql-tutorial/[^] 3. http://www.1keydata.com/sql/sqlunion.html[^] Hope this helps. All the best.

J4amieC

You can use a union, as already suggested, and with SQL Server you can also use the UNPIVOT command.

WITH myData(Store , Mth , Sale , Purchase )
AS
(
SELECT 'A','Jan', 2000,150
UNION SELECT 'B','Jan', 1000,200
)
SELECT Store, Mth, Expense, Value
FROM
(
Select Store, Mth, Sale, Purchase
FROM myData
) p
UNPIVOT
( Value FOR Expense IN (Sale, Purchase) ) AS unpvt;

Let me explain that a bit. The first bit (Starting 'WITH') just gives us some example data, I have added a second row onto your original data to help confirm the result. The second bit (Starting 'SELECT') Selects the data from an unpivoted view of your SALE and Purchase columns. The result of the above query is

A Jan Sale 2000
A Jan Purchase 150
B Jan Sale 1000
B Jan Purchase 200