Help Doing a Query.

Help Doing a Query.

Post by Ale K » Wed, 30 Jul 2003 20:17:47



Let's Say i have a Database that stores results from basketball games.

i have a Table Called Matches, and a another Called Quarters , a Match can
have many Quarters.
(Please don't give me adivces about design because i'm giving this table as
example my DB is too big to explain an show )

MATCHES TABLE                                      QUARTERS TABLE
MatchID-----------(one)-------I                   QuarterID
Team1                                      I--(many)----MatchID
Team2
HighScorerPlayer
Date

how can i do a query or stored procedure that returns the list of matches
and a colum in each record with the concatenated Names of the HighScorers
with No Repeatings.
EX:

MATCHID       TEAM1            TEAM2         HIGHSCORERS
1                            1                       2
Jones,Jordan (Jordan can be High Scorer on 3 Periods but i only want the
name once)
2                            1                       3
Jordan
3                            1                     27
Oneal,Grant,Pippen
4                           27                     4                Dr J,
Bird
etc....

Thanks.
Alex.

 
 
 

Help Doing a Query.

Post by oj » Wed, 30 Jul 2003 20:41:01


This looks like a crosstab issue. You could check these out.

If you want to roll your own sql check out and if you know in advance the
number of columns in the result set, try:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q175574.

If you have a variable number of columns, try:
http://www.sqlmag.com/Articles/Index.cfm?ArticleID=15608

RAC at:
www.rac4sql.net

--
-oj
RAC v2.2 & QALite!
http://www.rac4sql.net


Quote:> Let's Say i have a Database that stores results from basketball games.

> i have a Table Called Matches, and a another Called Quarters , a Match can
> have many Quarters.
> (Please don't give me adivces about design because i'm giving this table
as
> example my DB is too big to explain an show )

> MATCHES TABLE                                      QUARTERS TABLE
> MatchID-----------(one)-------I                   QuarterID
> Team1                                      I--(many)----MatchID
> Team2
> HighScorerPlayer
> Date

> how can i do a query or stored procedure that returns the list of matches
> and a colum in each record with the concatenated Names of the HighScorers
> with No Repeatings.
> EX:

> MATCHID       TEAM1            TEAM2         HIGHSCORERS
> 1                            1                       2
> Jones,Jordan (Jordan can be High Scorer on 3 Periods but i only want the
> name once)
> 2                            1                       3
> Jordan
> 3                            1                     27
> Oneal,Grant,Pippen
> 4                           27                     4                Dr J,
> Bird
> etc....

> Thanks.
> Alex.


 
 
 

1. Help doing a cross tab query

I have the following query:

SELECT CustID,
               TransID,
               PrimeGLNo,
               SUM(TransAmt) TransAmt
FROM lcs203_process, LCSGLAccounts
WHERE lcs203_process.PrimeGLNo = LCSGLAccounts.PrimeGLNo
GROUP BY CustID, TransID, PrimeGLNo

The results look as follows:

110XXX     00000111111    5678888  100.00
110XXX     00000111111    5678890  200.00
110XXX     00000111111    5678895  300.00
...

Is there anyway I can use SQL to do a crosstab so my resultset looks as
follows:

                                             5678888    5678890     5678895
110XXX    00000111111    100.00        200.00        300.00

I need the column names to be the PrimeGLNo with little coding as possible.
I do not
want to have to create a table or do anyother hard coding.

Thanks in advance
S

2. Update Trigger

3. help figuring out why query doing full table scan

4. EOF on comm chanel during insert

5. Help - Simple SQL query doing full table scans

6. How to inser

7. Doing string compares in Foxpro Queries - help needed

8. Top 10 Signs you Need an Information System!

9. Caché from InterSystems - doing wh at other MV vendors should be doing

10. ADO thinks DBMS is done, but DBMS ain't done

11. Trigger doing UNLOAD to file OR calling Store Procedure doing the UNLOAD to file

12. Can this be done in one query?

13. Doing a find on a query - Delphi 1