CODEWITHSUNDEEP
sql-servert-sqlsql-server-2012
scholzr
scholzr

Reputation: 245

multiple rows in query from one row

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.

Upvotes: 2

Views: 5150

Answers (3)

EricZ
EricZ

Reputation: 6205

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

Upvotes: 0

Sebastian Meine
Sebastian Meine

Reputation: 11783

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

Upvotes: 2

Lamak
Lamak

Reputation: 70638

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.

Upvotes: 4

Related Questions