SQL To Have Matrix (Three Tables Producing Results Set with Variable Rows & Columns)

Hi all,

Let me preface this by saying that I'm certainley not after free work and if this subject turns out to be an in depth complex answer, I'm happy to hire someone!

I'm developing a website for boat charters and the rates for charter vary based on time (e.g. Spring, Summer, Xmas, etc).  Typically I have simple had a table like this:


with a row for each boat which contained the rates for each period.

Obviously this has limitations and so I'd like the time periods to be editable by the customer so I created three tables:




I then loop through these in my scripting language to generate a table but I'm not going to be able to sort these results by different time ranges, etc.

So my question is, is there a better way?  If not, presumably I can create the result set in a SP and make it sortable on each column that way?

Thanks in advance for any help

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Hi Bob.

Mainly, I see that you must to type for evrey range/boat/year a rate , probably a lot of times.
But, you can set a main price per boat, and then a percent of the boat price per temporade.

Boat No. 1  -- > $50 Normal Time.
   For Spring -- > 25% added
   For Winter -- > 50% on discount of original price.
   By San Valentin -- > 300 % added,
and so on...

So you won change every time range the price, just only you must change boat price!

I hope this help you,
Kevin CrossChief Technology OfficerCommented:
I am not sure I am understanding what the challenge is...i.e., why can't you sort? It would probably be best to show the current result set and then your desired results in comparison. That way, we may be able to help you better. Without more information, I can say you can use PIVOT with SQL 2008 to get from the normalized tables to a display that is denormalized back to one row per boat with the rates going horizontally. You can just use a rank of the ranges, like range 1, 2, 3, ..., n. So range 1 for one boat can be different dates from another boat, but the rate for each is in the same column to signify that is the first range. Or you can explode the ranges back to individual months and have the PIVOT columns be January, February, ..., December.
Okidoki, look this query, and check the alias and column location (1,2,3...n)

select R.* ,B.[Boat Name],D.[Start Date], D.[Finish Date]
from Rates R inner join
        Boats B on R.boatid = B.boatid  inner join
       [Date Ranges] D on R.rangeid = D.rangeid
-- Then , you can order by any column of any table in the next line.
-- Just use the alias.columnName, or the query column display order number.

order by R.RangeID
-- or use :  order by 1,3
-- or use : order by Year(d.[start date]) desc

Just you must pick what column you need from your from block.

Hope this clear your dudes!
Bye ,
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Mango-ManAuthor Commented:
Hi both,

Many thanks for the responses.  To answer your immediate questions:

1) The customer definately wants to enter a boat price for every boat / time period (they cannot simply offer a discount for specific time periods).

2) With my current solution I cannot sort because the resulting HTML table is built up using the scripting language from nested recordsets, here is the VBSCRIPT to build the table:

SET vesselsRS = readOnlyConn.execute("SELECT * FROM CHARTER_vessels ORDER BY vesselName",,adCmdText)
SET rateDateRangesRS = readOnlyConn.execute("SELECT * FROM CHARTER_rateDateRange ORDER BY startDate",,adCmdText)
RESPONSE.WRITE(" 	<table border=""0"" cellspacing=""0"" class=""dataTbl"">")
	RESPONSE.WRITE(" 	<th>Vessel Name</th>")
	WHILE NOT rateDateRangesRS.EOF
		RESPONSE.WRITE(" 	<td>" & rateDateRangesRS("rateDateRangeName") & "</td>" & vbCrLf)
	RESPONSE.WRITE(" 	</tr>")

		RESPONSE.WRITE(" 	<tr>")
		RESPONSE.WRITE(" 	<th>" & vesselsRS("vesselName") & "</th>" & vbCrLf)
		SET rateDateRangesRS = readOnlyConn.execute("SELECT * FROM CHARTER_rateDateRange ORDER BY startDate",,adCmdText)
		WHILE NOT rateDateRangesRS.EOF
			RESPONSE.WRITE(" 	<td>")
			SET ratesRS = readOnlyConn.execute("SELECT rateCost, rateName FROM CHARTER_rates WHERE vesselID = " & vesselsRS("vesselID") & " AND rateDateRangeID = " & rateDateRangesRS("rateDateRangeID"),,adCmdText)
			IF NOT ratesRS.EOF THEN RESPONSE.WRITE(ratesRS("rateCost"))
			RESPONSE.WRITE("</td>" & vbCrLf)
		RESPONSE.WRITE(" 	</tr>" & vbCrLf)
RESPONSE.WRITE(" 	</table>")

Open in new window

If I can instead build the entire result set in an SP that would be great.

Dalsom - many thanks for the code, I will check it out tomorrow!  (late here now)

This solution adds a view which will
- simplify wriiting queries
- enable you to tweak results of queries by modifying the view instead of each query

create view boats_dates_rates as
select b.boat_name,
from boats b,
     date_ranges d,
     rates r
where b.boatid = r.boatid
  and r.rangeid = d.rangeid;

-- sample query for start dates Jan. - Feb.
select * from boats_dates_rates
where startdate between to_date('01/01/12','mmddyy')
                and to_date('02/27/12','mmddyy')
order by startdate, ratecost;

Mango-ManAuthor Commented:
Hi everyone,

Apologies for the long delay, I must also apologize for not being clear in what I'm trying to achieve.  I suspect that PIVOT may be the answer but I can't find examples of PIVOT that don't use an aggregate function, so to try and make it clear

I have three tables:

CHARTER_vessels (the boats)

vesselID    vesselName
--------    ----------
1           The Titanic
2           The Pinafore
3           The Black Pearl

CHARTER_rateDateRange (seasons basically)

rateDateRangeID     rateDateRangeName
---------------     -----------------
1                   Spring 2012
2                   Summer 2012
3                   Fall 2012

CHARTER_rates (the price for each boat in each season)

vesselID     rateDateRangeID      rateCost
--------     ---------------      --------
1            1                    434
1            2                    445
1            3                    231
2            1                    675
2            2                    545
2            3                    768
3            1                    543
3            2                    654
3            3                    658

Open in new window

And the output I'm trying to achieve is that the rates for each boat appear in the column for each season, like this:

vesselName         Spring 2012     Summer 2012     Fall 2012
----------         -----------     -----------     ---------
The Titanic        434             445             231
The Pinafore       675             545             768
The Black Pearl    543             654             658

Open in new window

Really sorry if I wasted anyone's time with my lack of clarity (and lack of knowledge!)

Thanks again

Mango-ManAuthor Commented:
(and obviously I would like to be able to sort the result set by the different columns if possible!)

Any points are much appreciated

SharathData EngineerCommented:
Can you check this.
declare @CHARTER_vessels  table (vesselID int, vesselName nvarchar(50))
insert @CHARTER_vessels values
(1,           'The Titanic'),
(2,           'The Pinafore'),
(3,           'The Black Pearl')

declare @CHARTER_rateDateRange table (rateDateRangeID int, rateDateRangeName nvarchar(50))
insert @CHARTER_rateDateRange values 
(1,                   'Spring 2012'),
(2,                   'Summer 2012'),
(3,                   'Fall 2012')

declare @CHARTER_rates table (vesselID int, rateDateRangeID int, rateCost int)
insert @CHARTER_rates values 
(1,            1 ,                   434),
(1,            2 ,                   445),
(1,            3 ,                   231),
(2,            1 ,                   675),
(2,            2 ,                   545),
(2,            3 ,                   768),
(3,            1 ,                   543),
(3,            2 ,                   654),
(3,            3 ,                   658)

select * from (
select cv.vesselName,crd.rateDateRangeName,cr.rateCost
  from @CHARTER_rates cr
  join @CHARTER_vessels cv on cr.vesselID = cv.vesselID
  join @CHARTER_rateDateRange crd on cr.rateDateRangeID = crd.rateDateRangeID) t1
PIVOT (SUM(rateCost) FOR rateDateRangeName IN ([Spring 2012],[Summer 2012],[Fall 2012])) AS P
vesselName	Spring 2012	Summer 2012	Fall 2012
The Black Pearl	543	654	658
The Pinafore	675	545	768
The Titanic	434	445	231

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mango-ManAuthor Commented:
Many thanks - sorry for the delay
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.