Explode records multiple times based on column.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Explode records multiple times based on column.

Author

Topic

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2012-01-12 : 15:21:09

How can I insert records into a tempt table multiple times, based on the numeric value of one of it's columns?

In the below example, For row 1, I want to insert 1 record because it's columnB = 1. For row 2, I want to insert 2 records because it's columnB = 2. For row 3, I want to insert 3 records, and so on....

COLA COLB
TEST 1
TEST2 2
TEST3 3

I am having a difficult time trying to explain what I am trying to do, but hopefully it makes sense to someone.

Thanks!

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-12 : 15:25:58

I...do not know...without using a CURSOR that is

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2012-01-12 : 15:28:02

Cursor is fine, I am just trying to find a working example.
Was also thinking that some sort of while loop would work....

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-01-12 : 16:07:16

No cursor needed, just a tally table (or CTE if you don't have one handy):

CREATE TABLE #one(COLA VARCHAR(20), COLB INT)
CREATE TABLE #two(COLA VARCHAR(20))

INSERT #one VALUES('TEST',1)
INSERT #one VALUES('TEST2',2)
INSERT #one VALUES('TEST3',3)

;WITH tally(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM tally WHERE n<100)
	INSERT #two
	SELECT COLA FROM #one CROSS JOIN tally WHERE n<=COLB

SELECT * FROM #two

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2012-01-12 : 20:42:45

This is essentially what I am trying to do:

http://www.experts-exchange.com/Hardware/Handhelds_-_PDAs/Blackberry/Q_24325715.html

I have a table of product records which includes a field with the qty on hand. I need to query that table returning as many rows for each product record as the value in the qty on hand field.

What is the best way to 'explode' the rows based upon the value in the qty field?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-01-13 : 07:06:37

Did you try the solution I posted? You don't really need a UDF to do it, and you don't want to use the one you posted if you have large (1000+) rows to be exploded due to performance issues. That UDF could be rewritten to be like the solution I posted and maintain high performance.

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2012-01-13 : 09:03:26

Hi Robvolk, I am about to try your solution. I just wanted to post the link because it explained a similar task better than my original post.

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2012-01-13 : 09:34:59

It appears your solution works, but I am unclear as to what the below statement is really doing?

;WITH tally(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM tally WHERE n<100)
INSERT #two
SELECT COLA FROM #one CROSS JOIN tally WHERE n<=COLB

Thanks
Qman

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-01-13 : 15:42:09

It's just generating numbers. You could replace all that with a table of numbers:

CREATE TABLE Tally(n int not null PRIMARY KEY)
...Insert statements, values from 1 to 100 or whatever upper limit you need...

INSERT #two
SELECT COLA FROM #one CROSS JOIN Tally WHERE n<=COLB

Here's an article with another application of a tally table:

http://www.sqlteam.com/article/parsing-csv-values-into-multiple-rows

And if you search SQLTeam and other SQL sites for "tally" you'll find a ton more examples.

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2012-01-16 : 07:39:28

Great, thanks Rob!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources