sql help

[Giacomo Vernoni]

If anybody is interested, the solution I found is GROUP_CONCAT:

SELECT
    name,
    surname,
    town,
    ifnull(GROUP_CONCAT(red SEPARATOR ''), '') AS red,
    ifnull(GROUP_CONCAT(green SEPARATOR ''), '') AS green,
    ifnull(GROUP_CONCAT(blue SEPARATOR ''), '') AS blue,
    ifnull(GROUP_CONCAT(yellow SEPARATOR ''), '') AS yellow
FROM tableName
GROUP BY name, surname, town;

Regards,
Giacomo



On 12 mag 2010, at 18:52, Giacomo Vernoni wrote:

> 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
>>
>
> _______________________________________________
> Valentina mailing list
> Valentina at lists.macserve.net
> http://lists.macserve.net/mailman/listinfo/valentina
>