Page 1 of 1

SQL rows into columns

Posted: Tue Dec 11, 2007 5:01 am
by mad_phpq
Is it possible to search a table and pull the results into columns.

I have 5 distinct rows. And i would like to be able to move these disctint rows in colums and go down through the table and fill in the columns with the distinct rows and leave if the distinct row for that search blank if null.

ie

Code: Select all

USERID         |           COURSEID |             SCORMID |             ELEMENT |            VALUE
---------------------------------------------------
1               1                   1              status      incomplete
1               1                   1              time         0:0:12:23
1               1                   1              exit          suspend
into this

Code: Select all

USERID  |                                COURSEID |                       SCORMID |                    STATUS          |                 TIME            |                 EXIT
--------------------------------------------------------------------------------------------------
1              1                 1                incomplete       0:0:12:23     suspend

Posted: Tue Dec 11, 2007 11:51 am
by Christopher
You could sort by those unique first three fields and then use PHP to loop through the results and gather all the data for each unique new row.

Posted: Tue Dec 11, 2007 2:01 pm
by nathanr
it would appear to me that you are trying to patch up a problem rather than fix it completely.. to have the table you described in your first example, means that you are storing string values along with timestamps in the same column, surely it would make the most sence to alter your table schema to:

USERID | COURSEID | SCORMID | STATUS | TIME | EXIT

if however you are too far along int he project (or indeed this isn't your design) to make changes like that, then..

Code: Select all

SELECT md5(concat(userid,courseid,scormid)) as rid, userid,courseid,scormid, SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(element,'::::',`value` SEPARATOR '^^^^'),'^^^^',1),'::::',-1) as `status`, SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(element,'::::',`value` SEPARATOR '^^^^'),'^^^^',2),'::::',-1) as `time`, SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(element,'::::',`value` SEPARATOR '^^^^'),'^^^^',-1),'::::',-1) as `exit` FROM tablename group by rid
assuming that your real column names are userid, courseid, scormid, element, value and your table name is tablename

Re: SQL rows into columns

Posted: Mon Jan 21, 2008 4:51 am
by mad_phpq
brilliant. Thanks nathan!

Can you tell me what the purpose of the md5 hash is?

Thanks for your help!

Re: SQL rows into columns

Posted: Mon Jan 21, 2008 5:18 am
by mad_phpq
Also, I've decided since my first post that i only want to use two columns, status and time.

I want to skip over records that dont have these element fields i need (status and time) and then move the correct value into the right column.

status = cmi.core.lesson_status
time = cmi.core.total_time

USERID | ELEMENT | VALUE
---------------------------------------------------
1 | cmi.core.lesson_status | completed
1 | cmi.core.total_time | 00:00:42.62
1 | cmi.core.exit | suspend
2 | cmi.core.lesson_status | incomplete
2 | cmi.core.total_time | 00:00:12.32

to


USERID | STATUS | TIME
---------------------------------------------------
1 | completed | 00:00:42.62
2 | incomplete | 00:00:12.32

NB Also, i'm thinking i will have to order the element field so the status and time field come out in an order we can sort into the correct column.