Row Constructor in SQL Server Demo and Explanation

Added to SQL Server in 2008 Steve explains and demonstrates how to set up row constructor.

Transcription of video:

So there’s this thing called a row constructor. And this was added in SQL 2008. But it’s a way of saying select x from, and you give it a list of values around f of x or a function like this, that creates it as a row. So we’re going to do is select x from that list, and that gives us John Marion bill. And we’re going to do the same thing. Instead of using a union statement. We’re gonna use the row constructor to say grab these first names, grab these last names, and then cross join. So that’s just a little bit cleaner way of doing it with a CTE. To come up with the same list of names we saw above. Using the same row constructor method, we can do the same thing with first, middle and last. And we get first name, middle initial and last for again, 27 rows, where we have the initial CTE is just declared as with F names, we have the second one with a comma and then M initials. We have the third one with a comma and then l names. And then we’re selecting all those first name, middle initial last name from F names l names, M initial cross joint, doing multiple CTE’s, I mean, you can put these together with with a number of them. I’ve done a few dozen in some more complicated queries that probably should have been refactored in some way. You can have quite a few CTE’s combined into one big query if needed.

More from Stedman Solutions:

Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!