sql - convert column values to rows -

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, pivot columns this:

select       pvt.[nairobi],      pvt.[milimani],      pvt.[criminal],      pvt.[1427932800]   (select * test) t pivot ( count(uid) [value] in ([nairobi],[milimani],[criminal],[1427932800])          --change count function , column match needs ) pvt; 

also, may see above, need use kind of aggregate function use pivot. hope helps!

sql fiddle demo

update

after re-reading question, figured might instead looking this:

select       ltrim(substring(t.concat_values, 1, charindex(' ', t.concat_values, 1))) first_col     ,ltrim(substring(t.concat_values, dbo.getnthcharacteroccurrence(t.concat_values, ' ', 1), (dbo.getnthcharacteroccurrence(t.concat_values, ' ', 2) - dbo.getnthcharacteroccurrence(t.concat_values, ' ', 1)))) second_col     ,ltrim(substring(t.concat_values, dbo.getnthcharacteroccurrence(t.concat_values, ' ', 2), (dbo.getnthcharacteroccurrence(t.concat_values, ' ', 3) - dbo.getnthcharacteroccurrence(t.concat_values, ' ', 2)))) third_col     ,ltrim(substring(t.concat_values, dbo.getnthcharacteroccurrence(t.concat_values, ' ', 3), len(t.concat_values) - dbo.getnthcharacteroccurrence(t.concat_values, ' ', 3)+1)) fourth_col (     select distinct stuff((                 select ' ' + t2.[value]                 test t2                 t1.pid = t2.pid                     , t1.mail = t2.mail                 xml path('')                 ), 1, 1, '') concat_values     test t1     ) t; 

sql fiddle demo 2

the trick method create comma separated list of values using stuff function xml path. then, break string based on position of string separator in case used space (' '). find nth occurrence of space, "borrowed" function written tavis lovell in blog. split values multiple columns, used substring, charindex , user defined function above needed.