Combined Semi-transpose of a data

Question

Consider the following query :

DECLARE @T1 TABLE(
  [Id] [int] IDENTITY(1,1) NOT NULL,
  [Data] VARCHAR(100),
  [Column1] VARCHAR(100),
  [Column2] VARCHAR(100),
  [Column3] VARCHAR(100));

INSERT INTO @T1([Data],[Column1],[Column2],[Column3])
    VALUES
        ('Data1','C11','C21','C31'),
        ('Data2','C12','C22','C32'),
        ('Data3','C13','C23','C33'),
        ('Data4','C14','C24','C34'),
        ('Data5','C15','C25','C35');

SELECT * FROM @T1;

The output looks like the following:

Now we want to keep the Data column and for each other column stack the result of select for that column into the final table. In other words the following query produces the output:

-- I am looking for a better solution than below!
DECLARE @output TABLE([Data] VARCHAR(100),[Column] VARCHAR(100));

INSERT INTO @output([Data],[Column])
    (SELECT [Data],[Column1] FROM @T1
      UNION
    SELECT [Data],[Column2] FROM @T1
      UNION
    SELECT [Data],[Column3] FROM @T1)

SELECT * FROM @output

What would be a better cleaner approach than above to produce the final output? As the number of columns increases it means for every single new column I need to have a separate insert which appears to be a crude solution. Ideally I am looking for a pivot-based solution but I couldn't come up with something concrete.

Answer 1

I often use apply instead of union :

select t1.data, t2.cols
from @t1 t1 cross apply
     ( values ([column1]), ([column2]), ([column3]) ) t2(cols);