Need to stack/transpose one row w/3 fields to a single column

Question

Need to stack/transpose one row w/3 fields to a single column

latingntlman

SSCarpal Tunnel

Points: 4348
More actions
April 29, 2015 at 11:48 am

#317811

Hello, the subject pretty much says it all. I have the following result set but I want to stack or transpose the 3 fields into a single column. I may add more fields later, but right now I want to know what's the best and simplest way.
Current result set:
TotalAllCustomersOnFile | TotalConsumersWithValidEmail | TotalConsumersWithValidEmailAndOptedIn
2,500 1,750 1,500
Desired result set:
Audience | Totals
----------------------------------------------------
TotalAllCustomersOnFile | 2,500
TotalConsumersWithValidEmail | 1,750
TotalConsumersWithValidEmailAndOptedIn | 1,500

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182948 More actions · Answer 1

latingntlman (4/29/2015)
Hello, the subject pretty much says it all. I have the following result set but I want to stack or transpose the 3 fields into a single column. I may add more fields later, but right now I want to know what's the best and simplest way.
Current result set:
TotalAllCustomersOnFile | TotalConsumersWithValidEmail | TotalConsumersWithValidEmailAndOptedIn
2,500 1,750 1,500
Desired result set:
Audience | Totals
----------------------------------------------------
TotalAllCustomersOnFile | 2,500
TotalConsumersWithValidEmail | 1,750
TotalConsumersWithValidEmailAndOptedIn | 1,500

Quick suggestion

😎

USE tempdb;

GO

SET NOCOUNT ON;

;WITH X(TotalAllCustomersOnFile,TotalConsumersWithValidEmail,TotalConsumersWithValidEmailAndOptedIn) AS

(SELECT * FROM (VALUES ('2,500', '1,750', '1,500')) AS X(TotalAllCustomersOnFile,TotalConsumersWithValidEmail,TotalConsumersWithValidEmailAndOptedIn)

)

SELECT

XY.Audience

,XY.Totals

FROM X

CROSS APPLY

(

SELECT 'TotalAllCustomersOnFile' AS Audience, TotalAllCustomersOnFile AS Totals UNION ALL

SELECT 'TotalConsumersWithValidEmail' AS Audience,TotalConsumersWithValidEmail AS Totals UNION ALL

SELECT 'TotalConsumersWithValidEmailAndOptedIn' AS Audience,TotalConsumersWithValidEmailAndOptedIn AS Totals

) AS XY;

Results

Audience Totals

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

TotalAllCustomersOnFile 2,500

TotalConsumersWithValidEmail 1,750

TotalConsumersWithValidEmailAndOptedIn 1,500

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

And just to give a second option.

WITH X(TotalAllCustomersOnFile,TotalConsumersWithValidEmail,TotalConsumersWithValidEmailAndOptedIn) AS

(SELECT * FROM (VALUES ('2,500', '1,750', '1,500')) AS X(TotalAllCustomersOnFile,TotalConsumersWithValidEmail,TotalConsumersWithValidEmailAndOptedIn)

)

SELECT

XY.Audience

,XY.Totals

FROM X

UNPIVOT

( Totals FOR Audience IN

( TotalAllCustomersOnFile

,TotalConsumersWithValidEmail

,TotalConsumersWithValidEmailAndOptedIn )

) AS XY;

References:

APPLY method: http://www.sqlservercentral.com/articles/CROSS+APPLY+VALUES+UNPIVOT/91234/

UNPIVOT method: https://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

latingntlman SSCarpal Tunnel Points: 4348 More actions · Answer 3

Thank you both. I'll work to implement one of these suggestions.