[Last Call] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 364
  • Last Modified:

Help needed with creating a query with 3 tables

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.
1 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)

        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;
        -- 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

saabStoryAuthor Commented:
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!

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Tackle projects and never again get stuck behind a technical roadblock.
Join Now