Pivot/Unpivot question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Pivot/Unpivot question

Author

Topic

nickfinity
Yak Posting Veteran

55 Posts

Posted - 2012-09-11 : 10:05:23

I've never used pivot/unpivot and I'm wondering if that's what I need to use or what my best option is. The data I'm querying is being used in a 3rd party web tool that I don't have much control over and I need to transpose it. Let's say I've got a table that contains info about a company's stock. The columns in the table are market cap, p/e, dividend, etc. There are 40 columns. Can I use pivot/unpivot to transpose the rows/columns? I need to get the results back as two columns, one with the name (market cap) and the other column with the value. And I'll have 40 rows returned. I only ever need to return one company at a time.

I fooled around last night for quite a while, but I never got it to work. The examples I saw never had anywhere near 40 columns and my data does not need aggregated. I just need to switch. Could someone be so kind as to give me a push in the right direction? I sincerely appreciate any help.

Thanks,
Nick

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-09-11 : 10:19:00

If you could post the table structure and some sample data and output it would be a big help. Generally speaking you'd use UNPIVOT like this:

-- sample data
CREATE TABLE a(Stock varchar(8) not null, MarketCap money, PE money, Dividend money)
INSERT a VALUES('GOOG',500e9,50e6,0)
INSERT a VALUES('APPL',600e9,150e6,0)
INSERT a VALUES('MSFT',400e9,10e6,0)
INSERT a VALUES('WCOM',0,-103e9,0)

-- query
SELECT * FROM a
UNPIVOT(val FOR col IN (MarketCap,PE,Dividend)) b

You list each column you want to unpivot in the IN list of the FOR clause. You don't use aggregates in UNPIVOT, but you would for PIVOT. You can use MAX or MIN to PIVOT a single or non-numeric value, like date or character data.

nickfinity
Yak Posting Veteran

55 Posts

Posted - 2012-09-11 : 10:41:36

Thanks a lot for the help. Your sample table is close, but when I add a varchar column I get an error about the types conflicting. Can I cast everything to varchar? For my purposes it really doesn't matter if everything is returned as a string of some sort.


-- sample data
CREATE TABLE a(Stock varchar(8) not null, MarketCap money, PE money, Dividend money, rating varchar(255))
INSERT a VALUES('GOOG',500e9,50e6,0, 'BUY')
INSERT a VALUES('APPL',600e9,150e6,0, 'HOLD')
INSERT a VALUES('MSFT',400e9,10e6,0, 'SELL')
INSERT a VALUES('WCOM',0,-103e9,0, 'BUY')

-- query
SELECT * FROM a
UNPIVOT(val FOR col IN (MarketCap,PE,Dividend, Rating)) b

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-11 : 11:16:27

you need to make sure all columns used inside UNPIVOT are of same type. so above case you might have to cast money fields to varchar

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nickfinity
Yak Posting Veteran

55 Posts

Posted - 2012-09-11 : 12:01:44

Thanks for your help. I'm feeling extra dense today because I still can't get it working.


select marketcap, rating from(
SELECT cast(marketcap as varchar(255)) as marketcap, rating  FROM a) results
UNPIVOT(val FOR col IN (marketcap,rating)) b

SELECT cast(marketcap as varchar(255)) as marketcap, rating  FROM a
UNPIVOT(val FOR col IN (marketcap,rating)) b

Is one of these on the right track? First query gives me invalid column names and the second says I have a type conflict.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-09-11 : 12:11:05

Try this:

WITH results AS (SELECT CAST(marketcap AS VARCHAR(255)) AS marketcap, rating  FROM a) 
SELECT marketcap, rating FROM results
UNPIVOT(val FOR col IN (marketcap,rating)) b

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-11 : 14:22:17

how would you've columns marketcap,rating once you do UNPIVOT?

i think it should be


select val,col from(
SELECT cast(marketcap as varchar(255)) as marketcap, rating  FROM a) results
UNPIVOT(val FOR col IN (marketcap,rating)) b

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nickfinity
Yak Posting Veteran

55 Posts

Posted - 2012-09-12 : 14:44:10

Thank you both for your help. Got it working.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-12 : 15:51:29

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources