You are going to have to learn about Database Normalization - here's a (very) brief starter The Basics of Database Normalization
Let's start with the easy bit ...
. You already have the basic table in your question
So let's just create a database table based on that information and add some data to it...
create table Products
ProdID int identity(1,1),
insert into Products values
('ProductA', 100, 'USD'),
('ProductB', 200, 'USD'),
('ProductC', 300, 'USD')
There are some very important points to note here:
1. The product value is numeric so
will be stored for ProductA and not
store values in the most appropriate column types. Do not
use varchar for anything other than character data.
2. I have given each Product a unique integer id number
- this is the most efficient way of linking tables
3. When inserting the data I have not give the ProdID for each product - SQL Server will assign that number for me automatically. It will always be unique but won't necessarily be consecutive numbers (read up on IDENTITY columns if you want to know why).
The contents of that table are now
ProdID ProdName ProdValue Currency
1 ProductA 100.00 USD
2 ProductB 200.00 USD
3 ProductC 300.00 USD
Now let's turn our attention to your comment
Quote:and also for the different type of sites have different types of data attached to it.
eg for hospitals it will be a number of beds and for hotels number of rooms.
There is enough of a hint here to suggest that we're going to need a table to hold information about Site types
. Let's create a simple table for that:
create table SiteTypes
TypeId int identity(1,1),
insert into SiteTypes values
Notice I've given each SiteType a unique reference just like I did with each Product. So now that table looks like:
TypeId TypeName ItemName
1 Hospital Beds
2 Hotel Rooms
Let's have a stab at creating our main table now
create table Sites
SiteId int identity(1,1),
When we insert data into the table this time, we don't want "Hospital", we want the TypeId for "Hospital". And we'll populate the
column from that little sub-table in your question
|abcd |488 |
|abcd2 |844 |
I'll insert the data from your main datasheet plus an extra 25-bed "Hotel" just to show what's going on
insert into Sites values
('a hotel','another street',2, 25)
Notice I haven't included anything in this table about the Products.
This is a key point about database design. You should always expect changes to be required sometime in the future!
At this point, having space for 3 products only works for you. If a fourth product comes along, all you have to do is add another column in Excel. Not a huge piece of work. But on your matching database table you would have to add another column to the
table. This might take some time if you have a lot of sites. And then what do you do when a fifth product comes along... and a sixth.
So we have a
table and we set up a
table earlier. What we need is a "linking" table that doesn't just give us information about which
products are associated with each site, but also how many of each product
are associated with each site. Something like this:
create table SiteProducts
insert into SiteProducts values
Now if ProductD comes along all we have to do is add a row of data to the
table and then as many rows as we need to the linking table
- no fiddly schema updates required.
Just to prove you can get back to the original spreadsheet you can use this query:
SELECT SiteId, SiteName, Address1, TypeName, items,
ItemName , ISNULL([ProductA],0),ISNULL([ProductB],0),ISNULL([ProductC],0), ISNULL(tot,0)
select S.SiteId, SiteName, Address1, TypeName, items, ItemName , P.ProdName, SP.Num, SUM(SP.Num) OVER(PARTITION BY S.SiteId) as tot
from Sites S
inner join SiteTypes ST on S.SiteType=ST.TypeId
left outer join SiteProducts SP on SP.SiteId=S.SiteId
left outer join Products P on P.ProdID=SP.ProdId
Sum(Num) for ProdName in ([ProductA],[ProductB],[ProductC])
which returns the following results
1 abcd Street Hospital 488 Beds 1 2 3 6
2 abcd2 Street Hospital 844 Beds 2 3 4 9
3 a hotel another street Hotel 25 Rooms 0 0 0 0
I'm not going to go into detail about the query as it's a bit early for that, I only included it to show that you can combine all of the tables back together again in quite complex ways if you have/want to.