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
This blog covers my experiences and learning in my career. Any opinions expressed are mine and not necessarily those of my employer.
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.
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.
Subscribe to:
Posts (Atom)