By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Member(
MemberID varchar(50),
FName varchar(50),
LName varchar(50)
);
CREATE TABLE Guest(
GuestID varchar(50),
FName varchar(50),
LName varchar(50),
MemberId varchar(50)
);
INSERT INTO Member VALUES ('001','Frank','Smith');
INSERT INTO Member VALUES ('002','Mary','Jane');
INSERT INTO Member VALUES ('003','John','Henry');
INSERT INTO Guest VALUES ('101','Steve','Smith','001');
INSERT INTO Guest VALUES ('102','Peter','Smith','001');
INSERT INTO Guest VALUES ('103','Mike','Jane','002');
6 rows affected
SELECT MemberID,FName,LName,
MAX(CASE WHEN rn = 1 THEN GuestID END) GuestID1,
MAX(CASE WHEN rn = 1 THEN g_LName END) LName1,
MAX(CASE WHEN rn = 2 THEN GuestID END) GuestID2,
MAX(CASE WHEN rn = 2 THEN g_LName END) LName2
FROM (
SELECT m.MemberID,
m.FName,
m.LName,
GuestID,
g.LName g_LName,
ROW_NUMBER() OVER(PARTITION BY m.MemberID ORDER BY GuestID) rn
FROM Member m LEFT JOIN Guest g
ON m.MemberId = g.MemberId
) t1
GROUP BY MemberID,FName,LName
| MemberID | FName | LName | GuestID1 | LName1 | GuestID2 | LName2 |
|---|---|---|---|---|---|---|
| 001 | Frank | Smith | 101 | Smith | 102 | Smith |
| 002 | Mary | Jane | 103 | Jane | null | null |
| 003 | John | Henry | null | null | null | null |
Warning: Null value is eliminated by an aggregate or other SET operation.
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX);
SET @cols = STUFF((
SELECT distinct ', MAX(CASE WHEN rn = '+ CAST(t1.cnt AS VARCHAR(5)) + ' THEN GuestID END)' + ' as '''+CONCAT(name,t1.cnt)+''''
FROM (SELECT COUNT(*) cnt FROM Guest GROUP BY MemberId) t1
CROSS JOIN (SELECT 'GuestID' name UNION ALL SELECT 'LName') t2
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT MemberID,FName,LName, ' + @cols + '
FROM (
SELECT m.MemberID,
m.FName,
m.LName,
GuestID,
g.LName g_LName,
ROW_NUMBER() OVER(PARTITION BY m.MemberID ORDER BY GuestID) rn
FROM Member m LEFT JOIN Guest g
ON m.MemberId = g.MemberId
) t1
GROUP BY MemberID,FName,LName '
execute(@query)
| MemberID | FName | LName | GuestID1 | LName1 | GuestID2 | LName2 |
|---|---|---|---|---|---|---|
| 001 | Frank | Smith | 101 | 101 | 102 | 102 |
| 002 | Mary | Jane | 103 | 103 | null | null |
| 003 | John | Henry | null | null | null | null |
Warning: Null value is eliminated by an aggregate or other SET operation.