Good Question

I have some Lotus Notes files that I saved as Excel spreadsheets. I migrated
the files to SQL using DTS. One of the files has a table with 2 columns as
follows:
Room ID Number of Beds
201 3
202 2
I need to add a column to that table that will look into the combination of
each RoomID and Number Of Beds and will look like this:
Room ID
201A
201B
201C
202A
202B
So, because Room 201 has a capacity of 3 beds, it showed up as 201A, 201B
and 201C and so forth for the other rooms. What code I can use to accomplish
this?
Thanks a million
--
TSUse an auxiliary numbers table.
Example:
use northwind
go
create table t1 (
room_id int not null unique,
number_of_beds int not null check (number_of_beds between 1 and 5)
)
go
insert into t1 values(201, 3)
insert into t1 values(202, 2)
go
select
identity(int, 1, 1) as number
into
number
from
sysobjects as a
cross join
sysobjects as b
go
declare @.s varchar(255)
set @.s = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
select
ltrim(room_id) + substring(@.s, n.number, 1)
from
t1
inner join
number as n
on n.number <= t1.number_of_beds
order by
room_id,
n.number
go
drop table t1
go
drop table number
go
AMB
"TS" wrote:

> I have some Lotus Notes files that I saved as Excel spreadsheets. I migrat
ed
> the files to SQL using DTS. One of the files has a table with 2 columns as
> follows:
> Room ID Number of Beds
> 201 3
> 202 2
> I need to add a column to that table that will look into the combination o
f
> each RoomID and Number Of Beds and will look like this:
> Room ID
> 201A
> 201B
> 201C
> 202A
> 202B
> So, because Room 201 has a capacity of 3 beds, it showed up as 201A, 201B
> and 201C and so forth for the other rooms. What code I can use to accompli
sh
> this?
> Thanks a million
> --
> TS|||Hello TS,
CREATE TABLE [dbo].[Rooms] (
[RoomID] [int] NULL ,
[NumberofBeds] [int] NULL
) ON [PRIMARY]
GO
select * from Rooms
GO
RoomID NumberofBeds
-- --
200 3
201 4
202 2
select A.RoomID, B.Division
from Rooms A
Inner Join ( Select 'A' 'Division',1 'Sequence'
UNION ALL
select 'B',2
UNION ALL
select 'C',3
UNION ALL
select 'D',4
UNION ALL
select 'E',5
UNION ALL
select 'F',6 ) B
ON B.Sequence <= A.NumberofBeds
Thanks,
Gopi
"TS" <TS@.discussions.microsoft.com> wrote in message
news:07AF5AB5-BFD0-4E5D-90CE-316F267DAF4B@.microsoft.com...
>I have some Lotus Notes files that I saved as Excel spreadsheets. I
>migrated
> the files to SQL using DTS. One of the files has a table with 2 columns as
> follows:
> Room ID Number of Beds
> 201 3
> 202 2
> I need to add a column to that table that will look into the combination
> of
> each RoomID and Number Of Beds and will look like this:
> Room ID
> 201A
> 201B
> 201C
> 202A
> 202B
> So, because Room 201 has a capacity of 3 beds, it showed up as 201A, 201B
> and 201C and so forth for the other rooms. What code I can use to
> accomplish
> this?
> Thanks a million
> --
> TS|||Here is an example of creating a table with the two fields and populating th
e
standard values. Then modifying the table to add new column and updating th
e
value of that column depending on the number of rooms.
Create table #Table1
(
RoomID nvarchar (3),
NumOfBeds nvarchar (1)
)
Insert #Table1
values ('201','3')
Insert #Table1
values ('202','2')
Insert #Table1
values ('203','3')
Insert #Table1
values ('204','2')
select * from #table1
--Alter table to have GUID ID
Alter TABLE [#table1] ADD [RoomType] nvarchar (4)
GO
-- Update values for colum with conditions for number of beds
Update #Table1
Set Roomtype = RoomID+'A'
WHERE Numofbeds ='3'
Update #Table1
Set Roomtype = RoomID+'B'
WHERE Numofbeds ='2'
Select * from #Table1
Drop table #table1
Hope this helps guide you in the right direction.
"TS" wrote:

> I have some Lotus Notes files that I saved as Excel spreadsheets. I migrat
ed
> the files to SQL using DTS. One of the files has a table with 2 columns as
> follows:
> Room ID Number of Beds
> 201 3
> 202 2
> I need to add a column to that table that will look into the combination o
f
> each RoomID and Number Of Beds and will look like this:
> Room ID
> 201A
> 201B
> 201C
> 202A
> 202B
> So, because Room 201 has a capacity of 3 beds, it showed up as 201A, 201B
> and 201C and so forth for the other rooms. What code I can use to accompli
sh
> this?
> Thanks a million
> --
> TS|||Thanks a lot. Your code did exactly what I was looking for. Now the only
thing I need in order to finish the conversion is to include the description
next to the room id as follows:-
This is how the table looked like before applying your code:
Room Capacity Description
201 2 Small Single
202 1 Large Double
This is how the table looks like now after applying your code
RoomID
201A
201B
202A
What I need is to add another column to what I have now so the table will
look like this
RoomID Description
201A Small Single
201B Small Single
202A Large Double
What is the code for that.
Thank you for all your help.
TS
"Alejandro Mesa" wrote:
> Use an auxiliary numbers table.
> Example:
> use northwind
> go
> create table t1 (
> room_id int not null unique,
> number_of_beds int not null check (number_of_beds between 1 and 5)
> )
> go
> insert into t1 values(201, 3)
> insert into t1 values(202, 2)
> go
> select
> identity(int, 1, 1) as number
> into
> number
> from
> sysobjects as a
> cross join
> sysobjects as b
> go
> declare @.s varchar(255)
> set @.s = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
> select
> ltrim(room_id) + substring(@.s, n.number, 1)
> from
> t1
> inner join
> number as n
> on n.number <= t1.number_of_beds
> order by
> room_id,
> n.number
> go
> drop table t1
> go
> drop table number
> go
>
> AMB
>
> "TS" wrote:
>