You need to learn about Database Normalisation
The Basics of Database Normalization[
^]
1NF, 2NF, 3NF and BCNF in Database Normalization | DBMS Tutorial | Studytonight[
^]
Database Normalization Techniques[
^]
In this case you could (for example) have tables for Customer, Product and Orders.
create table Customer
(
Id int identity(1,1) Primary Key,
CustId nvarchar(50),
CustName nvarchar(125),
CustType char(1)
)
create table Product
(
Id int identity(1,1) Primary Key,
ProdID nvarchar(50),
ProdName nvarchar(125)
)
create table Orders
(
OrderId int identity(1,1),
C_Id int,
P_Id int,
Units int,
PurchaseDate date
)
For the data you included in your example you would have these values
insert into Customer values ('100JSBTM', 'Mr.James Butt','R')
insert into Product values
('1001SVST1', 'SVS1T1'),
('1001SVST2', 'SVS1T2'),
('1001SVST3', 'SVS1T3')
insert into Orders values
(1,1,5,'2016-6-15'),
(1,2,5,'2016-6-15'),
(1,3,5,'2016-6-15')
And then you would get your results table back using this query
SELECT CustName, CustId, CustType, ProdID, ProdName, Units, PurchaseDate
FROM Orders O
INNER JOIN Customer C ON O.C_Id = C.Id
INNER JOIN Product P ON O.P_Id = P.Id
Note my example is not optimised - see the links for further ideas