view help - 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

view help

Author

Topic

Ranjit.ileni
Posting Yak Master

183 Posts

Posted - 2011-05-31 : 08:19:43

Hi,

Here is the table

create table #temp
(
subcustid int
,paxcount int
)

insert into #temp
select 2,5 union all
select 3,10 union all
select 4,2 union all
select 5,9

Expected output:

for example subcustid 2 ---> paxcount 5
i need to get 5 rows for subcustid 2 as 2/p1,2/p2,2/p3,2/p4,2/p5

i need to retrive this results from a VIEW

subcustId/pax
2/p1
2/p2
2/p3
2/p4
2/p5
3/p1
3/p2
3/p3
3/p4
3/p5
3/p6
3/p7
3/p8
3/p9
3/p10
4/p1
4/p2
5/p1
5/p2
5/p3
5/p4
5/p5
5/p6
5/p7
5/p8
5/p9

Here /p is static ..

Thanks,

--Ranjit

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-05-31 : 08:26:22

;with cte
as
(
select subcustid,paxcount, 1 as NextRow
from #temp
union all
select subcustid,paxcount, NextRow+1
from cte
where nextrow < paxcount)

select subcustid,paxcount,'p' + convert(varchar(10),NextRow)
from cte

order by 1,2

Jim

Everyday I learn something that somebody else already knew

Ranjit.ileni
Posting Yak Master

183 Posts

Posted - 2011-05-31 : 08:28:19

Thanks Jimf....

Actually recursive CTE Idea not came to mind...

--Ranjit

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-05-31 : 08:49:03

You're welcome. Recursive CTEs are finally start to come to my mind first. I usually would have solved this one using master..spt_Values, and then someone else would solve it better with the CTE.

Jim

Everyday I learn something that somebody else already knew

dujiaojing0
Starting Member

12 Posts

Posted - 2011-05-31 : 21:06:41

That's great answer from Jim. Really appreciate that. Thanks.

Simon

unspammed

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources