A---- Convention Pivote Table with TSQL:
/** 1 Prepare data holder */
create table #Table1
( RowNum int
, Name varchar(25)
, Math real
, Physics real
, Chemistry real
, Biology real
, col6 varchar(25)
, col7 varchar(25)
)
/** 2 Load data */
insert into #Table1
( RowNum
, Name
, Math
, Physics
, Chemistry
, Biology
)
SELECT 1, 'John', 95, 90, 100, 85
go
insert into #Table1
( RowNum
, Name
, Math
, Physics
, Chemistry
, Biology
)
SELECT 2, 'QQ', 94, 89, 99, 84
go
insert into #Table1
( RowNum
, Name
, Math
, Physics
, Chemistry
, Biology
)
SELECT 3, 'KK', 93, 88, 98, 83
go
insert into #Table1
( RowNum
, Name
, Math
, Physics
, Chemistry
, Biology
)
SELECT 4, 'AA', 92, 87, 97, 82
go
/** 3 test loaded data */
select * from #Table1
RowNum Name Math Physics Chemistry Biology col6 col7
------- ------ ------ ------- --------- -------- ----- -----
1 John 95.0 90.0 100.0 85.0 NULL NULL
2 QQ 94.0 89.0 99.0 84.0 NULL NULL
3 KK 93.0 88.0 98.0 83.0 NULL NULL
4 AA 92.0 87.0 97.0 82.0 NULL NULL
/** 4 extraction and transformation */
select RowNum, Name, Subject = 'Math', Math from #Table1
union all
select RowNum, Name, Subject = 'Physics', Physics from #Table1
union all
select RowNum, Name, Subject = 'Chemistry', Chemistry from #Table1
union all
select RowNum, Name, Subject = 'Biology', Biology from #Table1
union all
select RowNum, Name, Subject = 'col6', col6 from #Table1
union all
select RowNum, Name, Subject = 'col7', col7 from #Table1
order by
RowNum
, Name
, Subject
RowNum Name Subject Math
----------- ------------------------- --------- ------
1 John Biology 85.0
1 John Chemistry 100.0
1 John col6 NULL
1 John col7 NULL
1 John Math 95.0
1 John Physics 90.0
2 QQ Biology 84.0
2 QQ Chemistry 99.0
2 QQ col6 NULL
2 QQ col7 NULL
2 QQ Math 94.0
2 QQ Physics 89.0
3 KK Biology 83.0
3 KK Chemistry 98.0
3 KK col6 NULL
3 KK col7 NULL
3 KK Math 93.0
3 KK Physics 88.0
4 AA Biology 82.0
4 AA Chemistry 97.0
4 AA col6 NULL
4 AA col7 NULL
4 AA Math 92.0
4 AA Physics 87.0
(24 row(s) affected)
/** 5 clean up */
drop table #Table1
go
B --- Normalize table before application starts
B1, Student Table,
B2, Subject Table
B3, Student_Subject_Score table
C --- If not frequently happens, use Excel Pivate table service, it is a
simple way for most office people without programming background.
D-- This thing called Normalization in database design;
called ETL in data warehousing staging;
called Pivote table service in use application (excel).
E-- 1:56, good night.