Rows Breaking - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Rows Breaking

Author

Topic

goodman2253
Yak Posting Veteran

88 Posts

Posted - 2015-03-25 : 02:19:10

Hello
How are you guys, I stuck in one case and need your help. I have the data which looks like

Row1--Row2
T1--3
T2--4
T4--6

And I want the output

Row1--Row2
T1--1
T1--2
T1--3
T2--1
T2--2
T2--3
T2--4
T4--1
T4--2
T4--3
T4--4
T4--5
T4--6

I want to do this in one single sql query

mhorseman
Starting Member

44 Posts

Posted - 2015-03-25 : 04:14:13

There's probably a better way than this, but it seems to work, assuming I've understood your reqiest:

--set up data
drop table #table1
create table #table1(row1 varchar(5),row2 int)

insert into #table1 values
('T1',3),
('T2',4),
('T4',6)

--Create output
select t.row1,v.number from #table1 t join master..spt_values v
on t.row2 >= v.number and v.type = 'P' and v.number > 0

Mark

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2015-03-26 : 22:16:40

a slight variant of Mark's solution:




;with cte1 as 
(select Max(row2) as maxnum from #table1),
cte as
(
   select 1 as n
   union all
   select n+1
     from cte
    where n < (select maxnum from cte1)
)
select row1, n
  from cte join #table1 on n < row2;

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-27 : 08:29:15

@MuMu88 Recursive CTE. Really? Try that where you have a source row ('T5', 100000000)

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2015-03-27 : 16:53:30

At least MuMu's solution will work if you have ('T5', 100000000) if you have infinite computing resources and if you specify maxrecursion 0, but the spt_values solution definitely would not.

If you don't already have a numbers table (aka Tally table) in your database, creating one and using that instead of spt_values might perhaps be the best solution.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources