Table Value Constructors in T-SQL

Here’s the Microsoft documentation on Table Value Constructors, available in SQL Server 2008+
In short, a table value constructor lets you create and populate a table on the fly, right in the middle of a query.

Quiz: Table Value Constructors in TSQL

Test it yourself: copy the code sample on each question, they are each runnable against SQL Server in whatever test database you have handy.
  • SELECT *
    FROM (VALUES
       ('Spider Plant', 'Chlorophytum comosum', 'airplane plant'),
       ('Aloe Vera', 'Aloe vulgari', 'Burn Plant')
    )as v(name1, name2, name3);
  • SELECT *
    FROM ( VALUES
       ('Heart Leaf Philodendron'),
       ('Philodendron cordatum')
    ) as v(name1)
    CROSS APPLY ( VALUES
       ('green'),
       ('leafy')
    ) as v2(name1);
  • SELECT *
    FROM (VALUES
       ('Succulents'),
       ('Cacti')
    ) as v(thing)
    CROSS APPLY (VALUES
       (v.thing + ' are friends')
    ) as v2(thing);
  • SELECT v.plant, v2.faves
    FROM (VALUES
       ('Ferns', 'being spritzed', 'shade', 'humidity'),
       ('Succulents', 'well drained soil', 'to dry out a bit', 'brighter light')
    ) as v(plant, fav1, fav2, fav3)
    CROSS APPLY ( VALUES
       (fav1),
       (fav2),
       (fav3)
    ) as v2(faves);

4 thoughts on “Table Value Constructors in T-SQL

  1. That last question made me think!! It opens up a way of doing a simple pivot – very useful – thanks!

    1. I know, right!?!? That pattern is so cool, I tried to write the series of questions building up to it.

  2. One neat trick I love to do with the values clause is to perform aggregates across columns in the same row:

    SELECT [MaxVal] = (SELECT MAX(Num) FROM (VALUES (sv.number), (sv.status), (4)) AS RetryCounts(Num)), *
    FROM master..spt_values sv

    SELECT [MaxVal] = (SELECT AVG(Num) FROM (VALUES (sv.number), (sv.status), (4)) AS RetryCounts(Num)), *
    FROM master..spt_values sv

    1. Oh, I see! It builds on the “unpivot” pattern and then uses an aggregate on it. LOVE IT! Adapting my plant theme to this…

      SELECT MAX(v2.singlecol) as MAXVAL
      FROM (VALUES 
              (1, 5, 6, 10),
              (7, 2, 1, 5)
          ) as v(fern, succulent, cacti, philodendron)
      CROSS APPLY ( VALUES 
              (fern),
              (succulent),
              (cacti),
              (philodendron)
          ) as v2(singlecol);
      

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.