How to write the Pivot clause for this specific table?-CodePudding

I am using SQL Server 2014. Below is an extract of my table (t1):

Name    RoomType    Los   RO   BB     HB    FB   StartDate    EndDate     CaptureDate
A         DLX        7    0    0     154   200  2022-01-01   2022-01-07  2021-12-31
B         SUP        7    110  0       0     0  2022-01-01   2022-01-07  2021-12-31
C         COS        7    0    0     200   139  2022-01-01   2022-01-07  2021-12-31
D         STD        7    0    75      0   500  2022-01-01   2022-01-07  2021-12-31

I need a Pivot query to convert the above table into the following output:

Name    RoomType     Los   MealPlan   Price     StartDate    EndDate     CaptureDate
 A        DLX         7      RO         0       2022-01-01   2022-01-07   2021-12-31 
 A        DLX         7      BB         0       2022-01-01   2022-01-07   2021-12-31
 A        DLX         7      HB         154     2022-01-01   2022-01-07   2021-12-31
 A        DLX         7      FB         200     2022-01-01   2022-01-07   2021-12-31
 B        SUP         7      RO         110     2022-01-01   2022-01-07   2021-12-31 
 B        SUP         7      BB         0       2022-01-01   2022-01-07   2021-12-31
 B        SUP         7      HB         0       2022-01-01   2022-01-07   2021-12-31
 B        SUP         7      FB         0       2022-01-01   2022-01-07   2021-12-31
 C        COS         7      RO         0       2022-01-01   2022-01-07   2021-12-31 
 C        COS         7      BB         0       2022-01-01   2022-01-07   2021-12-31
 C        COS         7      HB         200     2022-01-01   2022-01-07   2021-12-31
 C        COS         7      FB         139     2022-01-01   2022-01-07   2021-12-31
 D        STD         7      RO         0       2022-01-01   2022-01-07   2021-12-31 
 D        STD         7      BB         75      2022-01-01   2022-01-07   2021-12-31
 D        STD         7      HB         0       2022-01-01   2022-01-07   2021-12-31
 D        STD         7      FB         500     2022-01-01   2022-01-07   2021-12-31

I had a look at the following article but it does not seem to address my problem:

SQL Server Pivot Clause

I am did some further research but I did not land on any site that provided a solution to this problem.

Any help would be highly appreciated.

CodePudding user response：

You actually want an UNPIVOT here (comparison docs).

SELECT Name, RoomType, Los, MealPlan, Price, 
       StartDate, EndDate, CaptureDate
FROM dbo.t1
UNPIVOT (Price FOR MealPlan IN ([RO],[BB],[HB],[FB])) AS u;

Example db<>fiddle