Output to view using PIVOT

  • Hi,

    I am trying to output to a view in a certail format. I think I may be able to use something like the PIVOT command but am very unsure of the syntax.

    The tables are as follows:

    tblDeliverableNames

    ID Name

    1 Instruction

    2 Meeting Held

    3 Site Visit

    4 Report Submitted

    tblProject

    ID Name

    1 Project1

    2 Project2

    3 Project3

    4 Project4

    tblDeliverables

    ID ProjectID DeliverableNameID ForecastDate CompleteDate

    1 1 1

    2 1 2

    3 1 3

    4 1 4

    5 2 1

    6 2 3

    7 2 4

    8 3 1

    9 3 2

    10 3 4

    11 4 1

    12 4 2

    13 4 3

    14 4 4

    I would like to output this to a view with the following format:

    Headings: ProjectName, InstructionForecastDate, InstructionCompleteDate, MeetingForecastDate, MeetingCompleteDate, SiteVisitInstructionDate, SiteVisitCompleteDate, ReportForecastDate, ReportCompleteDate

    with the dates populated from tblDeliverables under each heading.

    You will note that a 'meeting' is not always a deliverable for a project. This is the case for Project2 and Project3. I would like to put 'N/A' (or anything else to signify that is doesn't exist) in the date field for the view if there is no such deliverable for the project.

    The number of projects will be much larger than this and the number of deliverables will also increase.

    This will eventually be output to excel. I hope I have provided enough information. Please let me know if you require anything further.

    Thanks

  • Hi Buddy,how about you going through this following article and helping us help you?? 🙂

    CLICK HERE FOR FORUM POSTING ETIQUETTES - JEFF MODEN[/url]

    When u do so, i am sure a lot of us will help u instantly...

    So please post

    1. Table Structures - CREATE TABLE scripts

    2. Constraints or Indexes in the table, - CREATE SCRIPTS

    3. SAMPLE DATA - INSERT INTO TABLE scripts

    4. Desired output - some visual representation of this.

    I know how to do your request, but without the above said things, i cant proceed further buddy...

  • My poor soul, got lot of free time these days and i am utilizing it to LEARN..:w00t:

    Now that said, i have given my time in scripting the code for you.

    First let us look at the table DDLs ..Wellington, look at how i have made up the data, so that you make the people who want to help you , will really feel easy with what-is-what and they in-turn give out the BEST and TESTED code..

    IF OBJECT_ID('TEMPDB..#tblDeliverableNames') IS NOT NULL

    DROP TABLE #tblDeliverableNames

    CREATE TABLE #tblDeliverableNames

    (

    ID INT,

    [DELIName] VARCHAR(100)

    )

    INSERT INTO #tblDeliverableNames

    SELECT 1 , 'Instruction'

    UNION ALL

    SELECT 2 ,'Meeting Held'

    UNION ALL

    SELECT 3 ,'Site Visit'

    UNION ALL

    SELECT 4 ,'Report Submitted'

    IF OBJECT_ID('TEMPDB..#tblProject') IS NOT NULL

    DROP TABLE #tblProject

    CREATE TABLE #tblProject

    (

    ID INT,

    [PROJName] VARCHAR(100)

    )

    INSERT INTO #tblProject

    SELECT 1 , 'Project1'

    UNION ALL

    SELECT 2 ,'Project2'

    UNION ALL

    SELECT 3 ,'Project3'

    UNION ALL

    SELECT 4 ,'Project4'

    IF OBJECT_ID('TEMPDB..#tblDeliverables') IS NOT NULL

    DROP TABLE #tblDeliverables

    CREATE TABLE #tblDeliverables

    (

    ID SMALLINT ,--IDENTITY(1,1),

    ProjectID INT ,

    DeliverableNameID INT,

    ForecastDate DATETIME,

    CompleteDate DATETIME

    )

    INSERT INTO #tblDeliverables

    SELECT 1 , 1 , 1 , GETDATE() , DATEADD(DD,1,GETDATE())

    UNION ALL

    SELECT 2, 1 ,2 , GETDATE() , DATEADD(DD,1,GETDATE())

    UNION ALL

    SELECT 3 ,1, 3, GETDATE() , DATEADD(DD,1,GETDATE())

    UNION ALL

    SELECT 4 ,1, 4, GETDATE() , DATEADD(DD,1,GETDATE())

    UNION ALL

    SELECT 5, 2 ,1, GETDATE() , DATEADD(DD,1,GETDATE())

    UNION ALL

    SELECT 6, 2 ,3, GETDATE() , DATEADD(DD,1,GETDATE())

    UNION ALL

    SELECT 7 ,2, 4, GETDATE() , DATEADD(DD,1,GETDATE())

    UNION ALL

    SELECT 8 ,3, 1, GETDATE() , DATEADD(DD,1,GETDATE())

    UNION ALL

    SELECT 9 ,3 ,2, GETDATE() , DATEADD(DD,1,GETDATE())

    UNION ALL

    SELECT 10, 3 ,4, GETDATE() , DATEADD(DD,1,GETDATE())

    UNION ALL

    SELECT 11 ,4 ,1, GETDATE() , DATEADD(DD,1,GETDATE())

    UNION ALL

    SELECT 12, 4, 2, GETDATE() , DATEADD(DD,1,GETDATE())

    UNION ALL

    SELECT 13 ,4 ,3, GETDATE() , DATEADD(DD,1,GETDATE())

    UNION ALL

    SELECT 14 ,4, 4, GETDATE() , DATEADD(DD,1,GETDATE())

    Now, the CODE that will perform what we wanted it to and how you wished!

    SELECT

    P.[PROJName] ProjectName,

    MAX(CASE d.DeliverableNameID WHEN 1 THEN d.ForecastDate END) AS InstructionForecastDate ,

    MAX(CASE d.DeliverableNameID WHEN 1 THEN d.CompleteDate END) AS InstructionCompleteDate ,

    MAX(CASE d.DeliverableNameID WHEN 2 THEN d.ForecastDate END) AS MeetingForecastDate ,

    MAX(CASE d.DeliverableNameID WHEN 2 THEN d.CompleteDate END) AS MeetingCompleteDate ,

    MAX(CASE d.DeliverableNameID WHEN 3 THEN d.ForecastDate END) AS SiteVisitForecastDate ,

    MAX(CASE d.DeliverableNameID WHEN 3 THEN d.CompleteDate END) AS SiteVisitCompleteDate ,

    MAX(CASE d.DeliverableNameID WHEN 4 THEN d.ForecastDate END) AS ReportForecastDate ,

    MAX(CASE d.DeliverableNameID WHEN 4 THEN d.CompleteDate END) AS ReportCompleteDate

    FROM

    #tblDeliverables d

    JOIN

    #tblDeliverableNames n

    ON d.DeliverableNameID = n.ID

    LEFT JOIN

    #tblProject P

    ON (P.ID = d.ProjectID)

    GROUP BY

    P.[PROJName]

    Now, did the above give u what you wanted ?? 🙂 Pro'ly there may be other best methods for this , but as a starter, you can have this script!

    If thats what u wanted, then today's beer is on you 😛

    Cheers!!

  • Hi ColdCoffee

    Thanks for all your efforts. I'll have to get used to posting stuff like this and will pay more attention next time. Apologies for the hassle.

    Your answer did get part of the way, but what I need is the detail you have provided as headings to the columns in the view, and then to populate the forecast dates and complete dates within the view under each heading.

    In addition, if there are no such deliverable dates for a project (such as meeting in Project2), then I need to populate with something like 'N/A' to show that it is not applicable to that project.

    Does this make sense. Please let me know if there is anything else I can provide. I am still new to this stuff and appreciate you steering me in the right direction.

    Thanks again

  • jwellington (4/28/2010)


    Your answer did get part of the way, but what I need is the detail you have provided as headings to the columns in the view, and then to populate the forecast dates and complete dates within the view under each heading.

    I perfectly DINT (:() get what your required output is. I need to see some visual samples of your desired output

    In addition, if there are no such deliverable dates for a project (such as meeting in Project2), then I need to populate with something like 'N/A' to show that it is not applicable to that project.

    We can do this, but we will be loosing the date format that you are seeing now. We will have to convert NULLS into VARCHAR which will cost us the format of the date what u are using in the ForecastDate and CompleteDate columns

    Please let me know if there is anything else I can provide. I am still new to this stuff and appreciate you steering me in the right direction.

    Yes, i need to see visually how your desired output will be;

    Like this

    ProjectNameInstructionForecastDateInstructionCompleteDate

    Project12010-04-28 13:18:08.3802010-04-29 13:18:08.380

    Project22010-04-28 13:18:08.3802010-04-29 13:18:08.380

    Project32010-04-28 13:18:08.3802010-04-29 13:18:08.380

    Project42010-04-28 13:18:08.3802010-04-29 13:18:08.380

    To get above, wrap your desired output in code="plain" /code "IFCode" you see to the right of the typing area.

    Hope this helps you...

    Cheers!

  • Hi ColdCoffee,

    Works great thanks, except for putting the N/A in if the deliverable doesn't exist for that project such as below:

    ProjectNameMeetingHeldForecastDateMeetingHeldCompleteDate

    Project12010-04-28 13:18:08.3802010-04-29 13:18:08.380

    Project2N/AN/A

    Project32010-04-28 13:18:08.380NULL

    Project4NULLNULL

    You will notice that there are still nulls in there when the date has not been filled in (and the deliverable exists).

    I am willing to forgo the date format for this view, and also to convert the NULLs to ''.

    I have tried using CONVERT and CASE statements within the view but cannot seem to get it to work as required.

    Thanks for your assistance so far.

  • Ok, as you have decided to forgo the date fomat, it becomes my duty to provide you the code that will replace NULL dates with 'N/A'.

    Here is that final (presumably ;-)) CODE..

    SELECT

    P.[PROJName] ProjectName,

    ISNULL(CONVERT(VARCHAR,(MAX(CASE d.DeliverableNameID WHEN 1 THEN d.ForecastDate END)),20),'N/A') AS InstructionForecastDate ,

    ISNULL(CONVERT(VARCHAR,(MAX(CASE d.DeliverableNameID WHEN 1 THEN d.CompleteDate END)),20),'N/A') AS InstructionCompleteDate ,

    ISNULL(CONVERT(VARCHAR,(MAX(CASE d.DeliverableNameID WHEN 2 THEN d.ForecastDate END)),20),'N/A') AS MeetingForecastDate ,

    ISNULL(CONVERT(VARCHAR,(MAX(CASE d.DeliverableNameID WHEN 2 THEN d.CompleteDate END)),20),'N/A') AS MeetingCompleteDate ,

    ISNULL(CONVERT(VARCHAR,(MAX(CASE d.DeliverableNameID WHEN 3 THEN d.ForecastDate END)),20),'N/A') AS SiteVisitForecastDate ,

    ISNULL(CONVERT(VARCHAR,(MAX(CASE d.DeliverableNameID WHEN 3 THEN d.CompleteDate END)),20),'N/A') AS SiteVisitCompleteDate ,

    ISNULL(CONVERT(VARCHAR,(MAX(CASE d.DeliverableNameID WHEN 4 THEN d.ForecastDate END)),20),'N/A') AS ReportForecastDate ,

    ISNULL(CONVERT(VARCHAR,(MAX(CASE d.DeliverableNameID WHEN 4 THEN d.CompleteDate END)),20),'N/A') AS ReportCompleteDate

    FROM

    #tblDeliverables d

    JOIN

    #tblDeliverableNames n

    ON d.DeliverableNameID = n.ID

    LEFT JOIN

    #tblProject P

    ON (P.ID = d.ProjectID)

    GROUP BY

    P.[PROJName]

    Tell us back here if that piece worked your needs!!

    Cheers!

  • Hi ColdCoffee,

    Thanks for the code, but unfortunately it is not exactly what I was looking for. I don't actually want to replace all NULL dates directly with N/A.

    You will notice in my previous post (and the original one that was difficult to understand) that for Project 2 there was no 'Meeting Held' deliverable. In this case, there will be no date values for that project milestone (ie. they don't exist). Your code would still state this as Null, which is understandable, but then there is no differentiation between a milestone date that does not exist and one that has not yet been populated.

    I would like to differentiate between existing date fields that have not yet been populated (shown with NULL values) and project milestone dates that do not actually exist for the specific project (shown with N/A).

    Does this make more sense. I hope so and appreciate all your efforts to help so far. I find this forum so helpful. Hopefully on future posts I can get all that syntax correct to start with.

    Thanks again

  • Wellington, as i had already asked, i need to see the records in your table (i dont want your orginal data, you may very well cook-up data that looks similar to your original data) and what u expect your result should be..i have given you a head-start and have given how to give sample data.. if you could edit them and send me, i can give you what you want.. without that, my hands are tied, buddy..

  • Hi ColdCoffee,

    Thanks for your post. I understand your frustration. The code you put in your post with the table structures is suitable for this question (as it contains the missing 'Meeting Held' deliverable on Project2), but if it is not suitable for you to answer then I'm afraid you have me beaten. Thanks so much for the information you have helped me with so far. It has certainly put me on the path to finding a solution.

    Thanks again

  • jwellington (4/29/2010)


    Hi ColdCoffee,

    Thanks for your post. I understand your frustration. Thanks so much for the information you have helped me with so far. It has certainly put me on the path to finding a solution.

    Wellington, i never frustrated mate :-)! I was attending another issue for my colleague and i replied your hurriedly.. thats why my tone in the reply would be different. had my post hurt you, i apologize, sire!

    I hope that you find a solution to your problem soon, and once u do it, pls post your final code so that others may benefit from that !

    It was my pleasure solving your issues, Thanks!

  • Hi,

    I believe I have solved this one. It involves a fair bit of manual coding for the entire view, but below is the solution for one line:

    CASE WHEN EXISTS(SELECT * FROM tblDeliverables WHERE tblDeliverables.ProjectID= tblProject.id AND tblDeliverables.DeliverableNameID = 1) THEN CONVERT(nvarchar(10), tblDeliverables.ForecastDate) ELSE 'N/A' END AS InstructionCompleteForecastDate

    All columns in the view use Group By, and there is definitely only on record possible for a single projectID and DeliverableNameID combination.

    Hope this helps anyone looking in future.

    Thanks for all your prior help ColdCoffee.

  • You're welcome, Wellington! 🙂

    Good to see that your code is fixed!

    Cheers!!

Viewing 13 posts - 1 through 12 (of 12 total)

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