About these ads

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 cant find any results so I will create one, most of the results I have seen is 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 reuse it when performing the bottom select but not, it will still do a clustered index scan everytime 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 draw back 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

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 197 other followers

%d bloggers like this: