SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE dbo.SomeTable
(
  ColumnA int,
  ColumnB varchar(4),
  ColumnC varchar(3),
  ColumnD varchar(3)
);
INSERT INTO
  dbo.SomeTable (ColumnA, ColumnB, ColumnC, ColumnD)
VALUES
  (123, '1abc', 'def', 'ghi'),
  (123, '2abc', 'def', 'ghi'),
  (123, '3abc', 'def', 'ghi'),
  (456, '1abc', 'def', 'ghi'),
  (456, '2abc', 'def', 'ghi')
;
5 rows affected

SELECT
  x.ColumnA, x.ColumnB, x.ColumnC, x.ColumnD
FROM
  dbo.SomeTable AS t
  CROSS APPLY
  (
    VALUES
      (t.ColumnA, NULL, NULL, NULL),
      (NULL, t.ColumnB, NULL, NULL),
      (NULL, NULL, t.ColumnC, NULL),
      (NULL, NULL, NULL, t.ColumnD)
  ) AS x (ColumnA, ColumnB, ColumnC, ColumnD)
GROUP BY
  t.ColumnA, x.ColumnA, x.ColumnB, x.ColumnC, x.ColumnD
ORDER BY
  t.ColumnA ASC,
  IIF(x.ColumnA IS NULL, 1, 0) ASC, x.ColumnA ASC,
  IIF(x.ColumnB IS NULL, 1, 0) ASC, x.ColumnB ASC,
  IIF(x.ColumnC IS NULL, 1, 0) ASC, x.ColumnC ASC,
  IIF(x.ColumnD IS NULL, 1, 0) ASC, x.ColumnD ASC
;


ColumnA
ColumnB
ColumnC
ColumnD


123
null
null
null

null
1abc
null
null

null
2abc
null
null

null
3abc
null
null

null
null
def
null

null
null
null
ghi

456
null
null
null

null
1abc
null
null

null
2abc
null
null

null
null
def
null

null
null
null
ghi

ColumnA	ColumnB	ColumnC	ColumnD
123	null	null	null
null	1abc	null	null
null	2abc	null	null
null	3abc	null	null
null	null	def	null
null	null	null	ghi
456	null	null	null
null	1abc	null	null
null	2abc	null	null
null	null	def	null
null	null	null	ghi