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

    Unanswered: FOR XML PATH function... question...in regards to a pivot table question 8/15/12

    DECLARE @columns VARCHAR(8000)
    Declare @query nvarchar(4000)
    Set @columns =STUFF((SELECTDISTINCTTOP 100 PERCENT
    convert(char(8),'],['+convert(char(4),year(od.collectdate)))
    from smsu.orderdetails od where
    year(od.collectdate)*1>=2003
    FORXMLPATH('')),1,2,']')
    select @columns
    -----------------------------------------------------------------------
    Result:
    ][2006 ],[2010 ],[2005 ],[2009 ],[2012 ],[2003 ],[2011 ],[2004 ],[2007 ],[2008

    If I could just figure out how to move the ']' to the end instead of the beginning and remove the space to the right of the year.. anyone who can help?

    Why, oh why, aren't SQL PIVOT TABLES DYNAMIC?????? Would have made my life a whole heck of a lot easier today!

    Thanks in advance,
    Krista

  2. #2
    Join Date
    Apr 2012
    Posts
    213
    Try:

    Code:
    Set @columns = STUFF( (SELECT DISTINCT ',[' + CONVERT(CHAR(4), Year(od.collectdate)) + ']'
                           from smsu.orderdetails od 
                           where year(od.collectdate) >= 2003 
                           FOR XML PATH(''), TYPE).value('.', 'varchar(max)')
                           ,1, 1, '')
    Hope this helps.

  3. #3
    Join Date
    May 2005
    Posts
    119
    Thanks, it still didn't quite work because I needed to convert the year and the '[' to nvarchar(7).. that took out the spaces. I then put the last ']' at the end of the statement.

    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)

Tags for this Thread

Posting Permissions

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