sql help

[Giacomo Vernoni]

Thank you Ruslan.

Now I'm half way...

I had:

| name   | surname | town     | color  |
| John   | Smith   | New York | Blue   |
| John   | Smith   | New York | Red    |
| Steve  | Jobs    | Rome     | Blue   |
| Steve  | Jobs    | Rome     | Green  |
| Carl   | Mirren  | London   | Yellow |

I want:

| name   | surname | town     | red | green | blue | yellow |
| John   | Smith   | New York | 1   |       | 1    |        |
| Steve  | Jobs    | Rome     |     | 1     | 1    |        |
| Carl   | Mirren  | London   |     |       |      | 1      |

I have now:

| name   | surname | town     | red | green | blue | yellow |
| John   | Smith   | New York | 1   |       |      |        |
| John   | Smith   | New York |     |       | 1    |        |
| Steve  | Jobs    | Rome     |     | 1     |      |        |
| Steve  | Jobs    | Rome     |     |       | 1    |        |
| Carl   | Mirren  | London   |     |       |      | 1      |


Now I'm trying to consolidate multiple rows into one.
Suggestions welcome.

Cheers,
Giacomo


On 12 mag 2010, at 16:19, Ruslan Zasukhin wrote:

> On 12/5/10 5:12 PM, "Giacomo Vernoni" <giacomo at way-out.it> wrote:
>
>> Hi list,
>> not Valentina related, but Valentina developed :-)
>>
>> I'll explain my request with an example.
>>
>> Look at table1:
>>
>> | name   | surname | town     | color  |
>> | John   | Smith   | New York | Blue   |
>> | John   | Smith   | New York | Red    |
>> | Steve  | Jobs    | Rome     | Blue   |
>> | Steve  | Jobs    | Rome     | Green  |
>> | Carl   | Mirren  | London   | Yellow |
>>
>> I created table2 and I want to populate this new table with data from
>> table1, but in this way:
>>
>> | name   | surname | town     | red | green | blue | yellow | white |
>> black |
>> | John   | Smith   | New York | 1   |       | 1    |        |
>> |       |
>> | Steve  | Jobs    | Rome     |     | 1     | 1    |        |
>> |       |
>> | Carl   | Mirren  | London   |     |       |      | 1      |
>> |       |
>>
>> Does anybody know how to do this in SQL?
>>
>> A huge thank to the person who can solve my problem!
>
> Something like this I think:
>
> SELECT
>    name,
>    surname,
>    town,
>
>    CASE color
>        WHEN 'Red' THEN 1
>        ELSE  0
>    END CASE AS 'red'
>
>    CASE color
>        WHEN 'Green' THEN 1
>        ELSE  0
>    END CASE AS 'green'
>
> FROM
>    T1
>
>
> -- 
> Best regards,
>
> Ruslan Zasukhin
> VP Engineering and New Technology
> Paradigma Software, Inc
>
> Valentina - Joining Worlds of Information
> http://www.paradigmasoft.com
>
> [I feel the need: the need for speed]
>
>
> _______________________________________________
> Valentina mailing list
> Valentina at lists.macserve.net
> http://lists.macserve.net/mailman/listinfo/valentina
>