July 21, 2012 at 11:50 pm
The following uses a dynamic pivot and returns some rows with a record with a NULL. How can I return only the records without NULL. So in the following, all of the records for lines 102 and 103 would not be returned. I also would not get the warning message:
Warning: Null value is eliminated by an aggregate or other SET operation.
xDATEAAPLMSFTAMD
100600.5000100.5000200.5000
101601.5000101.5000201.5000
102602.6000102.6000NULL
103601.3000101.3000NULL
104599.600099.6000299.6000
105603.1000103.1000203.1000
106602.1000103.8000202.1000
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 ('MSFT', 106, 103.8)
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 ('AAPL', 106, 602.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 ('AMD', 106, 202.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)
INSERT INTO StockQuote (xSymbol, xDate, xClose) VALUES ('XYZ', 106, 900.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')
--DROP TABLE #TempSymbolList
DECLARE @info NVARCHAR(MAX)
DECLARE @InfoPvt NVARCHAR(MAX)
DECLARE @sql NVARCHAR(MAX)
SELECT @info = STUFF(( SELECT ', ' + 'MAX([' + TempSymbol + ']) As ['+ 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
DROP TABLE #TempSymbolList
DROP TABLE StockQuote
July 22, 2012 at 7:10 am
one idea...not performance tested
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 ('MSFT', 106, 103.8)
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 ('AAPL', 106, 602.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 ('AMD', 106, 202.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)
INSERT INTO StockQuote (xSymbol, xDate, xClose) VALUES ('XYZ', 106, 900.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')
--DROP TABLE #TempSymbolList
DECLARE @info NVARCHAR(MAX)
DECLARE @InfoPvt NVARCHAR(MAX)
DECLARE @sql NVARCHAR(MAX)
;WITH CTE ---==== select distinct xdate rows which have at least one row for every row in #TempSymbolList
AS ( SELECT S.xDate
FROM StockQuote AS S INNER JOIN
[#TempSymbolList] AS T ON S.xSymbol = T.TempSymbol
GROUP BY S.xDate
HAVING (COUNT(DISTINCT S.xSymbol) =
(SELECT COUNT(DISTINCT TempSymbol) AS Expr1
FROM [#TempSymbolList])))
--=== select only rows from stockQuote where sDate column is returned by the CTE
SELECT StockQuote.ID ,
StockQuote.xSymbol ,
StockQuote.xDate ,
StockQuote.xClose
INTO #StockQuote --- temp table to be used in dymanmic pivot below
FROM
StockQuote INNER JOIN CTE ON StockQuote.xDate = CTE.xDate;
SELECT @info = STUFF(( SELECT ', ' + 'ISNULL(MAX([' + TempSymbol + ']),0) As ['+ TempSymbol + ']' -- ISNULL not necessary left in for testing
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
DROP TABLE #TempSymbolList
DROP TABLE StockQuote
DROP TABLE #StockQuote
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 22, 2012 at 1:06 pm
J Livingston SQL (7/22/2012)
one idea...not performance tested
Thank's, I like your solution and I never would have thought of it.
I added a join of the StockQuote with #TempSymbolList to produce #StockQuote with only the dates and symbols needed. I thought this might improve performance when I apply the dynamic pivot to a smaller table.
--=== select only rows from stockQuote where sDate column is returned by the CTE
SELECT StockQuote.ID ,
StockQuote.xSymbol ,
StockQuote.xDate ,
StockQuote.xClose
INTO #StockQuote --- temp table to be used in dynamic pivot below
FROM StockQuote
INNER JOIN CTE
ON StockQuote.xDate = CTE.xDate
INNER JOIN #TempSymbolList
ON StockQuote.xSymbol = #TempSymbolList.TempSymbol;
July 22, 2012 at 1:18 pm
tobinare (7/22/2012)
J Livingston SQL (7/22/2012)
one idea...not performance testedThank's, I like your solution and I never would have thought of it.
its probably only one of many ways.....only you can judge the performance.
sometimes I find it easier to break the code out into separate blocks and reassemble...many call this "divide and conquer"
pls post findings on performance......what volume of data are you dealing with?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 22, 2012 at 2:09 pm
J Livingston SQL (7/22/2012)
tobinare (7/22/2012)
J Livingston SQL (7/22/2012)
one idea...not performance testedThank's, I like your solution and I never would have thought of it.
its probably only one of many ways.....only you can judge the performance.
sometimes I find it easier to break the code out into separate blocks and reassemble...many call this "divide and conquer"
pls post findings on performance......what volume of data are you dealing with?
I'll have about 3-4million records total in the StockQuote table with the table having several more columns of data than the sample I posted. The CTE table we create, will have about 10-30 thousand. I searched extensively on the internet prior to posting on ssc and did not find any alternatives. I tried adding an "IS NOT NULL" in a WHERE in the dynamic pivot statement but no luck. My priority is to find something that works as I am very much a newbie when it comes to SQL.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply