Post reply

How do I create a Pivot Table with multiple pivot columns ?

  • April 4, 2009 at 10:02 pm

    #155511

    Hi Guys, I am using SQL 2005 and IIS and have run ito an issue that I am sure someone with more understanding of TSQL will be able to solve in a jiffy.

    I have a Table:

    CREATE TABLE [dbo].[Assessments](

    [AssessmentGuid] [uniqueidentifier] NOT NULL,

    [TaskCode] [uniqueidentifier] NOT NULL,

    [AssessmentLevel] [nchar](10) NOT NULL,

    [AssessmentOnSer] [nchar](15) NULL,

    [AssessmentDate] [datetime] NOT NULL,

    [AssessmentDueDate] [datetime] NOT NULL,

    Now each entry is a new Assessment at which a member is given a level, a done date and a due date. There are only 5 level availibe.

    What I want to do is pass it a Members Ser # and have it return a table of ann Tasks done listing the relevant levels and done and due dates as one row.

    I can get it to give me either due date or done date but can't get it to do both at once. I am sure there has to be a way but it is beyond me.

    The following returns the MAX AssessmentDate of Assesement Level in one row for each TaskCode

    SELECT * FROM (SELECT TaskCode, AssessmentLevel, AssessmentDate, AssessmentOnPmkeys FROM Assessments)

    PIVOT (MAX(AssessmentDate)

    FOR [AssessmentLevel] IN ([TDM],,[SCM],[IND],[RegAuth])) AS PivotTable

    WHERE AssessmentOnSer = '123456'

    Now if only I could work out how to make it return MAX AssessmentDate and the corresponding AssessmentDueDate of each level in one row for each TaskCode.

    Any assistance would be greatly appreciated.

  • April 4, 2009 at 10:16 pm

    #972130

    1, B, SUP, Memb1, 4/3/2009 10:38:01 PM, 1/1/1900 12:00:00 AM

    2, B, TDM, Memb1, 4/3/2009 11:53:15 PM, 1/1/1900 12:00:00 AM

    3, B, SCM, Memb1, 4/3/2009 11:53:15 PM, 1/1/1900 12:00:00 AM

    4, B, IND, Memb1, 4/3/2009 11:53:15 PM, 1/1/1900 12:00:00 AM

    5, B, RegAuth, Memb1, 4/3/2009 11:53:15 PM, 1/1/1900 12:00:00 AM

    And I would like to return

    B, Memb1, TDMDate, TDMDueDate, SUPDate, SUPDoneDate, SCMDate, SCMDueDate, INDDate, INDDueDate, ReagAuthDate, RegaAuthDueDate

    The above code example gived me

    B, Memb1, TDMDueDate, TDMDate, SUPDate, SCMDate, INDDate, ReagAuthDate

    B, Memb1, SUPDoneDate, TDMDate, SUPDate, SCMDate, INDDate, ReagAuthDate

    B, Memb1, SCMDueDate, TDMDate, SUPDate, SCMDate, INDDate, ReagAuthDate

    B, Memb1, INDDueDate, TDMDate, SUPDate, SCMDate, INDDate, ReagAuthDate

    B, Memb1, RegaAuthDueDate, TDMDate, SUPDate, SCMDate, INDDate, ReagAuthDate

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply