SEO

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?

"Yaron" <yaroni@gmail.com> wrote in message
news:1107267206.063304.159240@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...%40127.0. 0.1

Simon

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.

"Yaron" <yaroni@gmail.com> wrote in message
news:1107267206.063304.159240@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

"Andy O'Neill" <aon14nocannedmeat@lycos.co.uk> wrote in message news:vx6Md.161$89.17@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