There isn't an exact equivalent to pivot queries in t-sql.
There is a pivot operator but it requires that you know and explicitly specify the pivoted columns when you're creating your code.
Just a heads up... Some one here will most assuredly either provide you with or point you to code that will allow you to do a "dynamic pivot"... Yes, it will allow you to pivot the data the way you're used to doing it in Access (without having
to specify the pivoted column names). The problem with dynamic pivots is the fact that they are useless. You can't put them in views, functions or stored procs and I'm not aware of any reporting software that can work with a variable number of input columns.
So, unless you simply want to copy & paste from SSMS to Excel, they aren't good for anything other than patting yourself on the back.
So... My 1st suggestion is to not pivot the data at all in SQL. Instead, pivot the data in whatever application you're using to display the data.
Since no one ever wants to hear suggestion #1, use aggregated case expressions. Based on the code you provided, it should look kinda like this...
SELECT
Eval1to4.evalOid,
Membershiptypemap.mappedvalue as membership,
SUM(CASE WHEN Eval1to4.answer = 1 THEN 1 END) AS Answer1,
SUM(CASE WHEN Eval1to4.answer = 2 THEN 1 END) AS Answer2,
SUM(CASE WHEN Eval1to4.answer = 3 THEN 1 END) AS Answer3,
SUM(CASE WHEN Eval1to4.answer = 4 THEN 1 END) AS Answer4,
SUM(CASE WHEN Eval1to4.answer = 5 THEN 1 END) AS Answer5
FROM
Eval1to4 e
JOIN Orders o
ON e.evalOid = o.oid
JOIN Members m
ON m.CID = o.cid
LEFT JOIN MembershipTypeMap mtm
ON m.MembershipStatus = mtm.membershipstatus
AND m.Membership = mtm.membershiptype
WHERE
o.program = 20141128
AND e.evalProgID = 20141128
GROUP BY
e.evalOid,
mtm.mappedvalue
#3... There is still the "PIVOT" operator. I personally don't use it because the aggregated case expressions are more flexible... But some people love them, so do a little Googleing and decide for yourself.
#4... Dynamic Pivot... If you're dying to try it, the interwabs are full of examples... or wait a bit and someone will post some for you.
HTH,
Jason
Other recent topics
