I need to select static colums + a dynamic number of rows as columns in SQL
TABLE 1
-------
HotelID
BlockID
BlockName
TABLE 2
-------
BlockDate (unknown number of these)
NumberOfRooms
Desired Result Row
------------------
HotelID | BlockID | BlockName | 02/10/10 | 02/11/10 | 02/12/10 | ...N
Where the date columns are the unknown number of BlockDate rows.
Restructure your database, that is bad. You use an imperative language to build such a list of columns.
you are missing a foreign key. I have to assume that BlockId should be PK in table 2?
Also, assuming that this is a legacy db and changing the structure is not an option, i have to ask which platform?
If ms sql, this could easily be achieved using a dynamic sql statement.
What you require is a pivot query, to convert row data into columnar:
SELECT t.hotelid,
t.blockid,
t.blockname,
MAX(CASE WHEN t2.blockdate = '02-10-10' THEN t.numberofrooms ELSE NULL END) AS 02_10_10,
MAX(CASE WHEN t2.blockdate = '02-11-10' THEN t.numberofrooms ELSE NULL END) AS 02_11_10,
MAX(CASE WHEN t2.blockdate = '02-12-10' THEN t.numberofrooms ELSE NULL END) AS 02_12_10,
...
FROM TABLE_1 t
JOIN TABLE_2 t2
GROUP BY t.hotelid, t.blockid, t.blockname
Mind that there's nothing to link the tables - realistically TABLE_2 needs hotelid and blockid attributes. As-is, this will return the results of TABLE_2 for every record in TABLE_1...
The database is important, because it will need dynamic SQL to create the MAX(CASE... statements
Do this in the client.
SQL is a fixed column language: you can't have a varible number of columns (even with PIVOT etc). Dynamic SQL is not a good idea.
I once wrote a stored procedure that did just something like this. Given are a users table with basic details and a variable number of profile properties for users. (The number of profile properties varies per DotNetNuke Portal)
This is straight from DotNetNuke 4.9, check the database schema from there and you'll get the details. Tables involved are Users, UserPortals, UserProfile, ProfilePropertyDefinition
In short words :
This gives me one row per user with all profile properties.
Here the code - not perfect but hey its tailored for my needs but should be easy enough to re-use (tried to avoid cursors btw)
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[rows2cols] @portalId INT
AS BEGIN
print 'PortalID=' + convert(varchar,@portalId)
--SET NOCOUNT ON;
declare @idx int
declare @rowcount int
declare @tmpStr nvarchar(max)
declare @ctype nvarchar(max)
declare @cname nvarchar(max)
declare @clen int
declare @createStr nvarchar(max)
---------------------------------------------------------------------------
-- create tmp table --
---------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[xxxx]') AND type in (N'U'))
DROP TABLE [dbo].[xxxx]
print 'Building Temp Table Cols for profile properties...'
set @rowcount = (select count(*) from ProfilePropertyDefinition where PortalID=0 and deleted=0)
set @idx = 1
set @tmpStr = ''
while (@idx <= @rowcount)
begin
-- dynamically generate rownumbers to be able to do loop over them (avoid cursors)
select @cname = t1.PropertyName from
( select ROW_NUMBER() OVER (ORDER BY ViewOrder) as Idx, PropertyName from ProfilePropertyDefinition
where PortalID=0 and deleted=0
) as t1 where t1.Idx = @idx
if (@cname = 'Email' or @cname = 'FirstName' or @cname = 'LastName') begin
set @clen = 1
end else begin
set @tmpStr = @tmpStr + '[' + @cname + '] [nvarchar](500), '
end
set @idx = @idx + 1
end
set @tmpStr = @tmpStr + '[userid] [int] '
set @createStr = 'create table xxxx ( ' + @tmpStr + ' )'
Print @createStr
Exec (@createStr)
---------------------------------------------------------------------------
-- fill tmp table --
---------------------------------------------------------------------------
declare @propName nvarchar(max)
declare @propVal nvarchar(max)
declare @userId int
declare @idx2 int
declare @rowcount2 int
declare @inscol nvarchar(max)
declare @insval nvarchar(max)
set @rowcount = (select count(*) FROM Users LEFT OUTER JOIN UserPortals ON Users.UserID = UserPortals.UserId WHERE UserPortals.PortalId = @portalId)
set @idx = 1
while (@idx <= @rowcount)
begin
-- get userId
select @userId = t1.UserID from (select u.UserID, ROW_NUMBER() OVER (ORDER BY u.UserID) as Idx
from Users as u LEFT OUTER JOIN UserPortals as up ON u.UserID = up.UserId where up.PortalId = @portalId) as t1
where t1.Idx = @idx
set @idx2 = 1
set @rowcount2 = (select count(*) from UserProfile where UserID = @userId)
set @inscol = ''
set @insval = ''
while (@idx2 < @rowcount2)
begin
-- build insert for a specific user
select @propName = t1.PropertyName , @propVal=t1.PropertyValue from
( select ROW_NUMBER() OVER (ORDER BY ProfileID) as Idx, up.PropertyDefinitionID,ppd.PropertyName, up.PropertyValue
from UserProfile as up
inner join ProfilePropertyDefinition as ppd on up.PropertyDefinitionID = ppd.PropertyDefinitionID
where UserID = @userId
) as t1 where t1.Idx = @idx2
if (@propName != 'Firstname' and @propName != 'LastName' and @propName != 'Email')
begin
set @inscol = @inscol + @propName + ', '
set @insval = @insval + 'N''' + replace(@propVal,'''','''''') + ''', '
end
set @idx2 = @idx2 + 1
end
set @inscol = @inscol + 'userid'
set @insval = @insval + convert(nvarchar,@userId)
set @tmpStr = 'insert into xxxx (' + @inscol + ') values (' + @insval + ')'
--print @tmpStr
Exec(@tmpStr)
set @idx = @idx + 1
end
-- -------------------------------------------------------------------------
-- return tmp table & dump --
-- -------------------------------------------------------------------------
SELECT Users.*, xxxx.* FROM xxxx INNER JOIN Users ON xxxx.userid = Users.UserID
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[xxxx]') AND type in (N'U'))
DROP TABLE [dbo].[xxxx]
END