Pivot in MS SQL

In response to the previous post - cast nText to varchar mid-query.


select  pvt.[48] as slideContent
, pvt.[49] as link
, pvt.[51] as startDate
, pvt.[52] as endDate
, pvt.[53] as setPriority
from (select 
recordid
, fieldid
, convert(varchar(8000),content) as myContent
from Moodle.dbo.mdl_data_content
join moodle.dbo.mdl_data_records on mdl_data_content.recordid = mdl_data_records.id
where dataid = 12 and approved = 1) as ps

PIVOT
(
min(myContent)
for fieldid in
([48], [49], [51],[52],[53])
)as pvt

NOTES: DataID and pvt[x] needs to be updated to correct values for live table, but otherwise this query is exactly what is required to extract the full dataset.

Additional development required - limit the returned data to display ONLY those records where startDate < Now < End Date

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.