CODEWITHSUNDEEP
sqlsql-server
TAD
TAD

Reputation: 45

SQL Server: converting column into new rows in another column

I'm using SQL Server 2014 and I have the following data: 

Owner|    Full Name|    Revenue|
--------------------------------
John      Jane          1000
Nancy     Ginger        2000
Sanya     Brian         3000

I'm hoping to restructure so that everyone is listed under Full Name, along with their revenue like below: 

Full Name|    Revenue|
----------------------
Jane          1000
Ginger        2000
Brian         3000
John          1000
Nancy         2000
Sanya         3000

I can do this easily by creating 2 separate views, and then doing a union - but there has got to be a way to do this in one view - instead of 2. Is there a way?

Upvotes: 0

Views: 39

Answers (2)

LukStorms
LukStorms

Reputation: 29647

You can use UNION ALL for this in 1 view

CREATE VIEW vwRevenues AS
SELECT [Full Name], Revenue
FROM YourTable
UNION ALL
SELECT Owner, Revenue
FROM YourTable;

Or this:

CREATE VIEW vwRevenues AS
SELECT v.[Full Name], t.Revenue
FROM YourTable t
OUTER APPLY (VALUES ([Full Name]), (Owner)) v([Full Name]);

But I'm not so certain if the APPLY method is actually faster than a UNION ALL when used in a view.

Upvotes: 1

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You want unpivot :

SELECT tt.*
FROM table t CROSS APPLY
     ( VALUES ([Full Name], [Revenue], 2), ([Owner], [Revenue], 1) 
     ) tt ([Full Name], [Revenue], seq)
ORDER BY seq;

Upvotes: 3

Related Questions