Thread: Query Problem
View Single Post
  #4 (permalink)  
Old October 20th, 2003, 01:34 PM
Jeff Mason Jeff Mason is offline
Friend of Wrox
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post

I answered your specific original question, but you then changed the rules. :(

Your table design leaves a lot to be desired. You need to read up on database normalization, as your encoding of information within character positions of the table columns violates so-called first normal form. In addition, you seem intent on representing a lot of information procedurally, rather than expressing it explicitly in the data itself. For example, apparently character positions 3 and 4 of the 'fromnode' actually means an intersection number with a specific string representation depending on some unknown mapping between those character values and the intersection value itself (e.g. - '13' means 'IP-11'?? How did you know that?) You should represent that relationship explicitly somewhere.

In my opinion you need to stop what you are doing, put down your coding pencil and do some research, get some education and learn about some of these design principles, or you are going to get in a lot of trouble.

That having been said, what you are trying to do looks like a cross-tab query. SQL really isn't very good at doing this sort of thing, as this is really a reporting issue best handled at the client. You can do it in SQL, but I don't think you're going to like what needs to be done. I know I don't. :D

You will have to use repeated CASE expressions to get what you want (assuming I understand what you want). First, you'll have a CASE expression to provide the value for the 'Intersection No' column according to your mysterious mapping:
    WHEN '01' THEN 'IP-1'
    WHEN '12' THEN 'IP-9'
then you'll need a CASE expression for each of the 8 columns. I can't figure out what your EASTBOUND, WESTBOUND, etc. mapping is so I'll just make something up. I'll assume EBU corresponds a value of 8 in the low order digits of both nodes, and I'll assume a 6 in the 'from' and 2 in the 'to' nodes means EBL, etc:
--EBU column
SUM(CASE WHEN fromnode%10=8 AND tonode%10=8 THEN volume ELSE 0 END) as EBU,
--EBT column
SUM(CASE WHEN fromnode%10=6 AND tonode%10=2 THEN volume ELSE 0 END) as EBT
...(continue on for the other 6 column definitions
GROUP BY SUBSTRING(fromnode,3,2)
the idea is that you sum either the volume column value or a 0 depending on whether the from and to node values correspond to the column value being generated.

You really should consider redesigning your database to explicitly state the intersection value relationships, and then you should also consider doing this sort of reporting via an appropriate client front-end.

Good luck.

Jeff Mason
Custom Apps, Inc.
Reply With Quote