Increment values by column into rows-- SQL Server

Question

I have the following type of data

CREATE TABLE #tmp
(
    Room [NVARCHAR](50) NOT NULL,
    iking INT,
    iqueen INT,
    isingle INT,
    idouble INT
)

INSERT INTO #tmp 
VALUES ('Marriot', 0, 1, 2, 1),
       ('Hilton', 1, 2, 0, 1)

I tried Cross Apply and case statements

I add data into temp table and wrote 4 cross apply functions for each column

1. King

   SELECT tk.Room, tk.iking, Type = CONCAT('BED', t.n)
   INTO #tempking1
   FROM #tmp tk
   CROSS APPLY
       (SELECT TOP (tk.iking)
           n = ROW_NUMBER() OVER (ORDER BY o.object_id)
        FROM sys.objects o) t
   ORDER BY tk.Room;
   --select * from #tempking1
   

2. Queen

   SELECT 
       tq.Room, tq.iQueen,   
       Type = CASE WHEN ROOM in (SELECT  Distinct ROOM FROM #tempking1) 
                      THEN CONCAT('BED', t.n + 1)
                      ELSE CONCAT('BED', t.n) 
              END          
   INTO #tempQueen1        
   FROM #tmp tq
   CROSS APPLY 
       (SELECT TOP (tq.iQueen) 
            n = ROW_NUMBER() OVER (ORDER BY o.object_id)
        FROM sys.objects o) t 
    ORDER BY tq.Room;
    --select * from #tempqueen1
   

3. Single

   SELECT 
       tq.Room, tq.isingle,   
       Type = CASE WHEN ROOM IN (SELECT Distinct ROOM FROM #tempking1) 
                      THEN CONCAT('BED', t.n + 1)
                   WHEN ROOM IN (SELECT Distinct ROOM FROM #tempqueen1) 
                      THEN CONCAT('BED', t.n + 1)
                   ElSE CONCAT('BED', t.n) 
              END
   INTO #tempsingle1
   FROM #tmp tq
   CROSS APPLY 
       (SELECT TOP (tq.isingle)
            n = ROW_NUMBER() OVER (ORDER BY o.object_id)
        FROM sys.objects o) t
   ORDER BY tq.Room;
   --select * from #tempsingle1
   

4. Double

   SELECT 
       tq.Room, tq.isingle,   
       Type = CASE WHEN ROOM IN (SELECT Distinct ROOM FROM #tempking1)  
                      THEN CONCAT('BED', t.n + 1)
                   WHEN ROOM IN (SELECT Distinct ROOM FROM #tempqueen1) 
                      THEN CONCAT('BED', t.n + 1)
                   WHEN ROOM IN (SELECT Distinct ROOM FROM #tempsingle1) 
                      THEN CONCAT('BED', t.n + 1)
                   ELSE CONCAT('BED', t.n) 
               END
       INTO #tempdouble1
       FROM #tmp tq
       CROSS APPLY 
           (SELECT TOP (tq.isingle)
                n = ROW_NUMBER() OVER (ORDER BY o.object_id)
            FROM sys.objects o) t
       ORDER BY tq.Room;
       --select * from #tempDouble1
   
   SELECT Room, Type, 'King' AS Descp FROM #tempKing1
   UNION ALL
   SELECT Room, Type, 'Queeen' AS Descp FROM #tempQueen1   
   UNION ALL
   SELECT Room, Type, 'Single' AS Descp FROM #tempsingle1
   UNION ALL
   SELECT Room, Type, 'Double' AS Descp FROM #tempDouble1
   

but I got

My excepted output is

Could you please help me

Answer 1

You can use UNPIVOT operator to fix your solution. Try this script:

drop TABLE #tmp
go
drop TABLE #Hotel
go

CREATE TABLE #tmp
( 
     Room       nvarchar(30) NOT NULL
    ,iking      int 
    ,iqueen     int
    ,isingle    int
    ,idouble    int 
)

CREATE TABLE #Hotel
(
     Room       NVARCHAR(30)
    ,RoomType   NVARCHAR(30)
    ,Total      INT
)

Insert into #tmp Values ('Marriot', 0,1,2,1),('Hilton', 1,2,0,1)

INSERT INTO #Hotel
SELECT Room, RoomType, Total  
FROM   
   (SELECT Room,iking,iqueen,isingle,idouble
   FROM #tmp) p  
UNPIVOT  
   (Total FOR RoomType IN   
      (iking,iqueen,isingle,idouble)  
)AS unpvt

SELECT   Room
        ,RoomType
        --,'Bed'+CAST(Number AS VARCHAR) AS [Desc]
        ,'Bed'+CAST(ROW_NUMBER() OVER(PARTITION BY Room ORDER BY RoomType,Number) AS VARCHAR) AS [Desc]
        ,'Bed'+CAST(Total AS varchar) [Desc2]
FROM #Hotel
INNER JOIN master.dbo.spt_values N ON  Total>=N.Number AND N.type='P' AND number<>0