I have a table with the following structure that I want to receive output from this table dynamically. But I do not know how to use Pivot and UnPivot.
The list of fields I use is as follows.
SELECT [RoomID]
,[RoomNumber]
,[RoomType]
,[RoomTypeDescription]
,[RoomBed]
,[PriceOfPerNight]
,[RoomStatuse]
,[RoomStatuseDesc]
,[RoomFloorID]
,[RoomFloorTitle]
FROM [HotelOnline].[dbo].[XtblRooms]
RoomID RoomNumber RType RDesc Beds Price RoomStatuse RDesc FloorID RoomFloorTitle
1 RM100001 2 Degree 2 6 9000000.00 1 Free 1 Floor 001
2 RM100002 1 Degree 1 4 6000000.00 1 Free 1 Floor 001
3 RM100003 2 Degree 2 3 4500000.00 1 Free 1 Floor 001
4 RM100004 3 Degree 3 5 4800000.00 1 Free 1 Floor 001
5 RM100005 1 Degree 1 3 4700000.00 1 Free 1 Floor 001
6 RM100006 1 Degree 1 6 7500000.00 1 Free 1 Floor 001
7 RM100007 1 Degree 1 5 7000000.00 1 Free 1 Floor 001
8 RM100008 1 Degree 1 2 2500000.00 1 Free 1 Floor 001
9 RM100009 3 Degree 3 3 3500000.00 1 Free 1 Floor 001
10 RM100010 3 Degree 3 8 8000000.00 1 Free 1 Floor 001
11 RM100011 2 Degree 2 5 6500000.00 1 Free 2 Floor 002
12 RM100012 3 Degree 3 2 3800000.00 1 Free 2 Floor 002
13 RM100013 2 Degree 2 5 9650000.00 1 Free 2 Floor 002
14 RM100014 3 Degree 3 2 2500000.00 1 Free 2 Floor 002
15 RM100015 2 Degree 2 2 4500000.00 1 Free 2 Floor 002
16 RM100016 3 Degree 3 4 4000000.00 1 Free 2 Floor 002
17 RM100017 1 Degree 1 2 2500000.00 1 Free 2 Floor 002
18 RM100018 3 Degree 3 3 4500000.00 1 Free 2 Floor 002
19 RM100019 2 Degree 2 5 5000000.00 1 Free 2 Floor 002
20 RM100020 2 Degree 2 4 4500000.00 1 Free 2 Floor 002
21 RM100021 1 Degree 1 6 7500000.00 1 Free 3 Floor 003
22 RM100022 2 Degree 2 3 3000000.00 1 Free 3 Floor 003
23 RM100023 3 Degree 3 3 2500000.00 1 Free 3 Floor 003
24 RM100024 1 Degree 1 3 2500000.00 1 Free 3 Floor 003
25 RM100025 2 Degree 2 5 4800000.00 1 Free 3 Floor 003
26 RM100026 3 Degree 3 4 4000000.00 1 Free 3 Floor 003
27 RM100027 2 Degree 2 2 1800000.00 1 Free 3 Floor 003
28 RM100028 3 Degree 3 5 4700000.00 1 Free 3 Floor 003
29 RM100029 1 Degree 1 3 3500000.00 1 Free 3 Floor 003
30 RM100030 2 Degree 2 6 4600000.00 1 Free 3 Floor 003
31 RM100031 2 Degree 2 5 4500000.00 1 Free 4 Floor 004
32 RM100032 1 Degree 1 2 3500000.00 1 Free 4 Floor 004
33 RM100033 3 Degree 3 4 3700000.00 1 Free 4 Floor 004
34 RM100034 2 Degree 2 3 2800000.00 1 Free 4 Floor 004
35 RM100035 3 Degree 3 6 5500000.00 1 Free 4 Floor 004
36 RM100036 2 Degree 2 4 3700000.00 1 Free 4 Floor 004
37 RM100037 3 Degree 3 6 5800000.00 1 Free 4 Floor 004
38 RM100038 1 Degree 1 3 4000000.00 1 Free 4 Floor 004
39 RM100039 1 Degree 1 5 5500000.00 1 Free 4 Floor 004
40 RM100040 1 Degree 1 6 6500000.00 1 Free 4 Floor 004
41 RM100041 1 Degree 1 4 4500000.00 1 Free 5 Floor 005
42 RM100042 2 Degree 2 6 5500000.00 1 Free 5 Floor 005
43 RM100043 2 Degree 2 4 4000000.00 1 Free 5 Floor 005
44 RM100044 2 Degree 2 3 3500000.00 1 Free 5 Floor 005
45 RM100045 3 Degree 3 3 3000000.00 1 Free 5 Floor 005
46 RM100046 3 Degree 3 5 4000000.00 1 Free 5 Floor 005
47 RM100047 3 Degree 3 4 3900000.00 1 Free 5 Floor 005
48 RM100048 2 Degree 2 5 4700000.00 1 Free 5 Floor 005
49 RM100049 2 Degree 2 3 3800000.00 1 Free 5 Floor 005
50 RM100050 3 Degree 3 5 4700000.00 1 Free 5 Floor 005
This is the output I need.
Floor 001 Floor 002 Floor 003 Floor 004 Floor 005
==========================================================
RM100001 RM100012 RM100028 RM100033 RM100049
RM100002 RM100013 RM100029 RM100033 RM100050
.. . . . . .
I tried several ways but did not get the answer. Help if possible. I tried several ways but did not get the answer. Help if possible. If possible, use the dynamic method to get the answer. It does not matter if it is not for you. My problem will be solved in the same way as usual.
CodePudding user response：
Because I am "illiterate" I was unable to answer this question:
--If you want a full working example for your data, provide full DDL and DML statements.
WITH YourTable AS(
SELECT *
FROM (VALUES
('1','RM100001','2','Degree 2','6','9000000.00','1','Free','1','Floor 001'),
('2','RM100002','1','Degree 1','4','6000000.00','1','Free','1','Floor 001'),
('3','RM100003','2','Degree 2','3','4500000.00','1','Free','1','Floor 001'),
('4','RM100004','3','Degree 3','5','4800000.00','1','Free','1','Floor 001'),
('5','RM100005','1','Degree 1','3','4700000.00','1','Free','1','Floor 001'),
('6','RM100006','1','Degree 1','6','7500000.00','1','Free','1','Floor 001'),
('7','RM100007','1','Degree 1','5','7000000.00','1','Free','1','Floor 001'),
('8','RM100008','1','Degree 1','2','2500000.00','1','Free','1','Floor 001'),
('9','RM100009','3','Degree 3','3','3500000.00','1','Free','1','Floor 001'),
('10','RM100010','3','Degree 3','8','8000000.00','1','Free','1','Floor 001'),
('11','RM100011','2','Degree 2','5','6500000.00','1','Free','2','Floor 002'))V(RoomID,RoomNumber,RType,RDesc,Beds,Price,RoomStatuse,RDesc2,FloorID,RoomFloorTitle)),
/*
Having multiple columns with the same name is a design flaw (not allowed).
I have named the second Rdesc as RDesc2, but this is also flawed (it breaks normalisation rules).
Fix your design, normalise your data.
*/
RNs AS(
SELECT RoomNumber,
RoomFloorTitle,
ROW_NUMBER() OVER (PARTITION BY RoomFloorTitle ORDER BY RoomNumber) AS RN --ORDER BY should be your ID/always ascending column
FROM YourTable)
SELECT MAX(CASE RoomFloorTitle WHEN 'Floor 001' THEN RoomNumber END) AS Floor001,
MAX(CASE RoomFloorTitle WHEN 'Floor 002' THEN RoomNumber END) AS Floor002
FROM RNs
GROUP BY RN;
CodePudding user response：
SELECT *
FROM (
select RoomStatuseDesc,
RoomNumber,
count(*) over (partition by RoomStatuseDesc order by
RoomNumber) rm2 from XtblRooms)as XtblRooms
PIVOT (max(RoomNumber) --as R_count, min(RoomNumber) as r_start
for RoomStatuseDesc in('Floor1','Floor2', 'Floor3', 'Floor4', 'Floor5'))pvt