T-SQL Splitting rows into multiple rows

Question

I have a unique request here. I have to split rows for each ID into separate rows, but moving sets of columns into separate rows. Kind of hard to explain with just that so here is a visual representation:

I have to get something like this:

            Column A    |   Column B    |   Column C    |   Column D
    ------------------------------------------------------------
    1       123         |    1abc       |     def       |      ghi
    ------------------------------------------------------------
    2       123         |    2abc       |     def       |      ghi
    ------------------------------------------------------------
    3       123         |    3abc       |     def       |      ghi
    ------------------------------------------------------------
    4       456         |    1abc       |     def       |      ghi
    ------------------------------------------------------------
    5       456         |    2abc       |     def       |      ghi

To look like this:

            Column A    |   Column B    |   Column C    |   Column D
    ---------------------------------------------------------------------
    1       123         |               |               |      
    ---------------------------------------------------------------------
    2                   |    1abc       |               |      
    ---------------------------------------------------------------------
    3                   |    2abc       |               |      
    ---------------------------------------------------------------------
    3                   |    3abc       |               |      
    ---------------------------------------------------------------------
    4                   |               |     def       |      
    ---------------------------------------------------------------------
    5                   |               |               |      ghi
    ---------------------------------------------------------------------
    6       456         |               |               |      
    ---------------------------------------------------------------------
    7                   |    1abc       |               |      
    ---------------------------------------------------------------------
    8                   |    2abc       |               |      
    ---------------------------------------------------------------------
    9                   |               |     def       |      
    ---------------------------------------------------------------------
    10                  |               |               |      ghi

I have a slight understanding that I will have to loop through the recordset then possibly use a CTE. This would then move onto the next ID in Column A and on.

Any ideas would be appreciated!

Answer 1

WITH cte AS (
SELECT *, DENSE_RANK() OVER (ORDER BY ColumnA) dr
FROM source
),
cte2 AS (
SELECT DISTINCT ColumnA, NULL ColumnB, NULL ColumnC, NULL ColumnD, dr
FROM cte
UNION ALL
SELECT DISTINCT NULL, ColumnB, NULL, NULL, dr
FROM cte
UNION ALL
SELECT DISTINCT NULL , NULL, ColumnC, NULL, dr
FROM cte
UNION ALL
SELECT DISTINCT NULL, NULL, NULL, ColumnD, dr
FROM cte
)
SELECT ColumnA, ColumnB, ColumnC, ColumnD 
FROM cte2
ORDER BY dr, ColumnA DESC, ColumnB DESC, ColumnC DESC, ColumnD DESC

fiddle

PS. Edit final ORDER BY clause (except dr first) as you need.

Answer 2

You can unfold each row using CROSS APPLY and then get distinct rows from the result:

SELECT DISTINCT
  t.ColumnA AS ColGrp, 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)
ORDER BY
  t.ColumnA ASC,
  x.ColumnA DESC,
  x.ColumnB DESC,
  x.ColumnC DESC,
  x.ColumnD DESC
;

The output will include an extra column:

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

If it bothers you, you can exclude it by using nesting:

SELECT
  ColumnA, ColumnB, ColumnC, ColumnD
FROM
  (
    SELECT DISTINCT
      t.ColumnA AS ColGrp, 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)
  ) AS s
ORDER BY
  ColGrp ASC,
  ColumnA DESC,
  ColumnB DESC,
  ColumnC DESC,
  ColumnD DESC
;

Alternatively, you can use a different method to get distinct rows – grouping. This way you can exclude the extra column without nesting:

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,
  x.ColumnA DESC,
  x.ColumnB DESC,
  x.ColumnC DESC,
  x.ColumnD DESC
;

In either case the output will look like this:

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

Still not quite matching your output because the ORDER BY in both cases reverses the order of values in columns B, C and D. This is the shortest way to make the values go together the way you have shown in your desired output. In order to also match the order precisely, SQL Server would need to support the NULLS FIRST/LAST modifier in ORDER BY, which would allow you to resolve the issue quite elegantly, like this:

ORDER BY
  t.ColumnA ASC,
  x.ColumnA ASC NULLS LAST,
  x.ColumnB ASC NULLS LAST,
  x.ColumnC ASC NULLS LAST,
  x.ColumnD ASC NULLS LAST

However, this syntax is not supported, and in absence of its support it is hard to achieve the same row order without making the ORDER BY clause a little unwieldy. Here is the most concise I could manage:

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

Full query, for completeness:

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
;

You can play with this solution in a live demo at db<>fiddle.uk.