sql help

[Ruslan Zasukhin]

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]