July 2, 2007 at 7:23 pm
Hi everyone,
It's a bit different requirement. I want to generate duplicate records based on the value in one column. Here is the snapshot of data in my table
DECLARE @rInv TABLE (RoomInventoryID int, Rate numeric(10,2), Date datetime, PersonsInRoom int default 0)
insert into @rinv (RoomInventoryID, Rate, Date, PersonsInRoom ) values (1710, 145.00, '14-Jul-2008', 2)
insert into @rinv (RoomInventoryID, Rate, Date, PersonsInRoom ) values (1711, 145.00, '14-Jul-2008', 2)
insert into @rinv (RoomInventoryID, Rate, Date, PersonsInRoom ) values (1712, 145.00, '14-Jul-2008', 3)
insert into @rinv (RoomInventoryID, Rate, Date, PersonsInRoom ) values (1713, 145.00, '14-Jul-2008', 1)
insert into @rinv (RoomInventoryID, Rate, Date, PersonsInRoom ) values (1714, 145.00, '14-Jul-2008', 2)
select*
from@rinv
RoomInventoryID Rate Date PersonsInRoom
1710 145.00 2008-07-14 00:00:00.000 2
1711145.002008-07-14 00:00:00.0002
1712145.002008-07-14 00:00:00.0003
1713145.002008-07-14 00:00:00.0001
1714145.002008-07-14 00:00:00.0002
so, based on the above data, I need to get duplicate rows based on the value of PersonsInRoom i.e. 2 rows for RoomID 1710, 2 rows for RoomID 1711, 3 rows for RoomID 1712 and so on.
So as an end result, my output should be
RoomInventoryID Rate Date
1710 145.00 2008-07-14 00:00:00.000
1710 145.00 2008-07-14 00:00:00.000
1711 145.00 2008-07-14 00:00:00.000
1711 145.00 2008-07-14 00:00:00.000
1712 145.00 2008-07-14 00:00:00.000
1712 145.00 2008-07-14 00:00:00.000
1712 145.00 2008-07-14 00:00:00.000
and so on....
There might be some simple solution but I'm stuck and can't think on how to solve this. Any suggestions welcome!
Thanks,
- Harish
July 2, 2007 at 11:15 pm
Hi
Other than implementing a loop i cant think of another solution to this now.
Anybody having better ideas.....
"Keep Trying"
July 3, 2007 at 12:08 am
DECLARE
@rInv TABLE (RoomInventoryID int, Rate numeric(10,2), Date datetime, PersonsInRoom int default 0)
insert
into @rinv (RoomInventoryID, Rate, Date, PersonsInRoom ) values (1710, 145.00, '14-Jul-2008', 2)
insert
into @rinv (RoomInventoryID, Rate, Date, PersonsInRoom ) values (1711, 145.00, '14-Jul-2008', 2)
insert
into @rinv (RoomInventoryID, Rate, Date, PersonsInRoom ) values (1712, 145.00, '14-Jul-2008', 3)
insert
into @rinv (RoomInventoryID, Rate, Date, PersonsInRoom ) values (1713, 145.00, '14-Jul-2008', 1)
insert
into @rinv (RoomInventoryID, Rate, Date, PersonsInRoom ) values (1714, 145.00, '14-Jul-2008', 2)
select
r.*
from
@rinv as r
cross
apply (
select s.number
from master..spt_values as s
where s.number between 1 and r.PersonsInRoom
and name is null
) as d
N 56°04'39.16"
E 12°55'05.25"
July 3, 2007 at 1:18 am
Thanks Peter, that's what exactly I was after. And I wasn't really interested in using loop.
Thank you very much!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply