a too long query MAX(CASE WHEN

Hello

I am using an allready Full database MS SQL 2000

my 3 tables -->

Report :
ReportID (PK)
RName
RValue

Product :
PName
Category
ReportID (FK)

Infos :
IComments
IVaLue

my query (to get a new table with only columns, or a .NETcollection) -->

SELECT

Report.ReportID AS RID,
Report.RName AS RN,
Report.RValue AS RV,

Infos.Commentar AS IC,

MAX(CASE WHEN Product.Category = 50 THEN Product.PName END) AS P50,
MAX(CASE WHEN Product.Category = 54 THEN Product.PName END) AS P54,
MAX(CASE WHEN Product.Category = 78 THEN Product.PName END) AS P78,
MAX(CASE WHEN Product.Category = 540 THEN Product.PName END) AS P540,
MAX(CASE WHEN Product.Category = 1421 THEN Product.PName END) AS P1421

FROM

Report INNER JOIN Product ON Report.ReportID = Product.ReportID
LEFT OUTER JOIN Infos ON Report.RValue = Infos.IValue

WHERE (Report.ReportID = 10)

GROUP BY Report.ReportID, Report.RName, Report.RValue, Infos.IComments

Report.ReportID = Product.ReportID --> Primary Key to Foreign Key
Report.RValue = Infos.IValue --> only on full text (100 char)

they are not indexed

in Product can be a few million of lines, a few 10.000 in Report, about 1000 in Infos

it can be very long
how can i do it in a better way ? (of course I cannot change the structure of tables, another aplication is using it)

thank youHi

This is some sort of odd pivot but I don't understand what your problem is or what you would like to accomplish. Please could you elaborate?|||on 3 tables i have columns or rows , i want to get only columns
MAX(CASE WHEN Product.Category = 1421 THEN Product.PName END) AS P1421 makes a column from a row|||It does indeed. So what is the problem? :)

EDIT - oh hang on - do you mean you want zero rows??|||i want to find a better way if exists|||You could try a UNION and see if that works any better.

SELECT Report.ReportID AS RID,
Report.RName AS RN,
Report.RValue AS RV,
Infos.Commentar AS IC,
Product.PName AS P50,
'' AS P54,
'' AS P78,
'' AS P540,
''AS P1421
FROM
Report INNER JOIN Product ON Report.ReportID = Product.ReportID
LEFT OUTER JOIN Infos ON Report.RValue = Infos.IValue
WHERE Category = 50
UNION
SELECT Report.ReportID AS RID,
Report.RName AS RN,
Report.RValue AS RV,
Infos.Commentar AS IC,
'' AS P50,
Product.PName AS P54,
'' AS P78,
'' AS P540,
''AS P1421
FROM
Report INNER JOIN Product ON Report.ReportID = Product.ReportID
LEFT OUTER JOIN Infos ON Report.RValue = Infos.IValue
WHERE Category = 54
....
....
??
Also - your original query you posted is not what you are using since it is syntactically incorrect. You might want to consider some indexing too.

HTH|||you think a union will be really faster ?|||you think a union will be really faster ?I think it may be faster - there are circumstances where UNIONS can be quicker than a "single" statement. It is just a suggestion... it removes the processing of aggregates so may improve performance there.

If the seperate queries would not return duplicate results (or you don't care if it does) you could speed it up further by using UNION ALL.|||ok i try it

thank you|||I think it may be faster - there are circumstances where UNIONS can be quicker than a "single" statement. It is just a suggestion... it removes the processing of aggregates so may improve performance there.

If the seperate queries would not return duplicate results (or you don't care if it does) you could speed it up further by using UNION ALL.

Just FYI,
Two basic rules for combining the result sets of two queries with UNION are:

The number and the order of the columns must be identical in all queries.
The data types must be compatible|||You could try a UNION and see if that works any better.

SELECT Report.ReportID AS RID,
Report.RName AS RN,
Report.RValue AS RV,
Infos.Commentar AS IC,
Product.PName AS P50,
'' AS P54,
'' AS P78,
'' AS P540,
''AS P1421
FROM
Report INNER JOIN Product ON Report.ReportID = Product.ReportID
LEFT OUTER JOIN Infos ON Report.RValue = Infos.IValue
WHERE Category = 50
UNION
SELECT Report.ReportID AS RID,
Report.RName AS RN,
Report.RValue AS RV,
Infos.Commentar AS IC,
'' AS P50,
Product.PName AS P54,
'' AS P78,
'' AS P540,
''AS P1421
FROM
Report INNER JOIN Product ON Report.ReportID = Product.ReportID
LEFT OUTER JOIN Infos ON Report.RValue = Infos.IValue
WHERE Category = 54
....
....
??
Also - your original query you posted is not what you are using since it is syntactically incorrect. You might want to consider some indexing too.

HTH

UNION ALL if no dupes.|||As mentioned in my next post :)