Row-to-column, column-to-row

-- ============================== ======================================== DROP table # student CREATE TABLE # student (stdname nvarchar (10 ), stdsubject nvarchar (10), result int) insert into # student VALUES ('zhang san', 'China', 80) insert into # student values ('zhang san', 'mat ', 90) insert into # student VALUES ('zhang san', 'Physical ', 85) insert into # student VALUES ('Li si', 'China', 85) insert into # student values ('Lee 4', 'mat', 92) insert into # student VALUES ('Lee 4', 'Physical ', 82) insert into # student VALUES ('Lee si', 'chemistry ', 82) ------ row SELECT * FROM # student ------ column select stdname, isnull (sum (case stdsubject when 'chemistry 'then Result end), 0) [chemistry], isnull (sum (case stdsubject when 'mate' then Result end), 0) [mathematics], isnull (sum (case stdsubject when 'physical 'then Result end), 0) [physical], isnull (sum (case stdsubject when 'then Result end ), 0) [language] from # student group by stdname -- refactor declare @ SQL varchar (1000) set @ SQL = 'select stdname, 'select @ SQL = @ SQL + 'sum (case when stdsubject = ''' + stdsubject + ''' then result else 0 end) as ['+ stdsubject +'], 'From (select distinct stdsubject from # student) Temp set @ SQL = LEFT (@ SQL, len (@ SQL)-1) + 'from # student Group by stdname' print @ SQL exec (@ SQL) -- ============================ ====================================== DROP table # student2 create table # student2 (stdname nvarchar (10 ), chemical int, mathematical int, physical int, Chinese int) insert into # student2 VALUES ('Li si',) insert into # student2 VALUES ('zhang san) -- column SELECT * FROM # student2 -- Row SELECT 'Li si' as stdname, stdname = 'Chemical ', chemistry as result from # student2 where stdname = 'lily' union all SELECT 'lily' as stdname, stdname = 'mat ', mathematical as result from # student2 where stdname = 'lily' union all SELECT 'lily' as stdname, stdname = 'physical ', physical as result from # student2 where stdname = 'lily' union all SELECT 'lily' as stdname, stdname = 'China ', language as result from # student2 where stdname = 'lily' union all SELECT 'zhangsan' as stdname, stdname = 'Chemical ', chemistry as result from # student2 where stdname = 'zhang san' union all SELECT 'zhang san' as stdname, stdname = 'mat ', mathematical as result from # student2 where stdname = 'zhang san' union all select' Zhang san' as stdname, stdname = 'physical ', physical as result from # student2 where stdname = 'zhang san' union all SELECT 'zhang san' as stdname, stdname = 'China ', language as result from # student2 where stdname = 'zhang san' -- restructured select [name] into # tmpCloumns from tempdb. dbo. syscolumns where id = object_id ('tempdb. dbo. # student2 ') and [name] <> 'stdname' -- select * from # tmpCloumnsdeclare @ strSql nvarchar (800) select @ strSql = ''select @ strSql = @ strSql + 'Union all' + char (10) + char (13) + 'select [stdname], ''' + [name] + ''' as [subject], ['+ [name] +'] '+ char (10) + char (13) + 'from [# student2] '+ char (10) + char (13) from # tmpCloumnsselect @ strSql = substring (@ strSql, 11, len (@ strSql )) + 'order by stdname, [subject] 'print @ strSqlexec (@ strsql)

-- ============================== ========================================
DROP table # student

Create table # student (stdname nvarchar (10), stdsubject nvarchar (10), result int)
Insert into # student VALUES ('zhang san', 'China', 80)
Insert into # student values ('zhang san', 'mat', 90)

Insert into # student VALUES ('zhang san', 'Physical ', 85)
Insert into # student VALUES ('Li si', 'China', 85)

Insert into # student values ('Lee 4', 'mat', 92)
Insert into # student VALUES ('lily', 'Physical ', 82)
Insert into # student VALUES ('Lee 4', 'chemistry ', 82)

------ Line
SELECT * FROM # student

------ Column
Select stdname, isnull (sum (case stdsubject when 'chemistry 'then Result end), 0) [chemistry], isnull (sum (case stdsubject when 'mate' then Result end ), 0) [mathematics], isnull (sum (case stdsubject when 'physical 'then Result end), 0) [physical], isnull (sum (case stdsubject when 'China' then Result end), 0) [language] from # student group by stdname

-- Refactoring
Declare @ SQL varchar (1000) set @ SQL = 'select stdname ,'
Select @ SQL = @ SQL + 'sum (case when stdsubject = ''' + stdsubject + ''' then result else 0 end) as ['+ stdsubject +'], 'From (select distinct stdsubject from # student) Temp
Set @ SQL = LEFT (@ SQL, len (@ SQL)-1) + 'from # student Group by stdname'
Print @ sqlexec (@ SQL)
-- ============================ ======================================
DROP table # student2
Create Table # student2 (stdname nvarchar (10), chemical int, mathematical int, physical int, Chinese INT)
Insert into # student2 values ('lily',) insert into # student2 values ('zhang san)

-- Column
Select * from # student2

-- Line
Select 'Li si' as stdname, stdname = 'Chemical', chemistry as result from # student2 where stdname = 'Li si'
Union all select 'lily' as stdname, stdname = 'mat', mathematics as result from # student2 where stdname = 'lily'
Union all select 'lily' as stdname, stdname = 'physical ', physical as result from # student2 where stdname = 'lily'
Union all select 'lily' as stdname, stdname = 'China', Chinese as result from # student2 where stdname = 'lily'
Union all select 'zhang san' as stdname, stdname = 'Chemical', chemistry as result from # student2 where stdname = 'zhang san'
Union all select 'zhang san' as stdname, stdname = 'mat', mathematics as result from # student2 where stdname = 'zhang san'
Union all select 'zhang san' as stdname, stdname = 'physical ', physical as result from # student2 where stdname = 'zhang san'
Union all select 'zhang san' as stdname, stdname = 'China', Chinese as result from # student2 where stdname = 'zhang san'

-- Refactoring
Select [name] into # tmpcloumnsfrom tempdb. DBO. syscolumnswhere id = object_id ('tempdb. DBO. # student2 ') and [name] <> 'stdname'
-- Select * from # tmpcloumns
Declare @ strsql nvarchar (800) Select @ strsql = ''select @ strsql = @ strsql + 'Union all' + char (10) + char (13) + 'select [stdname], ''' + [name] + ''' as [subject], ['+ [name] +'] '+ char (10) + char (13) + 'from [# student2] '+ char (10) + char (13) from # tmpcloumns
Select @ strsql = substring (@ strsql, 11, Len (@ strsql) + 'order by stdname, [subject] 'print @ strsqlexec (@ strsql)