SQL rows into columns

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
mad_phpq
Forum Commoner
Posts: 85
Joined: Fri Apr 27, 2007 5:53 am

SQL rows into columns

Post 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
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Post 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.
(#10850)
User avatar
nathanr
Forum Contributor
Posts: 200
Joined: Wed Jun 07, 2006 5:46 pm

Post 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
mad_phpq
Forum Commoner
Posts: 85
Joined: Fri Apr 27, 2007 5:53 am

Re: SQL rows into columns

Post by mad_phpq »

brilliant. Thanks nathan!

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

Thanks for your help!
mad_phpq
Forum Commoner
Posts: 85
Joined: Fri Apr 27, 2007 5:53 am

Re: SQL rows into columns

Post 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.
Post Reply