By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,419 Members | 1,612 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,419 IT Pros & Developers. It's quick & easy.

sql query syntax

P: n/a
Hi all,
I have the following tables:

T1
==
ID Name
-- ----
1 Name1
2 Name2
T2
==
ID Color
-- ----
1 Color1
1 Color2
2 Color2
2 Color3

and I would like to get the following output (without duplicates):

ID Colors
-- ------
1 Color1, Color2
2 Color2, Color3

Is it possible?

Jul 23 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a

"Yaron" <ya****@gmail.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...
Hi all,
I have the following tables:

T1
==
ID Name
-- ----
1 Name1
2 Name2
T2
==
ID Color
-- ----
1 Color1
1 Color2
2 Color2
2 Color3

and I would like to get the following output (without duplicates):

ID Colors
-- ------
1 Color1, Color2
2 Color2, Color3

Is it possible?


What does T1 have to do with your output? You could use a cursor to go
through the T2 table, but this sort of presentational work is usually best
handled by a reporting tool or other front end.

http://www.aspfaq.com/etiquette.asp?id=5006
http://tinyurl.com/bib2
http://groups.google.ch/groups?hl=en...an%40127.0.0.1

Simon
Jul 23 '05 #2

P: n/a
This is called destroying First Normal Form. You do it in the front
end and not the database because it has to do with display.

You might want to learn what the Normal Forms are and why we use them
in SQL. You might want to learn how a tiered architecture works, too.

Jul 23 '05 #3

P: n/a
"Yaron" <ya****@gmail.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...
Hi all,
I have the following tables:

T1
==
ID Name
-- ----
1 Name1
2 Name2
T2
==
ID Color
-- ----
1 Color1
1 Color2
2 Color2
2 Color3

and I would like to get the following output (without duplicates):

ID Colors
-- ------
1 Color1, Color2
2 Color2, Color3

Is it possible?


You could possibly do this in sql by two unioned queries.
I've done this when I want 12 months data cross tabbed/pivotted in a crystal
report.

It'll get really messy really quickly if you have some with >2 colours
though.

I'd imagine the pivot stuff in sql 2005 might make this rather easier.

--
Regards,
Andy O'Neill
Jul 23 '05 #4

P: n/a
-P-
"Andy O'Neill" <ao***************@lycos.co.uk> wrote in message news:vx*************@fe3.news.blueyonder.co.uk...

<snip>
You could possibly do this in sql by two unioned queries.
I've done this when I want 12 months data cross tabbed/pivotted in a crystal report.


Why? That's so simple in standard ANSI SQL.

Given an overly simplified table definition for sales transactions:
CREATE TABLE Sales(
customerID integer not null
references Customers( customerID ),
productID integer not null
references Products( productID ),
saleDate date not null,
qty integer not null,
unitPrice numeric(11,2) not null,
totalSale as (qty * unitPrice),
primary key (customerID, productID, saleDate)
) ;

Then a query that produces a 12-month cross-tab of sales by product for a given year would be:

Select
productID,
productName,
sum((case when datepart( mm, saleDate ) = 1 then 1 else 0 end) * totalSale) as JanSales,
sum((case when datepart( mm, saleDate ) = 2 then 1 else 0 end) * totalSale) as FebSales,
sum((case when datepart( mm, saleDate ) = 3 then 1 else 0 end) * totalSale) as MarSales,
...
sum((case when datepart( mm, saleDate ) = 12 then 1 else 0 end) * totalSale) as DecSales
from
SALES
JOIN PRODUCTS
on Sales.productID = Products.productID
where
datepart( yy, saleDate ) = 2004
GROUP BY
productID, productName ;

Paul Horan
Sr. Architect
VCI Springfield, Mass
www.vcisolutions.com
Jul 23 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.