[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 381
  • Last Modified:

Have results of subquery as column headings

I have a table of data which gives prices for many stocks over many periods of time.  It also records prices per exchange.  I would like a query to give the exchanges as column headings, the dates as row heading and the no. of stocks prices in the middle. For example

                   London,Frankfurt, Tokyo...
1st Jan        4564,6879,2379
2nd Jan      6897,3519,1297
.
.
.

The list of exchanges is in a separate table to the list of prices, but there is a one to many relationship between the exchange table and the price table  (there may be exchanges on the exchange table that are not on the price table, but not vice versa)

I'd also like to be able to choose how far back I want to go e.g only show how many stocks have priced in the last week.
0
AlHal2
Asked:
AlHal2
  • 4
  • 3
1 Solution
 
sas13Commented:
-- create test data
create table exch (id int, name varchar(10))
insert into exch (id, name) values (1, 'London')
insert into exch (id, name) values (2, 'Frankfurt')
insert into exch (id, name) values (3, 'Tokyo')
set dateformat dmy
create table price (id int identity(1,1), date datetime, exch_id int, price float)
insert into price (date, exch_id, price) values ('01/01/07', 1, 4564.0)
insert into price (date, exch_id, price) values ('01/01/07', 2, 6879.0)
insert into price (date, exch_id, price) values ('01/01/07', 3, 2379.0)
insert into price (date, exch_id, price) values ('02/01/07', 1, 6587.0)
insert into price (date, exch_id, price) values ('02/01/07', 2, 3591.0)
insert into price (date, exch_id, price) values ('02/01/07', 3, 8872.0)

-- test data
select * from exch
select * from price

-- select
declare @sql nvarchar(2000)
select @sql = 'select distinct p.date'
select @sql = @sql + ', isnull((select top 1 price from price where date=p.date and exch_id=' +
                    cast(id as varchar(5)) + '), 0) AS ' + name
from exch order by name
select @sql = @sql + ' from price p order by p.date'
exec sp_executesql @sql
0
 
AlHal2Author Commented:
Thanks for this.  I'm looking for the no. of prices for each exchange, not the actual prices themselves.  
Therefore I need some kind of grouping.
Also, the exchange field has the same name in the price table as it does in the exchange table.
Finally, there are 215 exchanges.  The result is over 8000 characters long which is not allowed.
0
 
AlHal2Author Commented:
one more thing.  I'd like to put in a filter to limit output to the last week  (relative dates rather than absolute)
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
sas13Commented:
-- try to use this procedure
CREATE PROCEDURE crosstab
@source varchar(1000), --between FROM and WHERE
@fields varchar(100)=null, --fields for "GROUP BY". If null, then result - 1 row by all records
@func varchar(100), -- function by type: sum(some_field), count... etc ... Not use * as argument
@pivot varchar(1000), -- rule for creating fields
@filtr varchar(1000)=null, -- conditions (after WHERE clause)
@into varchar(100)=null -- resulting table (if needed)

AS

DECLARE
@select VARCHAR(8000),
@sql VARCHAR(8000),
@sql1 VARCHAR(8000),
@sql2 VARCHAR(8000),
@sql3 VARCHAR(8000),
@sql4 VARCHAR(8000),
@sql5 VARCHAR(8000),
@sql6 VARCHAR(8000),
@sql7 VARCHAR(8000),
@sql8 VARCHAR(8000),
@sql9 VARCHAR(8000),
@sqlend VARCHAR(8000),
@delim VARCHAR(1),
@sqllen numeric(10),
@sqlnum numeric(10),
@pvalue VARCHAR(100),
@filtr1 varchar(1000)

SET NOCOUNT ON
SET ANSI_WARNINGS OFF

SET @filtr1=isnull(' AND '+@filtr, '')
EXEC ('SELECT DISTINCT ' + @pivot + ' as pivot INTO ##pivot FROM ' + @source + ' WHERE '
+ @pivot + ' Is Not Null '+ @filtr1)

SELECT @sql='',
@sql1='',
@sql2='',
@sql3='',
@sql4='',
@sql5='',
@sql6='',
@sql7='',
@sql8='',
@sql9='',
@select='SELECT '+isnull(@fields+',',''),  
@func=stuff(@func, len(@func), 1, ' END)')


SELECT top 1 @delim = CASE ISNUMERIC(pivot) WHEN 1 THEN '' ELSE '''' END FROM ##pivot
DECLARE cur_pivot_cursor scroll CURSOR FOR select pivot from ##pivot order by pivot  

OPEN cur_pivot_cursor

FETCH NEXT FROM cur_pivot_cursor
INTO @pvalue

SET @sqlnum = 1

WHILE @@FETCH_STATUS = 0

BEGIN

-- fields concatinations
SET @sql=@sql + '''' + convert(VARCHAR(100), @pvalue) + ''' = ' +
stuff(@func,charindex( '(', @func )+1, 0, 'CASE ' + @pivot +
' WHEN ' + @delim + convert(VARCHAR(100), @pvalue) + @delim + ' THEN ' ) + ', '

SET @sqllen = len(@sql)

-- check length string,
-- 1 passage
IF @sqllen > 7800 and @sqlnum = 1
BEGIN
SET @sql1 = @sql
SET @sqlnum = @sqlnum + 1
SET @sql = ''
SET @sqllen = 0
END

-- 2 passage
IF @sqllen > 7800 and @sqlnum = 2
BEGIN
SET @sql2 = @sql
SET @sqlnum = @sqlnum + 1
SET @sql = ''
SET @sqllen = 0
END

-- 3 passage
IF @sqllen > 7800 and @sqlnum = 3
BEGIN
SET @sql3 = @sql
SET @sqlnum = @sqlnum + 1
SET @sql = ''
SET @sqllen = 0
END

-- 4 passage
IF @sqllen > 7800 and @sqlnum = 4
BEGIN
SET @sql4 = @sql
SET @sqlnum = @sqlnum + 1
SET @sql = ''
SET @sqllen = 0
END

-- 5 passage
IF @sqllen > 7800 and @sqlnum = 5
BEGIN
SET @sql5 = @sql
SET @sqlnum = @sqlnum + 1
SET @sql = ''
SET @sqllen = 0
END

-- 6 passage
IF @sqllen > 7800 and @sqlnum = 6
BEGIN
SET @sql6 = @sql
SET @sqlnum = @sqlnum + 1
SET @sql = ''
SET @sqllen = 0
END

-- 7 passage
IF @sqllen > 7800 and @sqlnum = 7
BEGIN
SET @sql7 = @sql
SET @sqlnum = @sqlnum + 1
SET @sql = ''
SET @sqllen = 0
END

-- 8 passage
IF @sqllen > 7800 and @sqlnum = 8
BEGIN
SET @sql8 = @sql
SET @sqlnum = @sqlnum + 1
SET @sql = ''
SET @sqllen = 0
END

-- 9 passage
IF @sqllen > 7800 and @sqlnum = 9
BEGIN
SET @sql9 = @sql
SET @sqlnum = @sqlnum + 1
SET @sql = ''
SET @sqllen = 0
END

-- next string
FETCH NEXT FROM cur_pivot_cursor
INTO @pvalue

END

CLOSE cur_pivot_cursor
DEALLOCATE cur_pivot_cursor

DROP TABLE ##pivot

-- delete comma
IF len(@sql) > 0
SELECT @sql=left(@sql, len(@sql)-1)
ELSE
BEGIN
IF LEN(@sql9)>0
SELECT @sql9=left(@sql9, len(@sql9)-1)
ELSE IF LEN(@sql8)>0
SELECT @sql8=left(@sql8, len(@sql9)-1)
ELSE IF LEN(@sql7)>0
SELECT @sql7=left(@sql7, len(@sql7)-1)
ELSE IF LEN(@sql6)>0
SELECT @sql6=left(@sql6, len(@sql6)-1)
ELSE IF LEN(@sql5)>0
SELECT @sql5=left(@sql5, len(@sql5)-1)
ELSE IF LEN(@sql4)>0
SELECT @sql4=left(@sql4, len(@sql4)-1)
ELSE IF LEN(@sql3)>0
SELECT @sql3=left(@sql3, len(@sql3)-1)
ELSE IF LEN(@sql2)>0
SELECT @sql2=left(@sql2, len(@sql2)-1)
ELSE IF LEN(@sql1)>0
SELECT @sql1=left(@sql1, len(@sql1)-1)
END
SET @filtr1=isnull(' WHERE '+@filtr, '')
set @sqlend=isnull(' INTO '+@into,'')+' FROM '+@source+@filtr1+isnull(' GROUP BY '+@fields,'')

EXEC (@select+@sql1+@sql2+@sql3+@sql4+@sql5+@sql6+@sql7+@sql8+@sql9+@sql+@sqlend)
SET ANSI_WARNINGS ON
GO

-- max length of this query - 85 kb
-- example to execute:
exec crosstab
'your_table_name1 a INNER JOIN your_table_name2 b ON a.id=b.id',
'a.field1, b.field2',
'count(a.id)',
'right(left(convert(varchar,b.datetime_field,121),13),2)',
'b.datetime_field between ''20040701'' and getutcdate() and a.id > 1000',
'##temp'
 
--for my test data
exec crosstab
'exch e INNER JOIN price p ON e.id=p.exch_id',
'p.date',
'AVG(p.price)',
'e.name',
null,
null
0
 
AlHal2Author Commented:
I got these messages when I pasted into SQL 2005.

Msg 156, Level 15, State 1, Procedure crosstab, Line 51
Incorrect syntax near the keyword 'pivot'.
Msg 156, Level 15, State 1, Procedure crosstab, Line 52
Incorrect syntax near the keyword 'pivot'.
Msg 2812, Level 16, State 62, Line 4
Could not find stored procedure 'crosstab'.
Msg 2812, Level 16, State 62, Line 13
Could not find stored procedure 'crosstab'.
----------
Here are the lines that are being flagged.

Line 4 @func varchar(100), -- function by type: sum(some_field), count... etc ... Not use * as argument
Line 13 @sql VARCHAR(8000),
Line 51 SELECT top 1 @delim = CASE ISNUMERIC(pivot) WHEN 1 THEN '' ELSE '''' END FROM ##pivot

0
 
AlHal2Author Commented:
The price table has about 200,000,000 rows, so when I run select distinct I get all those dates.  Is it possible to create a table with 7 rows ie. the last 7 days in format yyyymmdd.  Today those rows would say

20070808
20070807
20070806
.
.
0
 
sas13Commented:
CREATE PROCEDURE crosstab
@source varchar(1000), --between FROM and WHERE
@fields varchar(100)=null, --fields for "GROUP BY". If null, then result - 1 row by all records
@func varchar(100), -- function by type: sum(some_field), count... etc ... Not use * as argument
@pivot varchar(1000), -- rule for creating fields
@filtr varchar(1000)=null, -- conditions (after WHERE clause)
@into varchar(100)=null -- resulting table (if needed)

AS

DECLARE
@select VARCHAR(8000),
@sql VARCHAR(8000),
@sql1 VARCHAR(8000),
@sql2 VARCHAR(8000),
@sql3 VARCHAR(8000),
@sql4 VARCHAR(8000),
@sql5 VARCHAR(8000),
@sql6 VARCHAR(8000),
@sql7 VARCHAR(8000),
@sql8 VARCHAR(8000),
@sql9 VARCHAR(8000),
@sqlend VARCHAR(8000),
@delim VARCHAR(1),
@sqllen numeric(10),
@sqlnum numeric(10),
@pvalue VARCHAR(100),
@filtr1 varchar(1000)

SET NOCOUNT ON
SET ANSI_WARNINGS OFF

SET @filtr1=isnull(' AND '+@filtr, '')
EXEC ('SELECT DISTINCT ' + @pivot + ' as [pivot] INTO ##pivot FROM ' + @source + ' WHERE '
+ @pivot + ' Is Not Null '+ @filtr1)

SELECT @sql='',
@sql1='',
@sql2='',
@sql3='',
@sql4='',
@sql5='',
@sql6='',
@sql7='',
@sql8='',
@sql9='',
@select='SELECT '+isnull(@fields+',',''),  
@func=stuff(@func, len(@func), 1, ' END)')


SELECT top 1 @delim = CASE ISNUMERIC([pivot]) WHEN 1 THEN '' ELSE '''' END FROM ##pivot
DECLARE cur_pivot_cursor scroll CURSOR FOR select [pivot] from ##pivot order by [pivot]  

OPEN cur_pivot_cursor

FETCH NEXT FROM cur_pivot_cursor
INTO @pvalue

SET @sqlnum = 1

WHILE @@FETCH_STATUS = 0

BEGIN

-- fields concatinations
SET @sql=@sql + '''' + convert(VARCHAR(100), @pvalue) + ''' = ' +
stuff(@func,charindex( '(', @func )+1, 0, 'CASE ' + @pivot +
' WHEN ' + @delim + convert(VARCHAR(100), @pvalue) + @delim + ' THEN ' ) + ', '

SET @sqllen = len(@sql)

-- check length string,
-- 1 passage
IF @sqllen > 7800 and @sqlnum = 1
BEGIN
SET @sql1 = @sql
SET @sqlnum = @sqlnum + 1
SET @sql = ''
SET @sqllen = 0
END

-- 2 passage
IF @sqllen > 7800 and @sqlnum = 2
BEGIN
SET @sql2 = @sql
SET @sqlnum = @sqlnum + 1
SET @sql = ''
SET @sqllen = 0
END

-- 3 passage
IF @sqllen > 7800 and @sqlnum = 3
BEGIN
SET @sql3 = @sql
SET @sqlnum = @sqlnum + 1
SET @sql = ''
SET @sqllen = 0
END

-- 4 passage
IF @sqllen > 7800 and @sqlnum = 4
BEGIN
SET @sql4 = @sql
SET @sqlnum = @sqlnum + 1
SET @sql = ''
SET @sqllen = 0
END

-- 5 passage
IF @sqllen > 7800 and @sqlnum = 5
BEGIN
SET @sql5 = @sql
SET @sqlnum = @sqlnum + 1
SET @sql = ''
SET @sqllen = 0
END

-- 6 passage
IF @sqllen > 7800 and @sqlnum = 6
BEGIN
SET @sql6 = @sql
SET @sqlnum = @sqlnum + 1
SET @sql = ''
SET @sqllen = 0
END

-- 7 passage
IF @sqllen > 7800 and @sqlnum = 7
BEGIN
SET @sql7 = @sql
SET @sqlnum = @sqlnum + 1
SET @sql = ''
SET @sqllen = 0
END

-- 8 passage
IF @sqllen > 7800 and @sqlnum = 8
BEGIN
SET @sql8 = @sql
SET @sqlnum = @sqlnum + 1
SET @sql = ''
SET @sqllen = 0
END

-- 9 passage
IF @sqllen > 7800 and @sqlnum = 9
BEGIN
SET @sql9 = @sql
SET @sqlnum = @sqlnum + 1
SET @sql = ''
SET @sqllen = 0
END

-- next string
FETCH NEXT FROM cur_pivot_cursor
INTO @pvalue

END

CLOSE cur_pivot_cursor
DEALLOCATE cur_pivot_cursor

DROP TABLE ##pivot

-- delete comma
IF len(@sql) > 0
SELECT @sql=left(@sql, len(@sql)-1)
ELSE
BEGIN
IF LEN(@sql9)>0
SELECT @sql9=left(@sql9, len(@sql9)-1)
ELSE IF LEN(@sql8)>0
SELECT @sql8=left(@sql8, len(@sql9)-1)
ELSE IF LEN(@sql7)>0
SELECT @sql7=left(@sql7, len(@sql7)-1)
ELSE IF LEN(@sql6)>0
SELECT @sql6=left(@sql6, len(@sql6)-1)
ELSE IF LEN(@sql5)>0
SELECT @sql5=left(@sql5, len(@sql5)-1)
ELSE IF LEN(@sql4)>0
SELECT @sql4=left(@sql4, len(@sql4)-1)
ELSE IF LEN(@sql3)>0
SELECT @sql3=left(@sql3, len(@sql3)-1)
ELSE IF LEN(@sql2)>0
SELECT @sql2=left(@sql2, len(@sql2)-1)
ELSE IF LEN(@sql1)>0
SELECT @sql1=left(@sql1, len(@sql1)-1)
END
SET @filtr1=isnull(' WHERE '+@filtr, '')
set @sqlend=isnull(' INTO '+@into,'')+' FROM '+@source+@filtr1+isnull(' GROUP BY '+@fields,'')

EXEC (@select+@sql1+@sql2+@sql3+@sql4+@sql5+@sql6+@sql7+@sql8+@sql9+@sql+@sqlend)
SET ANSI_WARNINGS ON
GO

-- for last 7 days
exec crosstab
'exch e INNER JOIN price p ON e.id=p.exch_id',
'p.date',
'AVG(p.price)',
'e.name',
'd.date between getdate()-8 and getdate()',
null
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now