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)
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;