Expand Col values based on names/email SQL

I want to explode my table based on their ‘KEY’s and assort their names and emails separately into individual cols.

In Python such scenarios are dealt easily(like we use explode in pandas). since I’ve just started with SQL I’m finding it difficult to get my feet wet. My googling didn’t help me even next to were i want, hence reaching out to the community as a my last resort for some guidance. In essence i’m trying to expand my table with my KEY as the index and the names and emails exploded across multiple cols.

my table:

KEY	FIRST	MIDDLE	LAST	EMAIL	FLAG_GENDER	FLAG_DESCENT
1	ROBIN	A	SIMPSON	[e-mail]	M	–
1	NICOLE	P	SIMPOSON	[e-mail]	F	NA
1	SANDY		LAYNE		F	NA
1	BRUCE		NILLS	BRUCENILLS@	M	NA
1	ERIC		WOOTEN	[e-mail]	M	NA
5	JUDY		THAMES	[e-mail]	F	NA
5	JUDY		THAMES	[e-mail]	F	NA
A290	RENN	J	JOHNSON	[e-mail]	M	C
K890	JAMES		RODRIGUES	NA	M	L
189	BECKIE	KATE	MOORS	[e-mail]	F	–
189	BECKIE			[e-mail]	F	–
189			MOORS	[e-mail]	F	–
189	BECKIE	KATE	MOORS	[e-mail]	F	–

output table:

KEY	FULL NAME_1	FULL NAME_2	FULL NAME_3	FULL NAME_4	FULL NAME_5	EMAIL_1	EMAIL_2	EMAIL_3	EMAIL_4	EMAIL_5
1	ROBIN A SIMPSON	NICOLE P SIMPOSON	SANDY LAYNE	BRUCE NILLS	ERIC WOOTEN	[e-mail]	[e-mail]	NA	BRUCENILLS@	[e-mail]
5	JUDY THAMES	JUDY THAMES				[e-mail]	[e-mail]
A290	RENN J JOHNSON					[e-mail]
K890	JAMES RODRIGUES					NA
189	BECKIE KATE MOORS	BECKIE MOORS	MOORS	BECKIE KATE MOORS		[e-mail]	[e-mail]	[e-mail]	[e-mail]

Answer

As noted in my comments, I strongly suggest that this is a duplicate of Group by column and multiple Rows into One Row multiple columns however, to demonstrate how you would do it for 2 columns, instead of 1:

WITH RNs AS(
    SELECT [KEY], --KEY is a reserved keyword and should not be used for object names.
           STUFF(CONCAT(' ' + [FIRST], ' ' + MIDDLE, ' ' + [LAST]),1,1,'') AS FullName, --FIRST and LAST are reserved ODBC keywords and should be avoided for object names.
           --If you're on 2017+ you can use CONCAT_WS:
           --CONCAT_WS(' ',[FIRST], MIDDLE, [LAST]) AS FullName,
           Email,
           ROW_NUMBER() OVER (PARTITION BY [KEY] ORDER BY (SELECT NULL)) AS RN --ORDER BY should be your ID/always ascending column --KEY is a reserved keyword and should not be used for object names.
    FROM dbo.YourTable)
SELECT [KEY], --KEY is a reserved keyword and should not be used for object names.
       MAX(CASE RN WHEN 1 THEN FullName END) AS FullName1,
       MAX(CASE RN WHEN 2 THEN FullName END) AS FullName2,
       ...
       MAX(CASE RN WHEN 1 THEN Email END) AS Email1,
       MAX(CASE RN WHEN 2 THEN Email END) AS Email2,
       ...
FROM RNs R
GROUP BY [KEY]; --KEY is a reserved keyword and should not be used for object names.

If you need a dynamic solution, that too is demonstrated in the linked duplicate candidate; the principle again is the same.