convert column values to rows

Question

uid   pid   mail   value
 1     78    27     Nairobi
 2     78    27     Milimani
 3     78    27     Criminal
 4     78    27     1427932800

I have a DB table above and only need the 'value' column values. I want to have the column values display in rows (not comma separated) for a cross-tab report. my ideal result would be:

        **Nairobi  Milimani  Criminal  1427932800**

The matching 'pid' and 'mail' means that the corresponding 'value' is from a single submission and a change in pid and mail (not captured here) is a new submission!

so how do I write an sql for converting the 'value' column values to row values? any help much appreciated.

'Pivot' has not really helped or i'm probably doing it wrongly.!!

This may be a duplicate of: http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string/194887#194887 — mwoelk, Jul 07 '15 at 20:56
(Oracle) ListAgg, WM_CONCAT... (mySQL) [group_concat](https://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat) — xQbert, Jul 08 '15 at 16:00

FutbolFan · Answer 1 · 2015-07-08T15:56:17.413

In SQL Server, you could easily pivot your columns something like this:

select 
     pvt.[Nairobi],
     pvt.[Milimani],
     pvt.[Criminal],
     pvt.[1427932800] 
from 
(select * from test) t
PIVOT
(
count(uid) for [value] in ([Nairobi],[Milimani],[Criminal],[1427932800]) 
        --change the count function and column to match your needs
) as pvt;

Also, as you may see above, you would need to use some kind of aggregate function to use pivot. Hope this helps!

SQL Fiddle Demo

UPDATE

After re-reading your question, I figured that you might be instead looking for something like this:

SELECT 
     ltrim(substring(t.concat_values, 1, charindex(' ', t.concat_values, 1))) AS 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)))) AS 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)))) AS third_col
    ,ltrim(substring(t.concat_values, dbo.GetNthCharacterOccurrence(t.concat_values, ' ', 3), len(t.concat_values) - dbo.GetNthCharacterOccurrence(t.concat_values, ' ', 3)+1)) AS fourth_col
FROM (
    SELECT DISTINCT stuff((
                SELECT ' ' + t2.[value]
                FROM test t2
                WHERE t1.pid = t2.pid
                    AND t1.mail = t2.mail
                FOR XML path('')
                ), 1, 1, '') AS concat_values
    FROM test t1
    ) t;

SQL Fiddle Demo 2

The trick for this method is to initially create a comma separated list of values using the STUFF function with XML Path. Then, break the string based on the position of the string separator which in this case I used space (' '). To find the nth occurrence of space, I "borrowed" a function that was written by Tavis Lovell originally in a blog. To split the values into multiple columns, I used substring, charindex and the user defined function above as needed.

hi Rookie... have tried the script and am having a problem with the FOR XML path('') ), 1, 1, '') AS concat_values FROM test t1 ) t; the XML path is giving a 1064 error — polycarp royal, Jul 13 '15 at 13:01
@polycarproyal I believe you will need to change the above statement to a mysql query instead. I initially provided the answer thinking you were running this on SQL Server. — FutbolFan, Jul 13 '15 at 13:45
@polycarproyal Please check out my new answer with MySql equivalent query. — FutbolFan, Jul 13 '15 at 14:35
@rookie sorry for bothering again but how do I now apply it for unknown amount of data(I have forms submitting to this DB table) and the data is enormous so converting it at one go would really be ideal. Just be able to pick all data in the table ...thanks again..! — polycarp royal, Jul 13 '15 at 15:35
In that case, you will need to look for more of a dynamic solution. — FutbolFan, Jul 13 '15 at 17:43
@ Rookie I solved it by only adding "GROUP BY" the 'mail' column(to your MySQL script) which in my DB has a relationship with my form's page submission DB table. — polycarp royal, Jul 14 '15 at 12:31

score 0 · Accepted Answer · answered Jul 13 '15 at 14:35

MySQL Version

Using group_concat and substring_index functions, you could easily convert your string values into multiple columns like this:

SELECT 
     SUBSTRING_INDEX(SUBSTRING_INDEX(concat_values, ',',1), ',', -1) as first_col,
     SUBSTRING_INDEX(SUBSTRING_INDEX(concat_values, ',',2), ',', -1) as second_col,
     SUBSTRING_INDEX(SUBSTRING_INDEX(concat_values, ',',3), ',', -1) as third_col,
     SUBSTRING_INDEX(SUBSTRING_INDEX(concat_values, ',',4), ',', -1) as fourth_col

FROM (
     SELECT GROUP_CONCAT(value) as concat_values
    FROM test t1  --change this to match your table name
    ) t;

MySQL Demo

convert column values to rows

2 Answers2

Linked