alternative query output format

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

ASP.NET

alternative query output format

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-07-11 : 06:25:11

Kevin writes "I'm trying to convert an ASP application to .net and I am having some problems with binding a datatable to an object. The output of the query i'm using in ASP is looped through to generate the HTML table. I can't quite figure out how to do this with .net.

Here is something similar to my problem:
Say I have the output of an inventory of shirts:


Color	Size	Price	sku
red	s	10.00	123
red	m	11.00	456
red	l	12.00	789
blue	s	12.00	012
blue	m	13.00	234
blue	l	14.00	567

Is there any way to get the output as one row per color that looks like this:


	s	m	l
Red	10.00	11.00	12.00
blue	12.00	13.00	14.00

The first row s,m,l I can hard code on the ASP page so I don't need to return the first row. There always going to be the same. However, the prices and and colors are variable depending on who you are connected as. If i use the 'select * from shirts' as the query for both and bind the radiobuttonlist to the results and the datatextfield = price and it repeats horizontally 3x it works fine. But like i said the color may change so i would like to return those as well with the query. Is this possible? I would like to not run a second query ('select distinct color where user = x') to return just the colors and set them as a datatable on the left. But I suppose that's always an option.
Any help would be greatly appreciated."

jhermiz

3564 Posts

Posted - 2005-07-11 : 09:08:11

Try this:



Select t1.Color, MAX(t2.Small) AS SMALL, MAX(t2.Medium) AS Medium, MAX(t2.Large) AS Large FROM
Table1 t1 INNER JOIN 
(SELECT Color, CASE WHEN SomeSize='s' THEN Price END AS Small,
CASE WHEN SomeSize='m' THEN Price END AS Medium,
CASE WHEN SomeSize='l' THEN Price END AS Large
FROM Table1) t2 ON t2.Color=t1.Color 
GROUP BY t1.Color

Change the column names to fit your needs.

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-07-11 : 09:16:14

here's lots of fun stuff for you to review regarding cross tabs, old ASP, and newer .NET.

http://weblogs.sqlteam.com/jeffs/archive/2005/05/15/5175.aspx

However, if you really have constants such that the column headers will always be those 3 values, then a technique like what Jon has demonstrated will be the way to go. If the columns are dynamic or change from time to time, then take a look at the various techniques in the link I gave (read all of the links from there as well).

You'll know more about cross tabs than you ever wanted to know!

Jon's query can be shortened a little and the join eliminated:


select 
  Color,
  MAX(case when someSize='s' then Price else 0 end) as S,
  MAX(case when someSize='m' then Price else 0 end) as M,
  MAX(case when someSize='l' then Price else 0 end) as L
from
  YourTable
group by
  Color

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources