Query - select common data from one column and display in severalcolumns

[rui dias]

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?


Rui Dias
rldias@gmail.co m



Thanks a lot

[Mark]

Re: Query - select common data from one column and display in severalcolumns

select Name,
sum(case when note=5 then 1 else 0 end) as note5,
sum(case when note=4 then 1 else 0 end) as note4
from mytable
group by Name

[czytacz]

Re: Query - select common data from one column and display in severalcolumns

not simple,
but possible is to use pivot/unpivot

[rui dias]

Re: Query - select common data from one column and display in severalcolumns

On Feb 28, 11:56 am, Mark <markc...@hotma il.comwrote:

select Name,
sum(case when note=5 then 1 else 0 end) as note5,
sum(case when note=4 then 1 else 0 end) as note4
from mytable
group by Name

Hello Mark,

Thanks a lot for your answer.

It is really nice!
I didn't know the command "case when filed=value then X else Y end"

Really nice! ;)

[Dan Guzman]

Re: Query - select common data from one column and display in several columns

I didn't know the command "case when filed=value then X else Y end"

Note that a SQL CASE is an expression rather than a command (as in some
other languages). You can use it where expressions are allowed in SQL.

--
Hope this helps.

Dan Guzman
SQL Server MVP

http://weblogs.sqlteam.com/dang/

"rui dias" <rldias@gmail.c omwrote in message
news:c67bdcc0-abe2-4f25-a4e8-bcd5151830ff@60 g2000hsy.google groups.com...

On Feb 28, 11:56 am, Mark <markc...@hotma il.comwrote:

>select Name,
> sum(case when note=5 then 1 else 0 end) as note5,
> sum(case when note=4 then 1 else 0 end) as note4
>from mytable
>group by Name

>
Hello Mark,
>
Thanks a lot for your answer.
>
It is really nice!
I didn't know the command "case when filed=value then X else Y end"
>
Really nice! ;)

[Plamen Ratchev]

Re: Query - select common data from one column and display in several columns

Here is a version with the PIVOT operator in SQL Server 2005:

SELECT [name],
SUM([5]) AS note5,
SUM([4]) AS note4
FROM Foo
PIVOT (COUNT(note) FOR note IN ([5], [4])) AS P
GROUP BY [name];

HTH,

Plamen Ratchev

http://www.SQLStudio.com