Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2005
    Location
    Texas
    Posts
    100

    Unanswered: SQL 2005 Pivot operator?

    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.

  2. #2
    Join Date
    Nov 2004
    Posts
    128
    Why don't you post some DDL, data, code, and desired output? Then we can all be on the same page.

  3. #3
    Join Date
    Dec 2005
    Location
    Texas
    Posts
    100
    Quote Originally Posted by cfr
    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
    FROM
    (SELECT WeekStart, [Grouping], Cases, Team
    FROM vwFEWeeklySummary) p
    PIVOT
    (
    COUNT(Cases) FOR [Grouping] IN ([MLI],[Term],[Other Protection],[PTA])
    )
    AS pvt
    ORDER BY WeekStart;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •