June 5, 2012 at 12:04 am
1.) I have a table of daily stock prices - symbol, date, open, high, low, close
2.) I have a temp table that is essentially a list of symbols
3.) I want to use the temp tables list of stocks to select closes from the first table
4.) The selection of rows should output as...
Date | CloseSymbol1 | CloseSymbol2 | CloseSymbol3 | ... | CloseSymbolN
Where N is the number of symbols int the temp table.
I have this so far which provides all the closes in a single column. All help is appreciated.
CREATE TABLE #TempSymbolList
(
TempSymbol VARCHAR(10)
)
INSERT INTO #TempSymbolList (TempSymbol) VALUES ('AAPL')
INSERT INTO #TempSymbolList (TempSymbol) VALUES ('AMD')
INSERT INTO #TempSymbolList (TempSymbol) VALUES ('INTC')
INSERT INTO #TempSymbolList (TempSymbol) VALUES ('MSFT')
SELECT qdb.Date, qdb.Close AS CloseA
FROM StockQuote qdb
INNER JOIN #TempSymbolList ON qdb.Symbol = #TempSymbolList.TempSymbol
ORDER BY Date ASC
DROP TABLE #TempSymbolList
June 5, 2012 at 12:21 am
June 5, 2012 at 12:43 am
This will create a table of the stock data, I stripped out everything but the close ( no open,high low)
USE [StockQuoteDB]
-- create the stock Quote Table
CREATE TABLE [dbo].[StockQuote](
[ID] [int] IDENTITY(1,1) NOT NULL,
[xSymbol] [varchar](10) NOT NULL,
[xDate] [int] NOT NULL,
[xClose] [decimal](18, 4) NOT NULL,
)
-- Put some data into the table
INSERT INTO StockQuote (xSymbol, xDate, xClose) VALUES ('MSFT', 100, 100.5)
INSERT INTO StockQuote (xSymbol, xDate, xClose) VALUES ('MSFT', 101, 101.5)
INSERT INTO StockQuote (xSymbol, xDate, xClose) VALUES ('MSFT', 102, 102.6)
INSERT INTO StockQuote (xSymbol, xDate, xClose) VALUES ('MSFT', 103, 101.3)
INSERT INTO StockQuote (xSymbol, xDate, xClose) VALUES ('MSFT', 104, 99.6)
INSERT INTO StockQuote (xSymbol, xDate, xClose) VALUES ('MSFT', 105, 103.1)
INSERT INTO StockQuote (xSymbol, xDate, xClose) VALUES ('AAPL', 100, 600.5)
INSERT INTO StockQuote (xSymbol, xDate, xClose) VALUES ('AAPL', 101, 601.5)
INSERT INTO StockQuote (xSymbol, xDate, xClose) VALUES ('AAPL', 102, 602.6)
INSERT INTO StockQuote (xSymbol, xDate, xClose) VALUES ('AAPL', 103, 601.3)
INSERT INTO StockQuote (xSymbol, xDate, xClose) VALUES ('AAPL', 104, 599.6)
INSERT INTO StockQuote (xSymbol, xDate, xClose) VALUES ('AAPL', 105, 603.1)
INSERT INTO StockQuote (xSymbol, xDate, xClose) VALUES ('AMD', 100, 200.5)
INSERT INTO StockQuote (xSymbol, xDate, xClose) VALUES ('AMD', 101, 201.5)
INSERT INTO StockQuote (xSymbol, xDate, xClose) VALUES ('AMD', 102, 202.6)
INSERT INTO StockQuote (xSymbol, xDate, xClose) VALUES ('AMD', 103, 201.3)
INSERT INTO StockQuote (xSymbol, xDate, xClose) VALUES ('AMD', 104, 299.6)
INSERT INTO StockQuote (xSymbol, xDate, xClose) VALUES ('AMD', 105, 203.1)
INSERT INTO StockQuote (xSymbol, xDate, xClose) VALUES ('XYZ', 100, 900.5)
INSERT INTO StockQuote (xSymbol, xDate, xClose) VALUES ('XYZ', 101, 901.5)
INSERT INTO StockQuote (xSymbol, xDate, xClose) VALUES ('XYZ', 102, 902.6)
INSERT INTO StockQuote (xSymbol, xDate, xClose) VALUES ('XYZ', 103, 901.3)
INSERT INTO StockQuote (xSymbol, xDate, xClose) VALUES ('XYZ', 104, 999.6)
INSERT INTO StockQuote (xSymbol, xDate, xClose) VALUES ('XYZ', 105, 903.1)
-- Create Temp Table of symbols
CREATE TABLE #TempSymbolList
(
TempSymbol VARCHAR(10)
)
INSERT INTO #TempSymbolList (TempSymbol) VALUES ('AAPL')
INSERT INTO #TempSymbolList (TempSymbol) VALUES ('AMD')
INSERT INTO #TempSymbolList (TempSymbol) VALUES ('MSFT')
SELECT qdb.xDate, qdb.xClose AS CloseA
FROM StockQuote qdb
INNER JOIN #TempSymbolList ON qdb.xSymbol = #TempSymbolList.TempSymbol
ORDER BY qdb.xDate ASC
DROP TABLE #TempSymbolList
DROP TABLE StockQuote
June 5, 2012 at 12:59 am
Try below Query
SELECT qdb.xDate,
sum(case when t.TempSymbol='AAPL' then qdb.xClose end) 'AAPL',
sum(case when t.TempSymbol='AMD' then qdb.xClose end) 'AMD',
sum(case when t.TempSymbol='MSFT' then qdb.xClose end) 'MSFT'
FROM StockQuote qdb
INNER JOIN #TempSymbolList t ON qdb.xSymbol = t.TempSymbol
group by qdb.xDate
ORDER BY qdb.xDate ASC
or use pivot table
June 5, 2012 at 1:01 am
The method you want is either a Crosstab query or the PIVOT function, but the problem with both of those is you typically want to know how many columns you're going to produce to the receiver.
If you're not always sure of how many columns you're going to return, you're looking at performing a dynamic pivot, which can get kind of hairy.
I'll leave you with that for now so you can work on it on your own for a bit or until someone else can try to tackle it in the meanwhile, else I'll try to provide you the code as I think I understand what you're looking for in the morning.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 5, 2012 at 1:08 am
Evil Kraig F (6/5/2012)
The method you want is either a Crosstab query or the PIVOT function, but the problem with both of those is you typically want to know how many columns you're going to produce to the receiver.If you're not always sure of how many columns you're going to return, you're looking at performing a dynamic pivot, which can get kind of hairy.
I'll leave you with that for now so you can work on it on your own for a bit or until someone else can try to tackle it in the meanwhile, else I'll try to provide you the code as I think I understand what you're looking for in the morning.
You are correct, the number of columns will change or vary. I will search for "dynamic pivot".
June 5, 2012 at 2:40 am
Is this what you have i mind?
--Static Pivot
Select xDATE, [MSFT] As CloseSymbol1, [AAPL] As CloseSymbol2, [AMD] As CloseSymbol3 From
(Select A.* From StockQuote as A
JOIN #TempSymbolList as B ON A.xSymbol = B.TempSymbol) As C
Pivot
(max(xSymbol) For xSymbol IN ([MSFT], [AAPL], [AMD])) pvt
--Dynamic Pivot
Declare @cols varchar(max), @sql varchar(max)
Declare @tbl Table(Cols varchar(10))
Insert into @tbl
Select Distinct TempSymbol From #TempSymbolList
Select @cols = Coalesce(@cols + ', ', '') + QUOTENAME(Cols) From @tbl
Set @sql = 'Select xDATE, '+@cols+' From
(Select A.* From StockQuote as A
JOIN #TempSymbolList as B ON A.xSymbol = B.TempSymbol) As C
Pivot
(max(xSymbol) For xSymbol IN ('+@cols+')) pvt'
Execute (@sql)
June 5, 2012 at 3:34 am
vinu512 (6/5/2012)
Is this what you have i mind?
--Static Pivot
Select xDATE, [MSFT] As CloseSymbol1, [AAPL] As CloseSymbol2, [AMD] As CloseSymbol3 From
(Select A.* From StockQuote as A
JOIN #TempSymbolList as B ON A.xSymbol = B.TempSymbol) As C
Pivot
(max(xSymbol) For xSymbol IN ([MSFT], [AAPL], [AMD])) pvt
--Dynamic Pivot
Declare @cols varchar(max), @sql varchar(max)
Declare @tbl Table(Cols varchar(10))
Insert into @tbl
Select Distinct TempSymbol From #TempSymbolList
Select @cols = Coalesce(@cols + ', ', '') + QUOTENAME(Cols) From @tbl
Set @sql = 'Select xDATE, '+@cols+' From
(Select A.* From StockQuote as A
JOIN #TempSymbolList as B ON A.xSymbol = B.TempSymbol) As C
Pivot
(max(xSymbol) For xSymbol IN ('+@cols+')) pvt'
Execute (@sql)
Vinu Vijayan, Thank you for your help!
I think it is very close and is obviously a very good demonstration of the dynamic pivot. Unfortunately, I haven't grasped the logic of the pivot yet to say if it works for me. The final output would look like ..
xDate | AAPL | AMD | MSFT
100 | 600.5 | 200.5 | 100.5
101 | 601.5 | 201.5 | 101.5
102 | 602.6 | 202.6 | 102.6
103 | 601.3 | 201.3 | 101.3
104 | 599.6 | 299.6 | 99.6
105 | 603.1 | 203.1 | 103.1
Can we tweak the Dynamic Pivot function to work as such?
June 5, 2012 at 4:11 am
Yes We can tweak it.
--Static Pivot
Select xDATE, Max(MSFT), MAX(AAPL), MAX(AMD) From
(Select xDATE, [MSFT], [AAPL], [AMD] From
(Select A.* From StockQuote as A
JOIN #TempSymbolList as B ON A.xSymbol = B.TempSymbol) As C
Pivot
(max(xClose) For xSymbol IN ([MSFT], [AAPL], [AMD])) pvt) As D
Group By xDate
--Dynamic Pivot
Declare @cols varchar(max), @sql varchar(max), @cols1 varchar(max)
Declare @tbl Table(Cols varchar(10))
Insert into @tbl
Select Distinct TempSymbol From #TempSymbolList
Select @cols = Coalesce(@cols + ', ', '') + QUOTENAME(Cols) From @tbl
Select @cols1 = Coalesce('Max(' +@cols1+ '), ' + 'Max(', '') + QUOTENAME(Cols) From @tbl
Set @cols1 = SUBSTRING(@cols1, 5, DATALENGTH(@cols1)) + ')'
Set @sql = 'Select xDATE, '+@cols1+' From
(Select xDATE, '+@cols+' From
(Select A.* From StockQuote as A
JOIN #TempSymbolList as B ON A.xSymbol = B.TempSymbol) As C
Pivot
(max(xClose) For xSymbol IN ('+@cols+')) pvt) As D
Group By xDate'
Execute (@sql)
June 5, 2012 at 4:20 am
vinu512 (6/5/2012)
Yes We can tweak it.
--Static Pivot
Select xDATE, Max(MSFT), MAX(AAPL), MAX(AMD) From
(Select xDATE, [MSFT], [AAPL], [AMD] From
(Select A.* From StockQuote as A
JOIN #TempSymbolList as B ON A.xSymbol = B.TempSymbol) As C
Pivot
(max(xClose) For xSymbol IN ([MSFT], [AAPL], [AMD])) pvt) As D
Group By xDate
--Dynamic Pivot
Declare @cols varchar(max), @sql varchar(max), @cols1 varchar(max)
Declare @tbl Table(Cols varchar(10))
Insert into @tbl
Select Distinct TempSymbol From #TempSymbolList
Select @cols = Coalesce(@cols + ', ', '') + QUOTENAME(Cols) From @tbl
Select @cols1 = Coalesce('Max(' +@cols1+ '), ' + 'Max(', '') + QUOTENAME(Cols) From @tbl
Set @cols1 = SUBSTRING(@cols1, 5, DATALENGTH(@cols1)) + ')'
Set @sql = 'Select xDATE, '+@cols1+' From
(Select xDATE, '+@cols+' From
(Select A.* From StockQuote as A
JOIN #TempSymbolList as B ON A.xSymbol = B.TempSymbol) As C
Pivot
(max(xClose) For xSymbol IN ('+@cols+')) pvt) As D
Group By xDate'
Execute (@sql)
Yes, that works perfectly. Thanks again. Now I have to try and understand how it works.
June 5, 2012 at 4:26 am
You can read about Dynamic Pivot here:
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply