SQL Server SQL JOIN / Merge resultant records to single row

csga3l58 于 2023-06-28 发布在其他

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

I have a table with bookings which has got itemcode, each booking might have multiple records in the table with multiple itemcode. I have an ItemMapping table which maps to itemcode. One person might have multiple bookings too.

I want a result for only 2 itemtypes say - 1000 and 2000.

The twist is in the ItemMapping table:
| ItemCode | ItemName | ItemTypes |
| ------------ | ------------ | ------------ |
| AA | Cell 2 | 1000 |
| CC | Cell 4 | 1000 |
| DD | Cell 5 | 2000 |
| EE | Cell 6 | 3000 |
| FF | Cell 7 | 2000 |
| BB | Cell 8 | 4000 |

Booking Table,

BookingId	Bookingdte	ItemCode	Name
111	March 2	AA	John
222	June 4	CC	David
111	March 2	DD	John
222	June 4	EE	David
333	MAy 10	FF	Don
111	March 2	BB	John

The resultset should have the below columns,

BookingId, BookingDate, Name, ItemName(1000), ItemName(2000)

When I am looking for bookingId 111, The result should be 111, March 2, John,Cell 2, Cell 5

But I am getting 2 rows, 111, March 2, John,NULL, Cell 5 111, March 2, John,Cell 2, NULL

The result I get is, one row for ItemName(1000) and another row for ItemName(2000) , but I want the result in a single row.

Can any of you guide me on this ?

SELECT DISTINCT 
    B.BookingID, B.BookingDate,
    CASE 
        WHEN B.ItemCOde = 1000 
            THEN ISNULL(I.ItemName, I.ItemName) 
    END AS ItemName(1000),
    CASE 
        WHEN B1.ItemCOde = 2000 
            THEN ISNULL(I1.ItemName, I1.ItemNAme) 
    END AS ItemName(2000)
FROM
    BOOKINGS B
LEFT JOIN 
    ItemMapping I ON I.COde = B.ItemCode
INNER JOIN 
    Bookings B1 ON B1.BookingId = B.BookingId
LEFT JOIN 
    ItemMapping I1 ON I1.Code = B1.Itemcode
WHERE 
    B.Itemcode = 1000 
    AND B1.COde = 2000

sql-server

来源：https://stackoverflow.com/questions/76547787/sql-join-merge-resultant-records-to-single-row

1条答案

按热度按时间

enyaitl31#

It looks like you are over-complicating this. You only need one join, and then just conditional aggregation.

SELECT
  b.BookingId,
  b.Bookingdte,
  b.Name,
  [ItemName(1000)] = MAX(CASE WHEN im.ItemTypes = 1000 THEN im.ItemName END),
  [ItemName(2000)] = MAX(CASE WHEN im.ItemTypes = 2000 THEN im.ItemName END)
FROM Booking b
JOIN ItemMapping im ON im.ItemCode = b.ItemCode
WHERE b.BookingId = 111
GROUP BY
  b.BookingId,
  b.Bookingdte,
  b.Name;

db<>fiddle

If you have multiple rows for each ItemTypes then you would need some way of showing all of them. You could possibly use

[ItemName(1000)] = STRING_AGG(CASE WHEN im.ItemTypes = 1000 THEN im.ItemName END, ', '),

Or you could maybe add a row-number and group over that as well, but your desired results are unclear.

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

我来回答

SQL Server SQL JOIN / Merge resultant records to single row

1条答案

相关问题

热门标签

最新问答