Thursday, 25 April 2013

Pivots in mssql

This is not a completely thought out post, it's simply an attempt to get that part of my brain that processes information to work.

I've created a Moodle Database activity which only teachers can access. They will submit material which will then be pulled by a function within Moodle to create a slider on my Moodle Home page.

The information submitted to the database can be extracted using:

select *
from mdl_data_records
join mdl_data_contents on mdl_data_records.id = recordid
where dataid = 12 and approved = 1

That returns several rows for each submission to the database and this needs to be restructured. A simplified overview:

recordid | field | content
58 | 49 | This is a text submission
58 | 50 | http://www.link.url
58 | 51 | 1366888911
58 | 52 | 1369888911
58 | 53 | 3 - Priority Normal

This needs to be pivoted as follows:

Database | slideContent | Link | StartDate | EndDate | Priority
58 | This is a text submission | http://www.link.url 1366888911 1369888911 | 3 - Priority Normal

However, the values in content here are nText, and they need to be comparable.

That's where I got stuck. Will have lunch and hopefully a blood rush to the brain will give me the answers.