Extracting Multiple Rows from a Single Row (TSQL)

I was tasked to create a query for an accounting journal entry from a transaction table with only amounts indicated, so if it’s a journal entry there should be a credit and debit but most of the other values will be similar, so I thought of extracting 2 rows from a single row through SQL query. Sounds confusing? I Googled for it and I could not find any results so I will create one, most of the results I have seen are combining multiple rows into one.

To make it simple, here is a sample.

For example, I have a Table called Sample Table and it contains the following records:

ID	FirstName	LastName
1	Anna	Gates
2	John	Doe
3	Joe	Bloggs
4	Raj	Kumar

Now you are tasked to create 3 lines for each record so it will show as:

ID	FirstName	LastName	ItemNumber	ItemDescription
1	Anna	Gates	1	Item 1
1	Anna	Gates	2	Item 2
1	Anna	Gates	3	Item 3
2	John	Doe	1	Item 1
2	John	Doe	2	Item 2
2	John	Doe	3	Item 3
3	Joe	Bloggs	1	Item 1
3	Joe	Bloggs	2	Item 2
3	Joe	Bloggs	3	Item 3
4	Raj	Kumar	1	Item 1
4	Raj	Kumar	2	Item 2
4	Raj	Kumar	3	Item 3

Now how do you achieve it? There are a lot of ways you can do it, but we are looking for the best way which means the most efficient query cost.

So here are some methods.

1. CROSS JOIN

SELECT SampleTable.ID, SampleTable.FirstName, SampleTable.LastName, _
	Extender.ItemNumber, Extender.ItemDescription
FROM SampleTable CROSS JOIN
(SELECT 1 AS ItemNumber, 'Item 1' AS ItemDescription
UNION ALL
SELECT 2 AS ItemNumber, 'Item 2' AS ItemDescription
UNION ALL
SELECT 3 AS ItemNumber, 'Item 3' AS ItemDescription) AS Extender;

2. UNION

SELECT SampleTable.ID, SampleTable.FirstName, SampleTable.LastName, _
	1 AS ItemNumber, 'Item 1' AS ItemDescription
FROM SampleTable
UNION ALL
SELECT SampleTable.ID, SampleTable.FirstName, SampleTable.LastName, _
	2 AS ItemNumber, 'Item 2' AS ItemDescription
FROM SampleTable
UNION ALL
SELECT SampleTable.ID, SampleTable.FirstName, SampleTable.LastName, _
	3 AS ItemNumber, 'Item 3' AS ItemDescription
FROM SampleTable;

3. WITH

WITH ExtendedTable(ID, FirstName, LastName) AS
(SELECT SampleTable.ID, SampleTable.FirstName, SampleTable.LastName
FROM SampleTable)
SELECT *, 1 AS ItemNumber, 'Item 1' AS ItemDescription from ExtendedTable
UNION ALL
SELECT *, 2 AS ItemNumber, 'Item 2' AS ItemDescription from ExtendedTable
UNION ALL
SELECT *, 3 AS ItemNumber, 'Item 3' AS ItemDescription from ExtendedTable

They all show the same results, but which is better?

Here are my initial thoughts:

I thought the CROSS JOIN and WITH will have the same performance initially as I thought the WITH would have stored the Query “SELECT SampleTable.ID, SampleTable.FirstName, SampleTable.LastName FROM SampleTable” in the memory and just reused it when performing the bottom select but not, it will still do a clustered index scan every time a Select is performed on the Extended Table. So CROSS JOIN will be the best as it does a clustered index scan once, and another drawback on using the WITH is that you can only use it on a SQL 2005 and above as it’s a Common Table Expressions.

Here is the execution plan for each to have a better view of what’s happening on each query: