Odtnhj

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

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