Hello,

I have a (big) table which is not normalized, but for i need at the
moment i think
that's no problem. Indeed, what i would like to do is to select the
name field and
the note. The problem is that i want to display the note in 2
different columns.
the first columns will show the number (count) of time that a certain
note (e.g note=4)
appears for a certain name and in the other column the same thing but
for a different note value.

so each column noteX will display the number of time that the note
with the value X appears for each name


for example, to the following table:


Name | note | job | city | id |
----------------------------------------
john | 4 | jb1 | hamb | 1 |
john | 5 | jb2 | hamb | 2 |
john | 5 | jb3 | hamb | 3 |
john | 5 | jb4 | hamb | 4 |
Mark | 4 | jb1 | mun | 5 |
Mark | 4 | jb2 | mun | 6 |
Mark | 4 | jb5 | mun | 7 |
Mark | 5 | jb1 | mun | 8 |
peter | 5 | jb3 | berl | 9 |
peter | 5 | jb5 | berl | 10 |
frank | 4 | jb6 | v.form | 11 |
frank | 5 | jb3 | v.form | 12 |
frank | 5 | jb2 | v.form | 13 |

the result should be:

Name | note5 | note4 |
-------------------------
john | 3 | 1 |
Mark | 1 | 3 |
peter | 2 | 0 |
frank | 2 | 1


How should be the right SQL command to show the data i
want? :chomp:

Thanks a lot.

Rui Dias
(E-Mail address blocked: See forum rules)

On Thu, 28 Feb 2008 11:39:06 +0100, rui dias <rldias@gmail.com> wrote:
> I have a (big) table which is not normalized, but for i need at the
> moment i think
> that's no problem. Indeed, what i would like to do is to select the
> name field and
> the note. The problem is that i want to display the note in 2
> different columns.
> the first columns will show the number (count) of time that a certain
> note (e.g note=4)
> appears for a certain name and in the other column the same thing but
> for a different note value.
>
> so each column noteX will display the number of time that the note
> with the value X appears for each name
>
>
> for example, to the following table:
>
>
> Name | note | job | city | id |
> ----------------------------------------
> john | 4 | jb1 | hamb | 1 |
> john | 5 | jb2 | hamb | 2 |
> john | 5 | jb3 | hamb | 3 |
> john | 5 | jb4 | hamb | 4 |
> Mark | 4 | jb1 | mun | 5 |
> Mark | 4 | jb2 | mun | 6 |
> Mark | 4 | jb5 | mun | 7 |
> Mark | 5 | jb1 | mun | 8 |
> peter | 5 | jb3 | berl | 9 |
> peter | 5 | jb5 | berl | 10 |
> frank | 4 | jb6 | v.form | 11 |
> frank | 5 | jb3 | v.form | 12 |
> frank | 5 | jb2 | v.form | 13 |
>
> the result should be:
>
> Name | note5 | note4 |
> -------------------------
> john | 3 | 1 |
> Mark | 1 | 3 |
> peter | 2 | 0 |
> frank | 2 | 1
>
>
> How should be the right SQL command to show the data i
> want? :chomp:

Not straight forward at all, here's some reading:
http://rpbouman.blogspot.com/2005/10...-in-mysql.html

> (E-Mail address blocked: See forum rules)

What forum? This is usenet.
--
Rik Wasmus

On Thu, 28 Feb 2008 11:46:33 +0100, Rik Wasmus
<luiheidsgoeroe@hotmail.com> wrote:
<snip crosstab>

>> How should be the right SQL command to show the data i
>> want? :chomp:
>
> Not straight forward at all, here's some reading:
> http://rpbouman.blogspot.com/2005/10...-in-mysql.html

This one might be better:
http://dev.mysql.com/tech-resources/...ard/index.html
--
Rik Wasmus

rui dias <rldias@gmail.com> wrote in
<85ad7903-ebcc-462b-8d34-104ffecd2557@s8g2000prg.googlegroups.com>:
> I have a (big) table which is not normalized, but for i
> need at the moment i think
> that's no problem. Indeed, what i would like to do is to
> select the name field and
> the note. The problem is that i want to display the note
> in 2 different columns.
> the first columns will show the number (count) of time
> that a certain note (e.g note=4)
> appears for a certain name and in the other column the
> same thing but for a different note value.
>
> Name | note5 | note4 |
> -------------------------
> john | 3 | 1 |
> Mark | 1 | 3 |
> peter | 2 | 0 |
> frank | 2 | 1

Unless I'm missing something, this is trivial:

mysql> SELECT Name,SUM(IF(note=5,1,0)) AS
note5,SUM(IF(note=4,1,0)) AS note4 FROM johnmark GROUP BY
Name;
+-------+-------+-------+
| Name | note5 | note4 |
+-------+-------+-------+
| frank | 2 | 1 |
| john | 3 | 1 |
| Mark | 1 | 3 |
| peter | 2 | 0 |
+-------+-------+-------+
4 rows in set (0.01 sec)

You could also use self-joins instead of IF()s for clarity.

> How should be the right SQL command to show the data i
> want? :chomp:

":chomp:"?

> (E-Mail address blocked: See forum rules)

"Forum rules"? This is a Usenet newsgroup.

--
When all you have is a transformation engine, everything
looks like a tree.