CASE when false query [modified]

foryou

Merci Ashfield. I used your solution : SELECT ID_STUDENT, CASE WHEN (CODE) = 1 THEN 'abs' ELSE CONVERT(varchar, [121]) END AS note1, CASE WHEN (CODE) = 1 THEN 'ASD' ELSE CONVERT(varchar, [122]) END AS note3, CASE WHEN (CODE) = 1 THEN 'ASD' ELSE CONVERT(varchar, [123]) END AS note3 FROM (SELECT ID_FIELD, ID_STUDENT, NOTE, CODE FROM EXAMEN) p PIVOT (sum(NOTE) FOR ID_FIELD IN ([121], [122], [123])) AS pvt I have not the problem of data type but I have the data displayed by double Here are my data TABLE examen Id_student---- id_feild ---- note----code 9306-------------121 ----------- 0-------1 9306-------------122---- -------4-------0 9306------------123----------- 12----- 0 9307------------121 ------------3------ 0 9307------------122------------ 0------ 1 9307------------123 ------------9------ 0 9308------------121------------ 12------0 9308------------122------------ 4------ 0 9308------------123------------ 9------0 following the requet résultatt 9306----NULL----NULL----NULL 9307----NULL----NULL----NULL 9308----NULL----NULL-----NULL 9306----NULL----4-------12 9307-----3------NULL----9 9308-----12-----4------9 9306-----ASD----ASD-----ASD 9307-----ASD----ASD-----ASD and this is what I want id_student---note1----note2---note3 9306---------ASD-------4---------12 9307---------3-------ASD -------9 9308--------12------4---- ------9 I dont know why please help me. Thanks

modified on Tuesday, February 17, 2009 6:32 AM

foryou · modified on Tuesday, February 17, 2009 6:32 AM

Hi! I think I have this result because we must specify that the ID_FIELD= 121 here:CASE WHEN (CODE) = 1 THEN 'abs' ELSE CONVERT(varchar, [121]) END AS note1, and ID_FIELD=122 here:CASE WHEN (CODE) = 1 THEN 'abs' ELSE CONVERT(varchar, [122]) END AS note2 how ? the case when accepts two conditions? thanks!

Wendelius

I don't if I follow the logic but if you drop away the code in pivot like this:

SELECT *
FROM (SELECT ID_FIELD,
ID_STUDENT,
NOTE
FROM EXAMEN) p
PIVOT (sum(NOTE) FOR ID_FIELD IN ([121], [122], [123])) AS pvt

you will get a result as wanted where note is 0 for the rows where code is 1 (is this always true?):

ID_STUDENT 121 122 123

9306 0 4 12
9307 3 0 9
9308 12 4 9

Now if you replace the zeros like:

SELECT ID_STUDENT,
CASE WHEN ([121]) = 0 THEN 'abs' ELSE CONVERT(varchar, [121]) END AS note1,
CASE WHEN ([122]) = 0 THEN 'ASD' ELSE CONVERT(varchar, [122]) END AS note2,
CASE WHEN ([123]) = 0 THEN 'ASD' ELSE CONVERT(varchar, [123]) END AS note3
FROM (SELECT ID_FIELD,
ID_STUDENT,
NOTE
FROM EXAMEN) p
PIVOT (sum(NOTE) FOR ID_FIELD IN ([121], [122], [123])) AS pvt

you will get:

ID_STUDENT note1 note2 note3

9306 abs 4 12
9307 3 ASD 9
9308 12 4 9

Is this getting you any closer to the result?

The need to optimize rises from a bad design.My articles[^]

foryou

Thank you very much Mika Wendelius you helped me a lot in this site :). for me code = 1 means that the student is abcent so his note= 0 and I have to replace 0 by 'abc' to differentiate between a student absent and another who is present and received 0.why I used code code_absence. I do not know if I come to explain my problem. Thnaks.

Wendelius

It didn't come out very pretty :) but could you use this for somekind of starting point. The idea is that the first query gets the records where the student is absent and the second gets those where the student isn't absent. then these are joined and columns are combined:

SELECT coalesce(a.ID_STUDENT, b.ID_STUDENT) as id_student,
coalesce(a.note1, b.note1) as note1,
coalesce(a.note2, b.note2) as note2,
coalesce(a.note3, b.note3) as note3
FROM
(SELECT ID_STUDENT,
CASE WHEN ([121]) = 0 THEN 'Abs' ELSE CONVERT(varchar, [121]) END AS note1,
CASE WHEN ([122]) = 0 THEN 'Abs' ELSE CONVERT(varchar, [122]) END AS note2,
CASE WHEN ([123]) = 0 THEN 'Abs' ELSE CONVERT(varchar, [123]) END AS note3
FROM (SELECT ID_FIELD, ID_STUDENT, NOTE
FROM EXAMEN WHERE code = 1) p
PIVOT (sum(NOTE) FOR ID_FIELD IN ([121], [122], [123])) AS pvt) a
full outer join
(SELECT ID_STUDENT,
CONVERT(varchar, [121]) AS note1,
CONVERT(varchar, [122]) AS note2,
CONVERT(varchar, [123]) AS note3
FROM (SELECT ID_FIELD, ID_STUDENT, NOTE
FROM EXAMEN WHERE code = 0) p
PIVOT (sum(NOTE) FOR ID_FIELD IN ([121], [122], [123])) AS pvt) b
on a.ID_STUDENT = b.ID_STUDENT

The result is:

id_student note1 note2 note3

9306 Abs 4 12
9307 3 Abs 9
9308 12 4 9

The need to optimize rises from a bad design.My articles[^]

foryou

Hi. Thank you a lot ,merci beaucoup Mika Wendelius it works :) :) .

Wendelius

You're welcome :)

The need to optimize rises from a bad design.My articles[^]