SQL experts - filtering down and across to create a 2 dimensional set of data.

Factual Questions
1

This one has come up on occasions and I’ve always resorted to a simple query that breaks down the data in list form instead of a two dimensional table.

Basically I am querying a table of transactions (bets)

Each bet has a track, and a pool type (win place show exacta etc…)

I want to be able to create a table with the programs down the side, and the pool types across the top.

Problem is the pool types are not fields (there isn’t a ‘win’ field or a ‘exacta’ field) but rather they are values in a ‘pool type’ field)

So the rubbish way of doing it is to create a grouped query of sums where the programs and pool types are all going down (with a single column for amounts)

but it would be nice if programs went down the side, and pool types were column headers.
I’m sure it’s very simple, I just can’t figure out how to do it efficiently without ending up with some monsterous SQL code.

2

What type of SQL is it? Oracle is my main beast. Could you post a snippet of sample data? I am not sure it I quite get it.

3

I’ll give you a simulation of the relevant fields of the table from which I am getting information…
program,pool type,amount
a,win,12
a,exacta,18
a,place,2
b,exacta,4
b,show,60
a,place,13
a,show,1
c,win,8
c,place,6
I want a table like the following…

program,win,place,show,exacta
a,12,15,1,18
b,0,0,60,4
c,8,6,0,0

4

It sounds like you want a Cross Tabulation. SQL Server 2005 has the new PIVOT feature designed for this. Shagnasty can help if it’s Oracle.

5

Is this too slow?



SELECT programtype,
(SELECT Count(*) FROM [table] WHERE pooltype = 'win' AND programtype = programtype) AS win,
(SELECT Count(*) FROM [table] WHERE pooltype = 'place' AND programtype = programtype) AS place,
(SELECT Count(*) FROM [table] WHERE pooltype = 'show' AND programtype = programtype) AS show,
(SELECT Count(*) FROM [table] WHERE pooltype = 'exacta' AND programtype = programtype) AS exacta
FROM [table]

Just off the top of my head. I’m not an SQL speed expert, though.

6

It’s MS SQL.

7

That’s the kind of thing I had in mind for the long winded way of getting it, but considering the size of the database it might be quite slow. I’m just googling ‘pivot’ at the moment,.

8

If you don’t have 2005, the inelegant way I’d do it is copy the results into Excel, and do the pivot there, which is pretty simple and quick.

9

It doesn’t look like my SQL server has the pivot feature as described on the internet.

it does suggest the following though, which I am trying with my specific fields and table…

SELECT Year,
SUM(CASE Quarter WHEN 1 THEN Amount ELSE 0 END) AS Q1,
SUM(CASE Quarter WHEN 2 THEN Amount ELSE 0 END) AS Q2,
SUM(CASE Quarter WHEN 3 THEN Amount ELSE 0 END) AS Q3,
SUM(CASE Quarter WHEN 4 THEN Amount ELSE 0 END) AS Q4
FROM [table]
GROUP BY Year
GO

10

That’ll work, as long as your column names don’t change … The advantage of the PIVOT, of course, is that if new values are added to the table that sources the columns, the query results will automatically include them.

You might want to double-check your SQL Server. My company’s install of MS-SQL Server 2005 gives me error messages when I put PIVOT commands into the query designer, telling me that it’s not supported. But when I run the query, it works just fine. :dubious: