multiple rows in query from one row

Question

I am trying to create a query from my database which will expand each row to multiple rows based on a count column. What I need is to take my table:

[DATE]     [COUNT]     [OTHER_COLUMN]
20120101   3           X
20120201   2           Y

And create this:

[DATE]     [OTHER_COLUMN]
20120101   X
20120101   X
20120101   X
20120201   Y
20120201   Y

I have found lots of ways to do the opposite (merge multiple rows into one), or to expand one row with multiple columns into multiple rows (one based on each column), but I can't seem to find a way to create identical rows. I am using SQL Server 2012.

Lamak · Accepted Answer · 2013-01-03 20:40:14Z

4

You can use a recursive CTE:

;WITH CTE AS
(
    SELECT *, 1 RN
    FROM YourTable
    UNION ALL
    SELECT [DATE], [COUNT], [OTHER_COLUMN], RN+1
    FROM CTE
    WHERE RN+1 <= [COUNT]
)
SELECT [DATE], [OTHER_COLUMN]
FROM CTE
OPTION(MAXRECURSION 0)

And here is a sql fiddle for you to try.

answered Jan 3, 2013 at 20:40

Lamak

70.8k12 gold badges119 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Lamak Over a year ago

@bluefeet Thanks, added a fiddle now

Sebastian Meine Over a year ago

This is causing the table to get scanned once per recursion step. If the table grows or the counts grow this is going to be very slow.

Lamak Over a year ago

@SebastianMeine It doesn't necessarily scan the table once per recursion, actually. In the fiddle in question, the query scan the table once. Though, this doesn't mean necessarily that its not gonna be slow either

Sebastian Meine Over a year ago

I just ran your version with STATISTICS IO on. The output shows 1 logical read on the table and 29 logical reads on a worktable. So yes, you are right that the table gets scanned only once, however the total is still 30 logical reads. My solution gets away with one logical read.

Lamak Over a year ago

@SebastianMeine That's great, actually. This is just one alternative to the problem asked. I'm in no way over defending my answer nor criticizing yours.

RBarryYoung · Accepted Answer · 2013-01-03 21:11:55Z

2

For this you need a tally table like this one: http://www.sqlmag.com/article/sql-server/virtual-auxiliary-table-of-numbers

Then you can just write:

SELECT [Date], Other_Column
FROM dbo.MyTable
CROSS APPLY dbo.GetNums([COUNT]);

Here is a SQL-Fiddle to show the solution in action: http://sqlfiddle.com/#!6/2edda/2

edited Jan 3, 2013 at 21:11

RBarryYoung

57k15 gold badges99 silver badges143 bronze badges

answered Jan 3, 2013 at 20:37

Sebastian Meine

11.9k1 gold badge34 silver badges44 bronze badges

Comments

EricZ · Accepted Answer · 2013-01-03 21:27:05Z

0

Another way to do it. Please note that you should have [count] <=2048. Otherwise, build another number table as you need.

WITH num AS (
    SELECT number
    FROM master..spt_values WHERE type = 'P' 
)
SELECT 
    [DATE],[OTHER_COLUMN]
FROM your_table t
INNER JOIN num n
    ON n.number < t.COUNT

answered Jan 3, 2013 at 21:27

EricZ

6,2151 gold badge33 silver badges30 bronze badges

Collectives™ on Stack Overflow

multiple rows in query from one row

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related