Results 1 to 3 of 3
  1. #1
    Join Date
    May 2005
    Posts
    119

    Unanswered: Pivot error -- driving me CRAZY!

    select*from
    (
    select
    r.result
    ,year(od.collectdate)as yr
    ,cs.site
    , cs.longitude, cs.latitude
    from smsu.results r
    join smsu.orderdetails od on r.samplenumber=od.samplenumber
    join smsu.customersites cs on od.site=cs.site
    where
    month([collectdate])in(7,8,9)
    and
    r.test='Chlorine (total) field-C'
    and r.resultstatus=3
    groupby r.result,year(od.collectdate),cs.site, cs.longitude, cs.latitude
    ) as sql
    PIVOT(min([result])FOR [yr])p <-- This is line 18
    --------------------------------
    Result:
    Msg 102, Level 15, State 1, Line 18
    Incorrect syntax near ')'.


  2. #2
    Join Date
    May 2005
    Posts
    119
    OK got this... had to put in specific years in the FOR statement..
    yr
    in([2007]))p

    BUT! I want it to be dynamic.. ie:automatically populate to ALL the years in the above query. Is this possible? It HAS to be possible!!!

  3. #3
    Join Date
    May 2005
    Posts
    119
    Just thought I'd follow-up and post the solution:

    Here's the final SQL to accomplish the dynamic pivot query that I wanted. REdiculous that this is something Access could have done in a matter of minutes, took me ALL DAY to come up with this. ugggggg!

    DECLARE @columns VARCHAR(8000)
    Declare @query nvarchar(4000)
    SEt @columns =STUFF((SELECTconvert(nvarchar(7),'],['+convert(char(4),year(od.collectdate)))
    from smsu.orderdetails od where
    year(od.collectdate)*1>=2003
    groupbyconvert(nvarchar(7),'],['+convert(char(4),year(od.collectdate)))
    orderbyconvert(nvarchar(7),'],['+convert(char(4),year(od.collectdate)))

    FORXMLPATH('')),1,2,'')+']'


    set @query ='
    select * from
    (
    select
    r.result
    , year(od.collectdate) as yr
    ,cs.site
    , cs.longitude, cs.latitude
    from smsu.results r
    join smsu.orderdetails od on r.samplenumber=od.samplenumber
    join smsu.customersites cs on od.site=cs.site
    where
    month([collectdate]) in (7,8,9)
    and r.test=''Chlorine (total) field-C''
    and r.resultstatus=3
    group by r.result, year(od.collectdate),cs.site, cs.longitude, cs.latitude
    ) as sql
    PIVOT (min([result]) FOR yr in('
    + @columns +'))p'
    execute(@query)

Posting Permissions

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