Dynamic pivot with nulls using count() and CTE when columns are unknown

Question

I am soo close! I have a survey and the client wants to be able to see reports using various weights for the answers. SQLFiddle only allows 2008 and up but in 2005 I am getting these results:

Lesson wt count -> | w1_5 | w2 | w3 | w5_5 | w6_5 | N
-----------------------------------------------------
q2                 | 1.5  | 2  | 3  | 5.5  | 6.5  | 9

Obviously they should be counts and not weights.

Furthermore the only record being returned happens to be a row that has 0 nulls.

I have made an SQLFiddle with a non-dynamic example of my expected outcome at the bottom.

I am not sure what the difference is that is making my dynamic query not sum and the non-dynamic one does.

Here is my previous work (1,2,3)

---UPDATE---FINAL SOLUTION---

Posted here For those who are interested.

Answer 1

Your dynamic sql inner joins each derived table, and you use one derived table for each weight. Some weights do not exist, so the result is empty result set. If you replace inner with left join, you will see your data, but the weight values, not the counts. To be honest, I don't understand why are you doing the joins in the first place. Why don't you generate a pivot query which would resemble pivot example you displayed at the bottom of the fiddle?

So, replace JOIN with LEFT JOIN:

SELECT -- Weight as the alias for join uniqueness
    @join_List = @join_List + ' 
LEFT JOIN 
    (
        SELECT lessonid, weight, count(weight) AS weightcnt 
        FROM tblResultsChoices 
        GROUP BY lessonid,weight
    ) w' + REPLACE(weight,'.','_') + ' 
ON (w' + REPLACE(weight,'.','_') + '.lessonid = weight_count.lessonid AND w' + REPLACE(weight,'.','_') + '.weight = ' + weight + '' +')'
    ,@select_List = @select_List + 
        ',isnull(w' + REPLACE(weight,'.','_') + '.weightcnt , 0) as w' + REPLACE(weight,'.','_') + ''
    ,@pivot_List = COALESCE(@pivot_List + ',', '') + '[' + weight + ']'
FROM Weights 

Oh, about Sql Fiddle: if I understood what I was told, you are supposed to delimit each sentence with semicolon if you want it in same batch. Didn't try it yet, though.

UPDATE: been playing around a bit more. You are generating select w1.weight where it should read w1.weightcnt. As you can see in code snippet above, replace .weight in @select_list part with .weightcnt and it will work. One question still remains: why aren't you building the same pivot query as working example?