This is a quite strange requirement. I would suggest this type of odd manipulation is probably better left to the front end.

But the query is pretty straight forward. This will produce the output you are looking for.

declare @Something table
(
    A char(1)
    , B int
    , C int
)
insert @Something values
('X', 1, 2)
, ('Y', 3, 4)
, ('Z', 5, 6)

select CONCAT(A, '_B')
    , B
from @Something s

union all

select CONCAT(A, '_C')
    , C
from @Something s

Something like this mayhaps:

;WITH cte AS (
    SELECT  *
    FROM    (VALUES
            (N'X', 1, 2)
        ,   (N'Y', 3, 4)
        ,   (N'Z', 5, 6)
    ) t (A,B,C)
)
SELECT  CONCAT(c.A, '_', postfix) AS A, value AS new
FROM    CTE c
CROSS APPLY
    (VALUES
        (B, 'B'),
        (C, 'C')
    ) v (value, postfix)
ORDER BY
    postfix,
    A;

With help of CROSS APPLY (VALUES , you explode your rows into two rows, one for B and one for C

You can use the UNPIVOT() function for this, e.g.

DECLARE @T TABLE (A CHAR(1), B INT, C INT);
INSERT @T (A, B, C)
VALUES ('X', 1, 2), ('Y', 3, 4), ('Z', 5, 6);

SELECT  A = CONCAT(upvt.A, '_', upvt.X), upvt.New
FROM    @T AS t
        UNPIVOT (New FOR X IN (B, C)) AS upvt
ORDER BY upvt.X, upvt.A;