CODEWITHSUNDEEP
mysqlsql
A123321
A123321

Reputation: 837

SQL: Select column as <another column's value>

I have a table

ID | LanguageID | Text
1  | 1          | Abc
1  | 2          | Def

I want to select data so that all rows with the same ID are grouped into one row and each text is selected into a column with the name of its LanguageID. In this particular example the result would be

[ID: 1, 1: 'Abc', 2: 'Def']

Can this be done? How? Thank you

I am using MySQL.

EDIT: Seems like the easiest thing to do is to write a script to merge the rows outside of the database.

EDIT2: I don't know what answer to accept since none works or meets the requirements above.

Upvotes: 3

Views: 360

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1270181

If you have a fixed number of language ids, then you can do this as:

select id,
       max(case when LanguageId = 1 then text end) as "1",
       max(case when LanguageId = 2 then text end) as "2",
       max(case when LanguageId = 3 then text end) as "3"
from t
group by id;

If you don't know the language ids in advance, then you need to use dynamic SQL to construct the query and then prepare and execute it.

If languageid is dynamic:

select @s = concat('select id',
                   group_concat(concat(', max(case when LanguageId = ',
                                       LanguageId,
                                       ' then text end) as "',
                                       LanguageId, '"'
                                      )
                               ),
                   ' from t group by id'
                  )
from (select distinct LanguageId from t) t;

PREPARE stmt1 FROM @s;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;

Upvotes: 1

leonardeveloper
leonardeveloper

Reputation: 1853

Check this out hope this is what you're looking for: http://sqlfiddle.com/#!2/159927/13 

    select id, group_concat(concat(languageid, ' ',text))
    from t
    group by id

Upvotes: 0

echo_Me
echo_Me

Reputation: 37233

     select id, group_concat(languageid,':', text) as result
     from table1
     group by id

demo

Upvotes: 0

juergen d
juergen d

Reputation: 204844

select id, group_concat(languageid), group_concat(text)
from t
group by id

SQLFiddle demo

or

select id, group_concat(languageid, ':', text)
from t
group by id

SQLFiddle demo

Upvotes: 0

Related Questions