Click here to Skip to main content
15,867,686 members
Articles / All Topics

Extracting Multiple Rows from a Single Row (TSQL)

Rate me:
Please Sign up or sign in to vote.
5.00/5 (4 votes)
9 Sep 2010CPOL2 min read 24.6K   6   3
How to extract 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:

IDFirstNameLastName
1AnnaGates
2JohnDoe
3JoeBloggs
4RajKumar

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

IDFirstNameLastNameItemNumberItemDescription
1AnnaGates1Item 1
1AnnaGates2Item 2
1AnnaGates3Item 3
2JohnDoe1Item 1
2JohnDoe2Item 2
2JohnDoe3Item 3
3JoeBloggs1Item 1
3JoeBloggs2Item 2
3JoeBloggs3Item 3
4RajKumar1Item 1
4RajKumar2Item 2
4RajKumar3Item 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

SQL
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

SQL
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

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

Image 1

Image 2 Image 3 Image 4 Image 5 Image 6 Image 7

License

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


Written By
Technical Lead
New Zealand New Zealand
http://nz.linkedin.com/in/macaalay
http://macaalay.com/

Comments and Discussions

 
QuestionThis article is incorrect Pin
MarshamMarty27-Dec-13 4:34
MarshamMarty27-Dec-13 4:34 
GeneralMy vote of 5 Pin
aeternam8-Dec-10 4:06
aeternam8-Dec-10 4:06 
GeneralMy vote of 5 Pin
chuddy0913-Sep-10 22:45
chuddy0913-Sep-10 22:45 

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

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