Click here to Skip to main content
Click here to Skip to main content

Extracting Multiple Rows from a Single Row (TSQL)

, 9 Sep 2010
Rate this:
Please Sign up or sign in to vote.
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 [...]

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 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 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:


License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

Raymund Macaalay
Technical Lead
New Zealand New Zealand
http://nz.linkedin.com/in/macaalay
http://macaalay.com/
Follow on   Twitter   Google+   LinkedIn

Comments and Discussions

 
QuestionThis article is incorrect PinmemberMarshamMarty27-Dec-13 4:34 
GeneralMy vote of 5 Pinmemberaeternam8-Dec-10 4:06 
GeneralMy vote of 5 Pinmemberchuddy0913-Sep-10 22:45 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web02 | 2.8.140721.1 | Last Updated 9 Sep 2010
Article Copyright 2010 by Raymund Macaalay
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid