Sql : Select A Dynamic Number Of Rows As Columns

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) NumberOfR

Solution 1:

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.

Solution 2:

What you require is a pivot query, to convert row data into columnar:

SELECT t.hotelid,
         t.blockid,
         t.blockname,
         MAX(CASEWHEN t2.blockdate ='02-10-10'THEN t.numberofrooms ELSENULLEND) AS02_10_10,
         MAX(CASEWHEN t2.blockdate ='02-11-10'THEN t.numberofrooms ELSENULLEND) AS02_11_10,
         MAX(CASEWHEN t2.blockdate ='02-12-10'THEN t.numberofrooms ELSENULLEND) AS02_12_10,
         ...
    FROM TABLE_1 t
    JOIN TABLE_2 t2
GROUPBY t.hotelid, t.blockid, t.blockname

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

2. The database is important, because it will need dynamic SQL to create the MAX(CASE... statements

Solution 3:

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.

Solution 4:

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 :

1. I create a temp table with all the profile properties as columns (dynamically)
2. I fill the temp table with userid (as foreign key to link to users table and all the profile properties data
3. I do a join on users table and temp table

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 ONset QUOTED_IDENTIFIER ON
go

ALTERPROCEDURE [dbo].[rows2cols] @portalIdINTASBEGIN
print 'PortalID='+convert(varchar,@portalId)
    --SET NOCOUNT ON;declare@idxintdeclare@rowcountintdeclare@tmpStr nvarchar(max)
    declare@ctype nvarchar(max)
    declare@cname nvarchar(max)
    declare@clenintdeclare@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'))
DROPTABLE [dbo].[xxxx]

print 'Building Temp Table Cols for profile properties...'set@rowcount= (selectcount(*) from ProfilePropertyDefinition where PortalID=0and deleted=0)
set@idx=1set@tmpStr=''
while (@idx<=@rowcount)
begin-- dynamically generate rownumbers to be able to do loop over them (avoid cursors)select@cname= t1.PropertyName from
    ( selectROW_NUMBER() OVER (ORDERBY ViewOrder) as Idx, PropertyName from ProfilePropertyDefinition 
      where PortalID=0and deleted=0
    ) as t1 where t1.Idx =@idx

    if (@cname='Email'or@cname='FirstName'or@cname='LastName') beginset@clen=1endelsebeginset@tmpStr=@tmpStr+'['+@cname+'] [nvarchar](500), 'endset@idx=@idx+1endset@tmpStr=@tmpStr+'[userid] [int] 'set@createStr='create table xxxx ( '+@tmpStr+' )'

Print @createStrExec (@createStr)

----------------------------------------------------------------------------- fill tmp table -----------------------------------------------------------------------------    declare@propName nvarchar(max)
declare@propVal nvarchar(max)
declare@userIdintdeclare@idx2intdeclare@rowcount2intdeclare@inscol nvarchar(max)
declare@insval nvarchar(max)

set@rowcount= (selectcount(*) FROM Users LEFTOUTERJOIN UserPortals ON Users.UserID = UserPortals.UserId WHERE UserPortals.PortalId =@portalId)
set@idx=1
    while (@idx<=@rowcount)
    begin-- get userIdselect@userId= t1.UserID from (select u.UserID, ROW_NUMBER() OVER (ORDERBY u.UserID) as Idx
        from Users as u LEFTOUTERJOIN UserPortals as up ON u.UserID = up.UserId where up.PortalId =@portalId) as t1 
        where t1.Idx =@idxset@idx2=1set@rowcount2= (selectcount(*) from UserProfile where UserID =@userId)
        set@inscol=''set@insval=''

        while (@idx2<@rowcount2)
        begin-- build insert for a specific userselect@propName= t1.PropertyName , @propVal=t1.PropertyValue from
            ( selectROW_NUMBER() OVER (ORDERBY ProfileID) as Idx, up.PropertyDefinitionID,ppd.PropertyName, up.PropertyValue 
              from UserProfile as up 
               innerjoin 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')
            beginset@inscol=@inscol+@propName+', 'set@insval=@insval+'N'''+ replace(@propVal,'''','''''') +''', 'endset@idx2=@idx2+1endset@inscol=@inscol+'userid'set@insval=@insval+convert(nvarchar,@userId) 

        set@tmpStr='insert into xxxx ('+@inscol+') values ('+@insval+')'--print @tmpStrExec(@tmpStr)
        set@idx=@idx+1end-- --------------------------------------------------------------------------- return tmp table & dump  ---- -------------------------------------------------------------------------    SELECT Users.*, xxxx.*FROM xxxx INNERJOIN 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'))
DROPTABLE [dbo].[xxxx]
END