CHill60
Вам придется узнать о нормализации базы данных - вот (очень) краткое начало Основы нормализации базы данных[^]
Давайте начнем с самого простого-чуть ... Products У вас уже есть основная таблица в вашем вопросе
+--------------+
|Product |Value|
|ProductA| $100|
|ProductB|$200 |
|ProductC|$300 |
+--------------+
Поэтому давайте просто создадим таблицу базы данных на основе этой информации и добавим в нее некоторые данные...
create table Products
(
ProdID int identity(1,1),
ProdName nvarchar(125),
ProdValue decimal(15,2),
Currency varchar(4)
)
insert into Products values
('ProductA', 100, 'USD'),
('ProductB', 200, 'USD'),
('ProductC', 300, 'USD')Здесь следует отметить несколько очень важных моментов:
1. значение продукта является числовым, поэтому
100 будет храниться продукта и
нет $100.
Всегда храните значения в наиболее подходящих типах столбцов. Делать
нет используйте varchar для чего-либо, кроме символьных данных.
2. Я дал каждому продукту уникальный целочисленный идентификатор
ProdID - это самый эффективный способ связывания таблиц
3. При вставке данных у меня нет, не дают продуктов, для каждого продукта - SQL-сервер будет назначать, что номер для меня автоматически. Он всегда будет уникальным, но не обязательно будет последовательными числами (читайте о столбцах идентификаторов, если хотите знать почему).
Содержимое этой таблицы теперь
ProdID ProdName ProdValue Currency
1 ProductA 100.00 USD
2 ProductB 200.00 USD
3 ProductC 300.00 USD
Теперь давайте обратимся к вашему комментарию
Цитата:
а также для разных типов сайтов есть разные типы данных, прикрепленных к нему.
например, для больниц это будет количество коек, а для гостиниц - количество комнат.
Здесь достаточно намека на то, что нам понадобится таблица для хранения информации о сайте
типы Давайте создадим для этого простую таблицу:
create table SiteTypes
(
TypeId int identity(1,1),
TypeName nvarchar(125),
ItemName nvarchar(125)
)
insert into SiteTypes values
('Hospital', 'Beds'),
('Hotel', 'Rooms')Обратите внимание, что я дал каждому типу сайта уникальную ссылку точно так же, как и каждому продукту. Так что теперь этот стол выглядит так:
TypeId TypeName ItemName
1 Hospital Beds
2 Hotel Rooms
Давайте теперь попробуем создать нашу главную таблицу
Sitescreate table Sites
(
SiteId int identity(1,1),
SiteName nvarchar(125),
Address1 nvarchar(125),
SiteType int,
items int
)
Когда мы вставляем данные в таблицу на этот раз, нам не нужна "больница", нам нужен TypeId для "больницы". И мы заселим
items колонка из этой маленькой подтаблицы в вашем вопросе
+-------------+
|Site |Beds|
|abcd |488 |
|abcd2 |844 |
+-------------+
Я вставлю данные из вашей основной таблицы данных плюс дополнительный 25-местный "отель", просто чтобы показать, что происходит
insert into Sites values
('abcd','Street',1,488),
('abcd2','Street',1,844),
('a hotel','another street',2, 25)Обратите внимание, что я ничего не включил в эту таблицу о продуктах.
Это ключевой момент проектирования баз данных. Вы всегда должны ожидать, что когда-нибудь в будущем потребуются изменения!
На данный момент наличие места для 3 продуктов работает только для вас. Если появляется четвертый продукт, все, что вам нужно сделать, это добавить еще один столбец в Excel. Не такая уж большая работа. Но в вашей соответствующей таблице базы данных вам придется добавить еще один столбец
Sites стол. Это может занять некоторое время, если у вас много сайтов. И что вы будете делать, когда появится пятый продукт?.. и шестой.
Итак, у нас есть
Sites стол, и мы установили
Products столик раньше. Что нам нужно, так это" связывающая " таблица, которая не просто дает нам информацию о
который продукты связаны с каждым сайтом, но также
сколько каждого продукта связаны с каждым сайтом. Что-то вроде этого:
create table SiteProducts
(
SiteId int,
ProdId int,
Num int
)
insert into SiteProducts values
(1,1,1),
(1,2,2),
(1,3,3),
(2,1,2),
(2,2,3),
(2,3,4)
Теперь, если появится ProductD, все, что нам нужно сделать, это добавить строку данных в
Products таблица, а затем столько строк, сколько нам нужно для связывания таблицы
SiteProducts - никаких дополнительных обновлений схемы не требуется.
Просто чтобы доказать, что вы можете вернуться к исходной электронной таблице, вы можете использовать этот запрос:
SELECT SiteId, SiteName, Address1, TypeName, items,
ItemName , ISNULL([ProductA],0),ISNULL([ProductB],0),ISNULL([ProductC],0), ISNULL(tot,0)
FROM
(
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
) base
PIVOT
(
Sum(Num) for ProdName in ([ProductA],[ProductB],[ProductC])
) pvt
который возвращает следующие результаты
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
Я не буду вдаваться в подробности этого запроса, так как для этого еще немного рано, я включил его только для того, чтобы показать, что вы можете объединить все таблицы обратно вместе довольно сложными способами, Если у вас есть/есть желание.