Thursday, 25 April 2013

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