SQL WTF. Yeah, another one
-
Say you have a table like this:
Person | Amount
-------------------------
Alice | 5
Bob | 10
Bob | 15
Chad | 20
David | 30
......
few thousand rows of that
And you wanted to get results in a certain format, for only certain values of "Person":
Total | Alice | Bob | Chad
--------------------------------------
50 | 5 | 25 | 20
Hopefully, your SQL would not look like this:
SELECT
IFNULL(SUM(IF(Person IN ("Alice", "Bob", "Chad"),Amount,NULL)),0) AS "Total",
IFNULL(SUM(IF(Provider="Alice",Amount,NULL)), 0) AS "Alice",
IFNULL(SUM(IF(Provider="Bob",Amount,NULL)), 0) AS "Bob",
IFNULL(SUM(IF(Provider="Chad",Amount,NULL)), 0) AS "Chad",
..... (more rows follow)
FROM table
-
Poor database design such as that means that crap SQL queries are needed.
-
IMO it's the job of the application to format the data as necessary, no need to abuse the database.
-
@alias said:
Poor database design such as that means that crap SQL queries are needed.
Also perhaps poor display design.
I mean, the data is stored in that direction, but the display of it is 90° rotated. Why not just keep the vertical layout?
-
I'm guessing this is probably access, but the basic idea looks a lot like the only way to do pivot tables in SQL Server prior to 2005. As to changing the application, well maybe its a 3rd part application which can't be changed?
-
Crosstab queries have traditionally been a pain in straight SQL. If this is MSSQL 2005, take a look at PIVOT, as it designed for this. If not, you could use dynamic SQL rather than that mess.
Unless this is a contrived example, I'm not sure I'd display the data this way. I can understand if the columns are constant (months, quarters, sales territories). I'd display the data another way or use the UI/Reporting module to format the data.
Larry
-
PIVOT is a nice addition to SQL, but it really only works for trivial examples. I can't think of any good repoting tool that would be happy with a variable number of columns being returned from the data source.
90% of the time pivoting is a UI issue. Do a GROUP BY query to get the data and then dynamically design the report taking into account the number of groups returned. The other 10% of the time can be taken care of by returning a pivoted result set and making a simple HTML table out of them or binding to a generic (no specific column definitions) DataGrid. If the query language doesn't do pivots, then dynamically build the query. And yes, it will look a lot like the given query.
-
@dhromed said:
I mean, the data is stored in that direction, but the display of it is 90° rotated. Why not just keep the vertical layout?
Or, indeed, run the query vertically, then append columns rather than appending rows to get the horizontal layout...
-
@random_garbage said:
@dhromed said:
I mean, the data is stored in that direction, but the display of it is 90° rotated. Why not just keep the vertical layout?
Or, indeed, run the query vertically, then append columns rather than appending rows to get the horizontal layout...
You know those fancy LCD monitors you can rotate?
That'd be [i]perfect[/i]! :)
-
There are actually books about exactly this type of query. For example:
An example query:
http://www.stephenforte.net/owDasBlog/PermaLink.aspx?guid=2b0532fc-4318-4ac0-a405-15d6d813eeb8
The benifit is, in some circumstances, you can generate reports in a single pass at the database.
-
Actually, that SELECT CASE..END, CASE...END, CASE...END, CASE...END, etc is the appropriate way to handle that in SQL Server <= 2000, and, as far as I know, Postgres or MySQL. Oracle probably has built in pivot support.
The only other option is to create the table, and then run N updates, one for each column. This is probably going to be slower than the first option, even if it looks prettier.
Amazon.com
