Has anyone had success using it yet? We've been playing around with it and maybe we're doing it wrong, but so far we can't get it to produce anything useful. I mean, it pivots but we can't get it to do anything useful, like group an aggregate or anything.
Why don't you post some DDL, data, code, and desired output? Then we can all be on the same page.
OK...I have a view that I'm querying that counts the number of applications in the system and groups them according to their type. What I want to do is pivot by type. There are four distinct types of applications and I'd like to pivot them and display them by week. Basically if it were a crosstab I want the week start value to be on the left and the groups to be on the top.
If I just do a straight select from the view, this is what I get (columns are week, type, number of apps, and team from l to r):
2006-07-01 00:00:00 MLI 1 INBPYM
2006-06-17 00:00:00 MLI 1 NBBH2
2006-07-22 00:00:00 Term 1 YNB5
2006-06-17 00:00:00 Term 1 PART5NB
2006-07-29 00:00:00 Term 1 NBSS
2006-06-24 00:00:00 Term 1 YNB6
2006-05-20 00:00:00 Term 1 NB2N
2006-07-08 00:00:00 Term 1 NB3N
2006-07-15 00:00:00 MLI 1 NBSS
2006-05-27 00:00:00 PTA 1 NBBH
2006-07-08 00:00:00 MLI 1 NBSS
Yesterday it simply wasn't aggregating the data; basically it would print one line for each case when I wanted it to add them all together. Today the code isn't even working at all:
SELECT WeekStart, [MLI] AS MLI, [Term] AS Term, [Other Protection] AS 'Other Protection', [PTA] AS PTA
(SELECT WeekStart, [Grouping], Cases, Team
FROM vwFEWeeklySummary) p
COUNT(Cases) FOR [Grouping] IN ([MLI],[Term],[Other Protection],[PTA])
ORDER BY WeekStart;