Help needed with creating a query with 3 tables

Posted on 2012-08-23
Last Modified: 2012-08-25
I'm stuck on this -- I need to create a query - the results of which will eventually be viewed via a spreadsheet.  It needs to show the capacities of different lodges/units on our properties on a given weekend during the year.  The report I've been asked to create will have headers with the property name in column 1 followed by the name of each individual lodge/unit in separate columns.

The data will be the start and end dates in column 1 followed by the capacities for each lodge during that time frame - and of course the capacities need to line up under the correct lodge/unit name. The number of lodges varies for each of the 6 camps and not every lodge is used every weekend. I've attached an excel file with a sample of what I'm talking about.

At some point in the future, we plan on putting a web front end on this data and let the content owners update their own data.  But first thing is to get a report working.

The tables have been created but we can change schemas if needed.  There's more to it than what I'm showing here but this is the thing at it's most basic.

The current layout of the tables are:
campID - int (PK)
campName - varchar(50)

unitID - int (PK)
campID - int
unitName - varchar(50)
unitType - varchar(25)

tbl CampDates
id - int (PK)
campID - int
untiID - int
dateStart - date
dateEnd - date
capacity - int

As always, many thanks in advance.
Question by:saabStory
    LVL 25

    Accepted Solution

    It is not easy to have a single SQL with a variable number of columns.  What it looks like you want is a PIVOT.  e.g.
    select convert(varchar(6), dateStart, 100)+'-'+datename(d,dateEnd) as dates
         , [Lodge 1],[Lodge 2],[Lodge 3],[Lodge 4]
    from (select campName, dateStart, dateEnd, unitName, capacity
          from tbl_CampNames cn
          join tbl_CampUnits cu ON cn.campID = cu.campID
          join tbl_CampDates cd on cn.campID = cd.campID and cu.unitID = cd.unitID
          where cn.campID = 1)v
    pivot(sum(capacity) for unitName in ([Lodge 1],[Lodge 2],[Lodge 3],[Lodge 4])) as pvt

    Open in new window

    This requires you to know each of the row values that will be pivoted into the column names.

    To do this in SQL with variable columns and all ... a procedure that dynamically build the SQL for each campID within a loop would be needed.  Something like:
        DECLARE @camp           INT
        DECLARE @campname       VARCHAR(MAX)
        DECLARE @PivotColumns   VARCHAR(MAX)
        DECLARE campCursor CURSOR FOR
            SELECT cn.campID, cn.campName,
                   stuff((SELECT DISTINCT ',[' + unitName +']' 
                          FROM tbl_CampUnits cu 
                          WHERE cn.campID = cu.campID 
                          FOR XML PATH ('')), 1, 1, '') as units
            FROM tbl_CampNames cn;
        OPEN campCursor;
        FETCH NEXT FROM campCursor INTO @camp, @campname, @PivotColumns;
        WHILE @@FETCH_STATUS = 0
            -- Build the dynamic SQL statement
            SET @PivotTableSQL = N'
    select convert(varchar(6), dateStart, 100)+''-''+datename(d,dateEnd) as [' + @campname + ']
         , ' + @PivotColumns + '
    from (select campName, dateStart, dateEnd, unitName, capacity
          from tbl_CampNames cn
          join tbl_CampUnits cu ON cn.campID = cu.campID
          join tbl_CampDates cd on cn.campID = cd.campID and cu.unitID = cd.unitID
          where cn.campID = @campID)v
    pivot(sum(capacity) for unitName in (' + @PivotColumns + ')) as pvt'
            EXECUTE sp_executesql @PivotTableSQL, N'@campID INT', @camp
            FETCH NEXT FROM campCursor INTO @camp, @campname, @PivotColumns;
        CLOSE campCursor;
        DEALLOCATE campCursor;

    Open in new window

    btw. Table definition and sample data (may be useful for other experts - save them the time of typing it all up).
    create table tbl_CampNames (
        campID      int IDENTITY(1,1),
        campName    varchar(50)
    insert into tbl_CampNames (campName) values ('Camp  1 Name'),('Camp  2 Name'),('Camp  3 Name');
    create table tbl_CampUnits (
        unitID      int IDENTITY(1,1),
        campID      int,
        unitName    varchar(50),
        unitType    varchar(25)
    insert into tbl_CampUnits (campID, unitName) values (1, 'Lodge 1'),(1, 'Lodge 2'),(1, 'Lodge 3'),(1, 'Lodge 4');
    insert into tbl_CampUnits (campID, unitName) values (2, 'Lodge 1'),(2, 'Lodge 2');
    insert into tbl_CampUnits (campID, unitName) values (3, 'Lodge 1'),(3, 'Lodge 2'),(3, 'Lodge 3');
    create table tbl_CampDates (
        id          int IDENTITY(1,1),
        campID      int,
        unitID      int,
        dateStart   date,
        dateEnd     date,
        capacity    int
    insert into tbl_CampDates (campID,unitID,dateStart,dateEnd,capacity) values 
    (1,1,'2012-09-28','2012-09-30',15),(1,2,'2012-09-28','2012-09-30', 0),(1,3,'2012-09-28','2012-09-30', 0),(1,4,'2012-09-28','2012-09-30',23),
    (1,1,'2012-10-26','2012-10-28', 0),(1,2,'2012-10-26','2012-10-28',30),(1,3,'2012-10-26','2012-10-28',22),(1,4,'2012-10-26','2012-10-28', 0);
    insert into tbl_CampDates (campID,unitID,dateStart,dateEnd,capacity) values 
    (2,5,'2012-09-14','2012-09-16',18),(2,6,'2012-09-14','2012-09-16', 0),
    (2,5,'2012-09-28','2012-09-30', 0),(2,6,'2012-09-28','2012-09-30',25),
    (2,5,'2012-10-26','2012-10-28', 0),(2,6,'2012-10-26','2012-10-28',25);
    insert into tbl_CampDates (campID,unitID,dateStart,dateEnd,capacity) values 
    (3,7,'2012-09-14','2012-09-16',18),(3,8,'2012-09-14','2012-09-16', 0),(3,9,'2012-09-14','2012-09-16',17),
    (3,7,'2012-10-26','2012-10-28', 0),(3,8,'2012-10-26','2012-10-28',15),(3,9,'2012-10-26','2012-10-28', 0);

    Open in new window


    Author Closing Comment

    All I can say is wow!  I tumbled across pivots late yesterday and realized that was where I needed to go but never would have gotten to this.  It's perfect and just what we needed.  Best of all, I've got lots of studying to do to understand this.

    Thanks so much - have a great weekend!
    LVL 51

    Expert Comment

    by:Mark Wills

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now