SQL Server String based SQL query

dzhpxtsq 于 2023-06-21 发布在其他

关注(0)|答案(1)|浏览(113)

I have a table with BedTypes and RoomId columns as below.
| BedType | RoomId | FirstName |
| ------------ | ------------ | ------------ |
| Double | 4 | Andrew |
| Double | 4 | Janet |
| Double | 5 | Anne |
| Double | 5 | Robert |
| Double | 7 | Michael |
| Double | 7 | Steven |
| Triple | 8 | Howard |
| Triple | 8 | John |
| Triple | 8 | Carlos |
| Triple | 9 | Jaime |
| Triple | 9 | Giovanni |
| Triple | 9 | Rene |
| Double | 10 | Alejandra |
| Double | 10 | Pascale |
| Single | 11 | Paolo |

I received to this table with the query below;

SELECT B.Name 'BedType',Ro.Id'RoomId',C.FirstName From Customers C
JOIN Rooms Ro ON Ro.Id = C.RoomId 
JOIN BedTypes B ON B.Id = Ro.BedTypeId 
ORDER BY Ro.ReservationId

The deal I could not solve is I want to make a "String based column". If the bed type of the room is Double I want only one Double Cell, and second customer's BedType cell is going to be null, as shown in the table below.

BedType	RoomId	FirstName
Double	4	Andrew
	4	Janet
Double	5	Anne
	5	Robert
Double	7	Michael
	7	Steven
Triple	8	Howard
	8	John
	8	Carlos
Triple	9	Jaime
	9	Giovanni
	9	Rene
Double	10	Alejandra
	10	Pascale
Single	11	Paolo

I tried to write a query as below but I could not succeed so far:

SELECT
( 
CASE WHEN B.Id =1  THEN  (SELECT Ro.Id  ) 
WHEN B.Id =3 THEN NULL 
WHEN B.Id = 4 THEN Null 
END  )  AS Trial
,B.Name 'BedType',Ro.Id'RoomId',C.FirstName From Customers C
JOIN Rooms Ro ON Ro.Id = C.RoomId 
JOIN BedTypes B ON B.Id = Ro.BedTypeId 
ORDER BY Ro.ReservationId

sql-server

来源：https://stackoverflow.com/questions/76472944/string-based-sql-query

1条答案

按热度按时间

czq61nw11#

As mentioned by @Thom A in comments, This can be done using the window function lag and case clause :

select case when lag_BedType is null then BedType else null end as BedType, RoomId, FirstName
from (
  select *, lag(BedType) over (partition by RoomId order by FirstName) as lag_BedType
  from mytable
) s

It can also be done using row_number() where only the first record per room should be filled by BedType :

select case when rn = 1 then BedType else null end as BedType, RoomId, FirstName
from (
  select *, row_number() over (partition by RoomId order by FirstName) as rn
  from mytable
) s

Demo here

赞(0）回复(0）举报 2023-06-21

我来回答

SQL Server String based SQL query

1条答案

相关问题

热门标签

最新问答