mysql - Convert column values to row values -

  **uid         pid    mail   value**       1          78     27     nairobi       2          78     27     milimani       3          78     27     criminal       4          78     27     1427932800 

i have db table above , need 'value' column values. want have column values display in rows (not comma separated) cross-tab report. ideal result be:

        **nairobi  milimani  criminal  1427932800** 

the matching 'pid' , 'mail' means corresponding 'value' single submission , change in pid , mail (not captured here) new submission!

so how write sql converting 'value' column values row values? appreciated.

'pivot' has not helped or i'm doing wrongly.!!

in sql-server can use pivot.

create table #test (     [uid] int,     pid int,     mail int,     value nvarchar(60) ) insert #test values  (1,          78,     27,     'nairobi'), (2,          78,     27,     'milimani'), (3,          78,     27,     'criminal'), (4,          78,     27,     '1427932800') 

this example of static pivot:

select [nairobi], [milimani], [criminal], [1427932800] (     select value     #test ) x pivot ( max(value) value in ([nairobi], [milimani], [criminal], [1427932800]) ) piv drop table #test 

and example of dynamic pivot:

declare @cols nvarchar(max),         @query  nvarchar(max)  select @cols = stuff((select ',' + quotename(value)                      #test                     group value, [pid]                     order [pid]             xml path(''), type             ).value('.', 'nvarchar(max)')          ,1,1,'')  set @query = n'select ' + @cols + n'               (                 select value                 #test             ) x             pivot             (                 max(value)                 value in (' + @cols + n')             ) p '  exec sp_executesql @query;