I have been stuck in a MS environment way too long and my thought process has devolved because of it.

I would have never bothered with trying to write dynamic SQL for something like this back in my Unix/Oracle days.

I would have written a shell script to auto-create and auto-run the SQL for me.

I broke out my very creaky PowerShell skills and wrote a script that use's Eirikur's code above and auto generates the aggregation section.

As this is not going to be a repeatable process, I don't mind doing some of the work by hand.

Here is the PowerShell code:

$strVersion = "1.20"

$strDivision = "Lab"

$strAgeGroup = "Adult"

$strFileName = $strDivision + "_" + $strAgeGroup + "_" + $strVersion + ".txt"

$strOutputFile = "C:\YOUR\FILE\STRUCTURE\HERE\" + $strFileName

out-file $strOutputFile -encoding ASCII

for ($i = 1; $i -le 400; $i++) {

$strSQL = ",MAX(CASE WHEN BD.SQV_RID = " + $i + " THEN BD.QuestionCode END) AS QuestioniCode_" + $i + "`n"

$strSQL = $strSQL + ",MAX(CASE WHEN BD.SQV_RID = " + $i + " THEN BD.QuestionText END) AS QuestionText_" + $i + "`n"

$strSQL = $strSQL + ",MAX(CASE WHEN BD.SQV_RID = " + $i + " THEN BD.AnswerCode END) AS AnswerCode_" + $i + "`n"

$strSQL = $strSQL + ",MAX(CASE WHEN BD.SQV_RID = " + $i + " THEN BD.Answer END) AS Answer_" + $i + "`n`r"

add-content $strOutputFile $strSQL

}

Later I will add in code to query each exam version for number of questions and meta data for file naming.