a too long query MAX(CASE WHEN

[anselme]

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 you

[skhanal]

In SQL 2000 this is the only way you can pivot rows to columns, which is very limited as it becomes ugly if you have many categories or categories are not known in advance.

Can you do it in front end?

[anselme]

maybe with UNION

front end ?