Convert Columns to Rows

Question

Post reply

Convert Columns to Rows

dallas13

SSCertifiable

Points: 7491
More actions
September 13, 2016 at 12:01 pm

#400719

Hi I have data in this format,
CREATE TABLE [dbo].[Example](
[AZ] INT NULL,
[NY] INT NULL,
[PA] INT NULL
)
GO
INSERT [dbo].[Example] ([AZ], [NY], [PA]) VALUES ('5', '7', '5')
INSERT [dbo].[Example] ([AZ], [NY], [PA]) VALUES ('2', '3', '5')
INSERT [dbo].[Example] ([AZ], [NY], [PA]) VALUES ('3', '2', '5')
SELECT * FROM [dbo].[Example]
I need to display them such as,
State Total
AZ 10
NY 12
PA 15
I m trying something like this and got stuck,
SELECT State,total
FROM [dbo].[Example]
UNPIVOT
( SUM(AZ) FOR

Viewing 15 posts - 1 through 15 (of 48 total)

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

Jason A. Long SSC-Insane Points: 23780 More actions · Answer 1

Try this...

IF OBJECT_ID('tempdb..#Example','U') IS NOT NULL

DROP TABLE #Example;

CREATE TABLE #Example (

id INT,

AZ INT NULL,

NY INT NULL,

PA INT NULL

);

GO

INSERT#Example (id,AZ,NY,PA) VALUES (1, '5','7','5'),(2, '2','3','5'),(3, '3','2','5');

SELECT

e.id,

x.State,

x.Value

FROM

#Example e

CROSS APPLY (VALUES ('AZ', e.AZ), ('NY', e.NY), ('PA', e.PA)) x ([State], Value)

Results...

id State Value

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

1 AZ 5

1 NY 7

1 PA 5

2 AZ 2

2 NY 3

2 PA 5

3 AZ 3

3 NY 2

3 PA 5

HTH,

Jason

dallas13 SSCertifiable Points: 7491 More actions · Answer 2

dallas13

SSCertifiable

Points: 7491

September 13, 2016 at 1:45 pm

#1900669

Thanks

Jason A. Long SSC-Insane Points: 23780 More actions · Answer 3

Jason A. Long

SSC-Insane

Points: 23780

September 13, 2016 at 2:21 pm

#1900675

dallas13 (9/13/2016)
Thanks

No problem. 🙂

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 4

CROSS APPLY VALUES #FTW!! Nicely done Jason. 🙂

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 5

Slightly different syntax but same results (and identical execution plan)

😎

SELECT

E.id

,X.STATE

,X.VAL

FROM #Example E

CROSS APPLY

(

SELECT 'AZ' , AZ UNION ALL

SELECT 'NY' , NY UNION ALL

SELECT 'PA' , PA

) AS X(STATE,VAL);

Jason A. Long SSC-Insane Points: 23780 More actions · Answer 6

TheSQLGuru (9/13/2016)
CROSS APPLY VALUES #FTW!! Nicely done Jason. 🙂

TY Sir! 😀

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 7

Jason A. Long (9/13/2016)
Try this...

IF OBJECT_ID('tempdb..#Example','U') IS NOT NULL
DROP TABLE #Example;
CREATE TABLE #Example (
id INT,
AZ INT NULL,
NY INT NULL,
PA INT NULL
);
GO
INSERT#Example (id,AZ,NY,PA) VALUES (1, '5','7','5'),(2, '2','3','5'),(3, '3','2','5');
SELECT
e.id,
x.State,
x.Value
FROM
#Example e
CROSS APPLY (VALUES ('AZ', e.AZ), ('NY', e.NY), ('PA', e.PA)) x ([State], Value)
Results...
id State Value
----------- ----- -----------
1 AZ 5
1 NY 7
1 PA 5
2 AZ 2
2 NY 3
2 PA 5
3 AZ 3
3 NY 2
3 PA 5
HTH,
Jason

Although that's great use of the CROSS APPLY trick to unpivot, the output isn't the same as the requested output, which appears to be a sum for each state. I might be missing something but don't you need to add a final aggregation to make the output the same as the requested output?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 8

Jeff Moden (9/14/2016)
Although that's great use of the CROSS APPLY trick to unpivot, the output isn't the same as the requested output, which appears to be a sum for each state. I might be missing something but don't you need to add a final aggregation to make the output the same as the requested output?

Good catch Jeff, I totally missed the aggregation, here is the correct query

😎

SELECT

X.STATE

,SUM(X.VAL) AS VAL

FROM #Example E

CROSS APPLY

(

SELECT 'AZ' , AZ UNION ALL

SELECT 'NY' , NY UNION ALL

SELECT 'PA' , PA

) AS X(STATE,VAL)

GROUP BY X.STATE;

Output

STATE VAL

----- ------

AZ 10

NY 12

PA 15

Jason A. Long SSC-Insane Points: 23780 More actions · Answer 9

Jeff Moden (9/14/2016)
Although that's great use of the CROSS APPLY trick to unpivot, the output isn't the same as the requested output, which appears to be a sum for each state. I might be missing something but don't you need to add a final aggregation to make the output the same as the requested output?

DOH! The devil's always in the details... Good catch Jeff.

This is kind of a moot point since Eirikur beet me to the fix... But...

SELECT

x.State,

SumValue = SUM(x.Value)

FROM

#Example e

CROSS APPLY (VALUES ('AZ', e.AZ), ('NY', e.NY), ('PA', e.PA)) x ([State], Value)

GROUP BY

x.State;

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 10

Sheesh, I missed that too, but based on the OPs code they would certainly have been able to get the right aggregated output thankfully! 🙂

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

drew.allen SSC Guru Points: 76944 More actions · Answer 11

Jason A. Long (9/14/2016)
Jeff Moden (9/14/2016)
Although that's great use of the CROSS APPLY trick to unpivot, the output isn't the same as the requested output, which appears to be a sum for each state. I might be missing something but don't you need to add a final aggregation to make the output the same as the requested output?
DOH! The devil's always in the details... Good catch Jeff.
This is kind of a mute point since Eirikur beet me to the fix... But...

The phrase is "moot point".

SELECT
x.State,
SumValue = SUM(x.Value)
FROM
#Example e
CROSS APPLY (VALUES ('AZ', e.AZ), ('NY', e.NY), ('PA', e.PA)) x ([State], Value)
GROUP BY
x.State;

I was actually wondering whether it would be more efficient to sum before the CROSS APPLY or after. I ran a simple test, but it was impossible to determine on such a small dataset and my computer crashed in the middle of my larger test.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 12

drew.allen (9/14/2016)
I was actually wondering whether it would be more efficient to sum before the CROSS APPLY or after. I ran a simple test, but it was impossible to determine on such a small dataset and my computer crashed in the middle of my larger test.
Drew

IIRC there isn't much of a difference, did test very similar a while back, will dig and see if I can find the test harness.

😎

Jason A. Long SSC-Insane Points: 23780 More actions · Answer 13

drew.allen (9/14/2016)
The phrase is "moot point".

Fixed. 😀

Just looking at the execution plans... Summing 1st is quite a bit more efficient...

IF OBJECT_ID('tempdb..#Example','U') IS NOT NULL

DROP TABLE #Example;

CREATE TABLE #Example (

id INT,

AZ INT NULL,

NY INT NULL,

PA INT NULL

);

GO

INSERT#Example (id,AZ,NY,PA) VALUES (1, '5','7','5'),(2, '2','3','5'),(3, '3','2','5');

--====================================

SELECT

x.State,

SumValue = SUM(x.Value)

FROM

#Example e

CROSS APPLY (VALUES ('AZ', e.AZ), ('NY', e.NY), ('PA', e.PA)) x ([State], Value)

GROUP BY

State;

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

WITH

cte_Example AS (

SELECT

AZ = SUM(e.AZ),

NY = SUM(e.NY),

PA = SUM(e.PA)

FROM

#Example e

)

SELECT

x.State,

x.Value

FROM

cte_Example e

CROSS APPLY (VALUES ('AZ', e.AZ), ('NY', e.NY), ('PA', e.PA)) x ([State], Value);

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 14

Is it just me, or is the syntax for PIVOT/UNPIVOT *much* harder to follow than cross-tab or APPLY VALUES ?

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills