Pivot/Unpivot

Question

Post reply

Pivot/Unpivot

ekant_alone

SSCrazy

Points: 2423
More actions
February 27, 2012 at 7:32 am

#251437

I have one query (say qry1) which return a result set as follows

symbol bit value description

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

Bo 1 10 LastPrice1

Bob 1 69 SEC_LastPrice

KOT 1 1 ASK

KOT 1 4 BID

KOT 1 70 SEC_ShareOut

PEPY 1 10 LastPrice1

PEPY 1 20 VOL

PEPY 1 69 SEC_LastPrice

i have another table tab1 which has one record per symbol.

It has 60 different column, and column name is same as description of the above query (ask,bid,lastprice etc).

I need a query which picks one symbol and description from above qry1 and fetch me the value from tab1's column.

For example in above statement say for symbol KOT pick ASK and go to tab1, look for column ASK and get me the value.

Can some one please help

Viewing 7 posts - 1 through 6 (of 6 total)

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

Borhene.H Grasshopper Points: 21 More actions · Answer 1

Borhene.H

Grasshopper

Points: 21

February 27, 2012 at 7:53 am

#1452079

declare @columnname as varchar(50)

declare @query as varchar(5000)

-- pick the column name (i.e) 'ASK'

select top 1 @columnname = [description] from qry1 where symbol = 'KOT'

-- get the ASK value from tab1

set @query = 'select ' + @columnname + 'from tab1'

exec @query

go

😛

drew.allen SSC Guru Points: 76776 More actions · Answer 2

From the title it's obvious that you know you should be using the PIVOT/UNPIVOT to accomplish the task, but the text of your question indicates that you have no idea how to approach the task. The only logical conclusion for this inconsistency is that it's a homework assignment. You should try it on your own.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Alexander G. SSCrazy Points: 2705 More actions · Answer 3

Hoping to be more helpful and less snotty than others:



WITH x AS

(

SELECT 'Bo' AS SYMBOL,1 AS BBit,10 AS Bval,'LastPrice1' AS BKey

UNION ALL SELECT 'Bob' AS SYMBOL,1 AS BBit,69 AS Bval,'SEC_LastPrice' AS BKey

UNION ALL SELECT 'KOT' AS SYMBOL,1 AS BBit,1 AS Bval,'ASK' AS BKey

UNION ALL SELECT 'KOT' AS SYMBOL,1 AS BBit,4 AS Bval,'BID' AS BKey

UNION ALL SELECT 'KOT' AS SYMBOL,1 AS BBit,70 AS Bval,'SEC_ShareOut' AS BKey

UNION ALL SELECT 'PEPY' AS SYMBOL,1 AS BBit,10 AS Bval,'LastPrice1' AS BKey

UNION ALL SELECT 'PEPY' AS SYMBOL,1 AS BBit,20 AS Bval,'VOL' AS BKey

UNION ALL SELECT 'PEPY' AS SYMBOL,1 AS BBit,69 AS Bval,'SEC_LastPrice' AS BKey

)

SELECT

	Symbol,

	SEC_LastPrice,

	VOL,

	ASK,

	BID

FROM

	x

	PIVOT (SUM(Bval) FOR BKey IN (SEC_LastPrice, VOL, ASK, BID)) y

Please consider the following:

1. You need to know your key values (at least the ones that are interesting for your purpose).

2. You need to SUM or use another aggregate function, because more than one SEC_LAST_Price might exist for a particular symbol.

drew.allen SSC Guru Points: 76776 More actions · Answer 4

Alexander G. (2/27/2012)

Hoping to be more helpful and less snotty than others:

I'm a firm believer in the old aphorism, "Give a man a fish and you feed him for a day. Teach a man to fish and you feed him for a lifetime."

Asking the OP to try their own solution forces them to actually think about the problem and how to approach it. Spoonfeeding them the solution doesn't give them the practice in thinking about how to approach the problem. I don't want to encourage a cookie-cutter approach to SQL solutions.

Second, if you're going to chide people for not being helpful, it would be wise to make sure that you're actually answering the question that is being asked.

ekant_alone (2/27/2012)

I need a query which picks one symbol and description from above qry1 and fetch me the value from tab1's column.

You're "solution" simply reformats the results of qry1, it does not even reference tab1 let alone fetch the requested value from tab1.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

LutzM SSC Guru Points: 107049 More actions · Answer 5

Did you try to use UNPIVOT with tab1 to get a table with whatever_symbol, whatever_description and whatever_value that can be used in a join on with your current query (or, even better, the source table of this query)?

I know the syntax is a little weird... but give it a try and post back if there's something you're struggling with.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Alexander G. SSCrazy Points: 2705 More actions · Answer 6

I'm a firm believer in the old aphorism, "Give a man a fish and you feed him for a day. Teach a man to fish and you feed him for a lifetime."

Asking the OP to try their own solution forces them to actually think about the problem and how to approach it. Spoonfeeding them the solution doesn't give them the practice in thinking about how to approach the problem. I don't want to encourage a cookie-cutter approach to SQL solutions.

Maybe your fisherman has died of starvation before he has learned to fish. And the pivoting syntax (at least as I understood the issue) isn't really what you would call intuitive and explanation in books online is not very didactic.

Second, if you're going to chide people for not being helpful, it would be wise to make sure that you're actually answering the question that is being asked.

I have to admit that you might be right in this regard.

Maybe ekant_alone should be a little clearer with his question...