Home Select 2 rows with multiple columns into a single row with multiple columns from one table

Questions

Select 2 rows with multiple columns into a single row with multiple columns from one table

June 20, 2024

the table is this:

ID	Name	Serial	SentYN	Data	Type
1	test1	SerialA	1	2024-06-20	EX1
2	test2	SerialB	0	2024-06-19	Ex2

I want this result please:

ID1	ID2	Name1	Name2	Serial1	Serial2	SentYN2	SentYN2	Data1	Data2	Type1	Type2
1	2	test1	test2	SerialA	SerialB	1	0	2024-06-20	2024-06-19	EX1	Ex2

I try this:

SELECT 
    CASE WHEN Type = 'EX1' THEN ID END AS ID1,
        CASE WHEN Type = 'EX2' THEN ID END AS ID2,
        CASE WHEN Type = 'EX1' THEN Name END AS Name1,
        CASE WHEN Type = 'EX2' THEN Name END AS Name2,
        CASE WHEN Type = 'EX1' THEN Serial END AS Serial1,
        CASE WHEN Type = 'EX2' THEN Serial END AS Serial2,
        CASE WHEN Type = 'EX1' THEN SentYN END  AS SentYN1,
        CASE WHEN Type = 'EX2' THEN SentYN END  AS SentYN2,
        CASE WHEN Type = 'EX1' THEN Data END AS Data1,
        CASE WHEN Type = 'EX2' THEN Data END AS Data2,
FROM table WITH (NOLOCK)

>Solution :

If you know you are after exactly two rows, and know how to identify them (e.g. by ID), you could consider something like this:

DECLARE @ParentRow int = 1, @ChildRow int = 2;

SELECT ID1     = p.ID,     ID2     = c.ID, 
       Name1   = p.Name,   Name2   = c.Name,
       Serial1 = p.Serial, Serial2 = c.Serial,
       SentYN1 = p.SentYN, SentYN2 = c.SentYN,
       Data1   = p.Data,   Data2   = c.Data,
       Type1   = p.[Type], Type2   = c.[Type]
FROM dbo.[table] AS p
CROSS APPLY
(
  SELECT * FROM dbo.[table] WHERE ID = @ChildRow
) AS c
WHERE p.ID = @ParentRow;