July 20, 2012 at 4:30 am
I am using a dynamic pivot to get the 'x' number of stock prices for a series of dates.
The following code creates a database and uses the dynamic pivot to produce the results in the following format:
xDATEAAPLMSFTAMD
100NULL100.5000NULL
100600.5000NULLNULL
100NULLNULL200.5000
101NULL101.5000NULL
101601.5000NULLNULL
101NULLNULL201.5000
102NULL102.6000NULL
....
However, I want the results as the following format:
xDATEAAPLMSFTAMD
100600.5000 100.5000 200.5000
101601.5000 101.5000 201.5000
102602.6000 102.6000 202.6000
103601.3000 101.3000 201.3000
104599.6000 99.6000 299.6000
105603.1000 103.1000 203.1000
Can anybody suggest how I need to write my dynamic pivot query to get the desired result? I had a similar post but the suggestion I given ultimately did not work. Any help is appreciated.
============================================
CREATE DATABASE StockQuoteDB
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 ('MSFT')
INSERT INTO #TempSymbolList (TempSymbol) VALUES ('AMD')
--INSERT INTO #TempSymbolList (TempSymbol) VALUES ('XYZ')
DECLARE @info nvarchar(max)
DECLARE @sql nvarchar(max)
select @info = STUFF(
( select ','+TempSymbol from #TempSymbolList for xml path('')
) ,1,1,'')
Set @sql = N'SELECT xDATE, '+@Info+' From
(Select A.* From StockQuote as A
JOIN #TempSymbolList as B ON A.xSymbol = B.TempSymbol) As C
Pivot
(MAX(xClose) For xSymbol IN ('+@Info+')) pvt
ORDER BY xDate ASC'
exec sp_executesql @sql
DROP TABLE #TempSymbolList
DROP TABLE StockQuote
July 20, 2012 at 4:44 am
Hi there,
try
DECLARE @info NVARCHAR(MAX)
DECLARE @InfoPvt NVARCHAR(MAX)
DECLARE @sql NVARCHAR(MAX)
SELECT @info = STUFF(( SELECT ', ' + 'MAX(' + TempSymbol + ')'
FROM #TempSymbolList
FOR
XML PATH('')
), 1, 1, '')
SELECT @InfoPvt = STUFF(( SELECT ', ' + TempSymbol
FROM #TempSymbolList
FOR
XML PATH('')
), 1, 1, '')
SET @sql = N'SELECT xDATE, ' + @info + ' From
(Select A.* From StockQuote as A
JOIN #TempSymbolList as B ON A.xSymbol = B.TempSymbol) As C
Pivot
(MAX(xClose) For xSymbol IN (' + @InfoPvt + ')) pvt
group by xDATE
ORDER BY xDate ASC
'
EXEC sp_executesql @sql
July 20, 2012 at 4:53 am
You need to Group the data. You need to change the Dynamic Pivot Query as follows:
DECLARE @info nvarchar(max)
DECLARE @sql nvarchar(max)
--Added Variable
DECLARE @info1 nvarchar(max)
select @info = STUFF(
( select ','+TempSymbol from #TempSymbolList for xml path('')
) ,1,1,'')
--Added Stuff Claculation
select @info1 = STUFF(
( select '), Max('+TempSymbol from #TempSymbolList for xml path('')
) ,1,2,'')
Select @info1
--Altered dynamic Query
Set @sql = N'Select xDate, '+@Info1+' From
(SELECT xDATE, '+@Info+' From
(Select A.* From StockQuote as A
JOIN #TempSymbolList as B ON A.xSymbol = B.TempSymbol) As C
Pivot
(MAX(xClose) For xSymbol IN ('+@Info+')) pvt) As a
Group By xDate
ORDER BY xDate ASC'
Print @sql
exec sp_executesql @sql
July 20, 2012 at 5:13 am
Thanks, clayman and vinus! Both solutions worked with the exception of one maybe not so small point. I did not get the symbols in the header. Is this possible?
xDate(No column name)(No column name)(No column name)
100600.5000100.5000200.5000
101601.5000101.5000201.5000
102602.6000102.6000202.6000
103601.3000101.3000201.3000
104599.600099.6000 299.6000
105603.1000103.1000203.1000
should be ..
xDateAAPL MSFT AMD
100600.5000100.5000200.5000
101601.5000101.5000201.5000
102602.6000102.6000202.6000
103601.3000101.3000201.3000
104599.600099.6000 299.6000
105603.1000103.1000203.1000
July 20, 2012 at 5:20 am
tobinare (7/20/2012)
Thanks, clayman and vinus! Both solutions worked with the exception of one maybe not so small point. I did not get the symbols in the header. Is this possible?xDate(No column name)(No column name)(No column name)
100600.5000100.5000200.5000
101601.5000101.5000201.5000
102602.6000102.6000202.6000
103601.3000101.3000201.3000
104599.600099.6000 299.6000
105603.1000103.1000203.1000
should be ..
xDateAAPL MSFT AMD
100600.5000100.5000200.5000
101601.5000101.5000201.5000
102602.6000102.6000202.6000
103601.3000101.3000201.3000
104599.600099.6000 299.6000
105603.1000103.1000203.1000
Change
SELECT @info = STUFF(( SELECT ', ' + 'MAX(' + TempSymbol + ')'
FROM #TempSymbolList
to
SELECT @info = STUFF(( SELECT ', ' + 'MAX(' + TempSymbol + ') As ['
+ TempSymbol + ']'
FROM #TempSymbolList
July 20, 2012 at 5:24 am
Thank you so much! That works great
July 20, 2012 at 5:24 am
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply