Can we use a sql data field as column name instead?

First a query

SELECT * FROM mytable WH开发者_C百科ERE id='1'

Gives me a certain number of rows For example

id             |       context      |      cat     |    value
1                     Context 1            1           value 1
1                     Context 2            1           value 2
1                     Context 1            2           value 3
1                     Context 2            2           value 4

Now my problem instead of receiving the result in such way I want it is this way instead

id      |   cat    |    Context 1     |  Context 2   
1            1           value 1          value 2       
1            2           value 3          value 4    

---

@updated - see comment # in code block 3 (MySQL) which was meant for debugging only from the console or HeidiSQL

What you are looking for is called pivoting. You have not stated what DBMS this is on, but for SQL Server, look in Books Online for the PIVOT operator - that could work if the categories are fixed. If not, then you need dynamic sql, something like

declare @sql nvarchar(max);

-- generate the column names
select @sql = coalesce(@sql + ',', '') + QuoteName(context)
from (select distinct context from mytable) T;

-- replace the column names into the generic PIVOT form
set @sql = REPLACE('
select id, cat, :columns:
from (select id, cat, context, value From mytable) p
pivot (max(value) for context in (:columns:)) pv',
':columns:', @sql)

-- execute for the results
exec (@sql)

Test data

create table mytable (id int, context varchar(10), cat int, value varchar(20))
insert mytable select 1 ,'Context 1', 1 ,'value 1'
insert mytable select 1 ,'Context 2', 1 ,'value 2'
insert mytable select 1 ,'Context 1', 2 ,'value 3'
insert mytable select 1 ,'Context 2', 2 ,'value 4'

The above is for SQL Server. For MySQL use this instead, which follows the same technique.

SELECT @sql := NULL;
SELECT @sql := group_concat(
    'max(case when context=''',
    replace(context,'''',''''''),
    ''' then value end) `',
    replace(context,'`','``'),
    '`')
from (select distinct context from mytable) T;
SELECT @sql := concat(
    'select id, cat,',
    @sql,
    ' from mytable group by id, cat');
# SELECT @sql; # commented out, PHP sees this as the first result
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Based on your test data, plus two curlies (quote and backquote):

create table mytable (id int, context varchar(10), cat int, value varchar(20));
insert mytable select 1 ,'Context 1', 1 ,'value 1';
insert mytable select 1 ,'Context 2', 1 ,'value 2';
insert mytable select 1 ,'Context 1', 2 ,'value 3';
insert mytable select 1 ,'Context 2', 2 ,'value 4';
insert mytable select 1 ,'Context''3', 1 ,'quote';
insert mytable select 1 ,'Context`4', 2 ,'backquote';

---

SELECT  md.id, md.cat, m1.value AS context1, m2.value AS context2
FROM    (
        SELECT  DISTINCT id, cat
        FROM    mytable
        WHERE   id = 1
        ) md
LEFT JOIN
        mytable m1
ON      m1.id = 1
        AND m1.cat = md.cat
        AND m1.context = 'context 1'
LEFT JOIN
        mytable m2
ON      m2.id = 1
        AND m2.cat = md.cat
        AND m2.context = 'context 2'

---

SELECT t1.id, t1.cat, t1.value as 'Context 1', t2.value as 'Context 2'
FROM mytable t1, mytable t2
WHERE t1.id = t2.id AND t1.cat = t2.cat and t1.context = 'Context 1' and t2.context = 'Context 2'

works if every ID/cat combination has both context 1 and context 2.