/*
普通行列转换
(爱新觉罗.毓华 2007-11-18于海南三亚)假设有张学生成绩表(tb)如下:
Name Subject Result
张三 语文 74
张三 数学 83
张三 物理 93
李四 语文 74
李四 数学 84
李四 物理 94
*/-------------------------------------------------------------------------
/*
想变成
姓名 语文 数学 物理
---------- ----------- ----------- -----------
李四 74 84 94
张三 74 83 93
*/create table tb
(
Name varchar(10) ,
Subject varchar(10) ,
Result int
)insert into tb(Name , Subject , Result) values('张三' , '语文' , 74)
insert into tb(Name , Subject , Result) values('张三' , '数学' , 83)
insert into tb(Name , Subject , Result) values('张三' , '物理' , 93)
insert into tb(Name , Subject , Result) values('李四' , '语文' , 74)
insert into tb(Name , Subject , Result) values('李四' , '数学' , 84)
insert into tb(Name , Subject , Result) values('李四' , '物理' , 94)
go--静态SQL,指subject只有语文、数学、物理这三门课程。
select name 姓名,
max(case subject when '语文' then result else 0 end) 语文,
max(case subject when '数学' then result else 0 end) 数学,
max(case subject when '物理' then result else 0 end) 物理
from tb
group by name
/*
姓名 语文 数学 物理
---------- ----------- ----------- -----------
李四 74 84 94
张三 74 83 93
*/--动态SQL,指subject不止语文、数学、物理这三门课程。
declare @sql varchar(8000)
set @sql = 'select Name as ' + '姓名'
select @sql = @sql + ' , max(case Subject when ''' + Subject + ''' then Result else 0 end) [' + Subject + ']'
from (select distinct Subject from tb) as a
set @sql = @sql + ' from tb group by name'
exec(@sql)
/*
姓名 数学 物理 语文
---------- ----------- ----------- -----------
李四 84 94 74
张三 83 93 74
*/-------------------------------------------------------------------
/*加个平均分,总分
姓名 语文 数学 物理 平均分 总分
---------- ----------- ----------- ----------- -------------------- -----------
李四 74 84 94 84.00 252
张三 74 83 93 83.33 250
*/--静态SQL,指subject只有语文、数学、物理这三门课程。
select name 姓名,
max(case subject when '语文' then result else 0 end) 语文,
max(case subject when '数学' then result else 0 end) 数学,
max(case subject when '物理' then result else 0 end) 物理,
cast(avg(result*1.0) as decimal(18,2)) 平均分,
sum(result) 总分
from tb
group by name
/*
姓名 语文 数学 物理 平均分 总分
---------- ----------- ----------- ----------- -------------------- -----------
李四 74 84 94 84.00 252
张三 74 83 93 83.33 250
*/--动态SQL,指subject不止语文、数学、物理这三门课程。
declare @sql1 varchar(8000)
set @sql1 = 'select Name as ' + '姓名'
select @sql1 = @sql1 + ' , max(case Subject when ''' + Subject + ''' then Result else 0 end) [' + Subject + ']'
from (select distinct Subject from tb) as a
set @sql1 = @sql1 + ' , cast(avg(result*1.0) as decimal(18,2)) 平均分,sum(result) 总分 from tb group by name'
exec(@sql1)
/*
姓名 数学 物理 语文 平均分 总分
---------- ----------- ----------- ----------- -------------------- -----------
李四 84 94 74 84.00 252
张三 83 93 74 83.33 250
*/drop table tb ---------------------------------------------------------
---------------------------------------------------------
/*
如果上述两表互相换一下:即姓名 语文 数学 物理
张三 74 83 93
李四 74 84 94想变成
Name Subject Result
---------- ------- -----------
李四 语文 74
李四 数学 84
李四 物理 94
张三 语文 74
张三 数学 83
张三 物理 93
*/create table tb1
(
姓名 varchar(10) ,
语文 int ,
数学 int ,
物理 int
)insert into tb1(姓名 , 语文 , 数学 , 物理) values('张三',74,83,93)
insert into tb1(姓名 , 语文 , 数学 , 物理) values('李四',74,84,94)select * from
(
select 姓名 as Name , Subject = '语文' , Result = 语文 from tb1
union all
select 姓名 as Name , Subject = '数学' , Result = 数学 from tb1
union all
select 姓名 as Name , Subject = '物理' , Result = 物理 from tb1
) t
order by name , case Subject when '语文' then 1 when '数学' then 2 when '物理' then 3 when '总分' then 4 end--------------------------------------------------------------------
/*加个平均分,总分
Name Subject Result
---------- ------- --------------------
李四 语文 74.00
李四 数学 84.00
李四 物理 94.00
李四 平均分 84.00
李四 总分 252.00
张三 语文 74.00
张三 数学 83.00
张三 物理 93.00
张三 平均分 83.33
张三 总分 250.00
*/select * from
(
select 姓名 as Name , Subject = '语文' , Result = 语文 from tb1
union all
select 姓名 as Name , Subject = '数学' , Result = 数学 from tb1
union all
select 姓名 as Name , Subject = '物理' , Result = 物理 from tb1
union all
select 姓名 as Name , Subject = '平均分' , Result = cast((语文 + 数学 + 物理)*1.0/3 as decimal(18,2)) from tb1
union all
select 姓名 as Name , Subject = '总分' , Result = 语文 + 数学 + 物理 from tb1
) t
order by name , case Subject when '语文' then 1 when '数学' then 2 when '物理' then 3 when '平均分' then 4 when '总分' then 5 enddrop table tb1
普通行列转换
(爱新觉罗.毓华 2007-11-18于海南三亚)假设有张学生成绩表(tb)如下:
Name Subject Result
张三 语文 74
张三 数学 83
张三 物理 93
李四 语文 74
李四 数学 84
李四 物理 94
*/-------------------------------------------------------------------------
/*
想变成
姓名 语文 数学 物理
---------- ----------- ----------- -----------
李四 74 84 94
张三 74 83 93
*/create table tb
(
Name varchar(10) ,
Subject varchar(10) ,
Result int
)insert into tb(Name , Subject , Result) values('张三' , '语文' , 74)
insert into tb(Name , Subject , Result) values('张三' , '数学' , 83)
insert into tb(Name , Subject , Result) values('张三' , '物理' , 93)
insert into tb(Name , Subject , Result) values('李四' , '语文' , 74)
insert into tb(Name , Subject , Result) values('李四' , '数学' , 84)
insert into tb(Name , Subject , Result) values('李四' , '物理' , 94)
go--静态SQL,指subject只有语文、数学、物理这三门课程。
select name 姓名,
max(case subject when '语文' then result else 0 end) 语文,
max(case subject when '数学' then result else 0 end) 数学,
max(case subject when '物理' then result else 0 end) 物理
from tb
group by name
/*
姓名 语文 数学 物理
---------- ----------- ----------- -----------
李四 74 84 94
张三 74 83 93
*/--动态SQL,指subject不止语文、数学、物理这三门课程。
declare @sql varchar(8000)
set @sql = 'select Name as ' + '姓名'
select @sql = @sql + ' , max(case Subject when ''' + Subject + ''' then Result else 0 end) [' + Subject + ']'
from (select distinct Subject from tb) as a
set @sql = @sql + ' from tb group by name'
exec(@sql)
/*
姓名 数学 物理 语文
---------- ----------- ----------- -----------
李四 84 94 74
张三 83 93 74
*/-------------------------------------------------------------------
/*加个平均分,总分
姓名 语文 数学 物理 平均分 总分
---------- ----------- ----------- ----------- -------------------- -----------
李四 74 84 94 84.00 252
张三 74 83 93 83.33 250
*/--静态SQL,指subject只有语文、数学、物理这三门课程。
select name 姓名,
max(case subject when '语文' then result else 0 end) 语文,
max(case subject when '数学' then result else 0 end) 数学,
max(case subject when '物理' then result else 0 end) 物理,
cast(avg(result*1.0) as decimal(18,2)) 平均分,
sum(result) 总分
from tb
group by name
/*
姓名 语文 数学 物理 平均分 总分
---------- ----------- ----------- ----------- -------------------- -----------
李四 74 84 94 84.00 252
张三 74 83 93 83.33 250
*/--动态SQL,指subject不止语文、数学、物理这三门课程。
declare @sql1 varchar(8000)
set @sql1 = 'select Name as ' + '姓名'
select @sql1 = @sql1 + ' , max(case Subject when ''' + Subject + ''' then Result else 0 end) [' + Subject + ']'
from (select distinct Subject from tb) as a
set @sql1 = @sql1 + ' , cast(avg(result*1.0) as decimal(18,2)) 平均分,sum(result) 总分 from tb group by name'
exec(@sql1)
/*
姓名 数学 物理 语文 平均分 总分
---------- ----------- ----------- ----------- -------------------- -----------
李四 84 94 74 84.00 252
张三 83 93 74 83.33 250
*/drop table tb ---------------------------------------------------------
---------------------------------------------------------
/*
如果上述两表互相换一下:即姓名 语文 数学 物理
张三 74 83 93
李四 74 84 94想变成
Name Subject Result
---------- ------- -----------
李四 语文 74
李四 数学 84
李四 物理 94
张三 语文 74
张三 数学 83
张三 物理 93
*/create table tb1
(
姓名 varchar(10) ,
语文 int ,
数学 int ,
物理 int
)insert into tb1(姓名 , 语文 , 数学 , 物理) values('张三',74,83,93)
insert into tb1(姓名 , 语文 , 数学 , 物理) values('李四',74,84,94)select * from
(
select 姓名 as Name , Subject = '语文' , Result = 语文 from tb1
union all
select 姓名 as Name , Subject = '数学' , Result = 数学 from tb1
union all
select 姓名 as Name , Subject = '物理' , Result = 物理 from tb1
) t
order by name , case Subject when '语文' then 1 when '数学' then 2 when '物理' then 3 when '总分' then 4 end--------------------------------------------------------------------
/*加个平均分,总分
Name Subject Result
---------- ------- --------------------
李四 语文 74.00
李四 数学 84.00
李四 物理 94.00
李四 平均分 84.00
李四 总分 252.00
张三 语文 74.00
张三 数学 83.00
张三 物理 93.00
张三 平均分 83.33
张三 总分 250.00
*/select * from
(
select 姓名 as Name , Subject = '语文' , Result = 语文 from tb1
union all
select 姓名 as Name , Subject = '数学' , Result = 数学 from tb1
union all
select 姓名 as Name , Subject = '物理' , Result = 物理 from tb1
union all
select 姓名 as Name , Subject = '平均分' , Result = cast((语文 + 数学 + 物理)*1.0/3 as decimal(18,2)) from tb1
union all
select 姓名 as Name , Subject = '总分' , Result = 语文 + 数学 + 物理 from tb1
) t
order by name , case Subject when '语文' then 1 when '数学' then 2 when '物理' then 3 when '平均分' then 4 when '总分' then 5 enddrop table tb1
insert ta select '贝凤丽',163 ,'是'
insert ta select '贝凤丽', 164 ,'是'
insert ta select '贝凤丽', 165 ,'是'
insert ta select '贝凤丽', 166 ,'否'
insert ta select '贝凤丽', 167 ,'是'
insert ta select '贝凤丽', 168 ,'是'
insert ta select '蔡春燕', 163 ,'是'
insert ta select '蔡春燕', 164 ,'是'
insert ta select '蔡春燕', 165 ,'是'
insert ta select '蔡春燕', 166 ,'是'
insert ta select '蔡春燕', 167 ,'是'
insert ta select '蔡春燕', 168 ,'是'
insert ta select '蔡佩琦', 163 ,'是'
insert ta select '蔡佩琦', 164 ,'是'
insert ta select '蔡佩琦', 165 ,'是'
insert ta select '蔡佩琦', 166 ,'是'
insert ta select '蔡佩琦', 167 ,'是'
insert ta select '蔡佩琦', 168 ,'否'
insert ta select '夏国平', 163 ,'是'
insert ta select '夏国平', 164 ,'是'
insert ta select '夏国平', 165 ,'是'
insert ta select '夏国平', 166 ,'是'
insert ta select '夏国平', 167 ,'是'
insert ta select '夏国平', 168 ,'是'
insert ta select '夏国平', 169 ,'未答'
insert ta select '夏国平', 170,'未答' go
declare @s varchar(8000)
declare @I intselect @i = min(questionid)- 1 from ta
set @s = 'select CustomerName'
select @s =@s + ',['+ltrim(questionId - @i)+']=max(case when questionId = '+ltrim(questionId)+' then answer else '''' end)'
from (select distinct questionid from ta) a
exec( @s+ ' from ta group by CustomerName')
/*
CustomerName 1 2 3 4 5 6 7 8
------------ ------ ------ ------ ------ ------ ------ ------ ------
贝凤丽 是 是 是 否 是 是
蔡春燕 是 是 是 是 是 是
蔡佩琦 是 是 是 是 是 否
夏国平 是 是 是 是 是 是 未答 未答
*/drop table ta[/code]
SELECT distinct ca.answerId, c.CustomerId, c.CustomerName,c.customerCode, ca.questionId, ca.answer FROM Customer AS c INNER JOIN
customerAnswers AS ca ON c.customerCode = ca.customerCode INNER JOIN contractRole AS cr ON c.customerCode = cr.customerCode
sql语句 改写!!!谢谢 自己参考先
insert into tb values('贝凤丽', 163, '是')
insert into tb values('贝凤丽', 164, '是')
insert into tb values('贝凤丽', 165, '是')
insert into tb values('贝凤丽', 166, '否')
insert into tb values('贝凤丽', 167, '是')
insert into tb values('贝凤丽', 168, '是')
insert into tb values('蔡春燕', 163, '是')
insert into tb values('蔡春燕', 164, '是')
insert into tb values('蔡春燕', 165, '是')
insert into tb values('蔡春燕', 166, '是')
insert into tb values('蔡春燕', 167, '是')
insert into tb values('蔡春燕', 168, '是')
insert into tb values('蔡佩琦', 163, '是')
insert into tb values('蔡佩琦', 164, '是')
insert into tb values('蔡佩琦', 165, '是')
insert into tb values('蔡佩琦', 166, '是')
insert into tb values('蔡佩琦', 167, '是')
insert into tb values('蔡佩琦', 168, '否')
insert into tb values('夏国平', 163, '是')
insert into tb values('夏国平', 164, '是')
insert into tb values('夏国平', 165, '是')
insert into tb values('夏国平', 166, '是')
insert into tb values('夏国平', 167, '是')
insert into tb values('夏国平', 168, '是')
insert into tb values('夏国平', 169, '未答')
insert into tb values('夏国平', 170, '未答')
go
select customername ,
max(case questionId when 163 then answer else '' end) [163],
max(case questionId when 164 then answer else '' end) [164],
max(case questionId when 165 then answer else '' end) [165],
max(case questionId when 166 then answer else '' end) [166],
max(case questionId when 167 then answer else '' end) [167],
max(case questionId when 168 then answer else '' end) [168],
max(case questionId when 169 then answer else '' end) [169],
max(case questionId when 170 then answer else '' end) [170]
from tb group by customernamedrop table tb/*
customername 163 164 165 166 167 168 169 170
------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
贝凤丽 是 是 是 否 是 是
蔡春燕 是 是 是 是 是 是
蔡佩琦 是 是 是 是 是 否
夏国平 是 是 是 是 是 是 未答 未答(4 行受影响)
*/
into ta
FROM Customer AS c INNER JOIN
customerAnswers AS ca ON c.customerCode = ca.customerCode INNER JOIN contractRole AS cr ON c.customerCode = cr.customerCode
----下面的就不要变了
declare @s varchar(8000)
declare @I intselect @i = min(questionid)- 1 from ta
set @s = 'select CustomerName'
select @s =@s + ',['+ltrim(questionId - @i)+']=max(case when questionId = '+ltrim(questionId)+' then answer else '''' end)'
from (select distinct questionid from ta) a
exec( @s+ ' from ta group by CustomerName')---最后删除 ta
drop table ta
insert into tb values('贝凤丽', 163, '是')
insert into tb values('贝凤丽', 164, '是')
insert into tb values('贝凤丽', 165, '是')
insert into tb values('贝凤丽', 166, '否')
insert into tb values('贝凤丽', 167, '是')
insert into tb values('贝凤丽', 168, '是')
insert into tb values('蔡春燕', 163, '是')
insert into tb values('蔡春燕', 164, '是')
insert into tb values('蔡春燕', 165, '是')
insert into tb values('蔡春燕', 166, '是')
insert into tb values('蔡春燕', 167, '是')
insert into tb values('蔡春燕', 168, '是')
insert into tb values('蔡佩琦', 163, '是')
insert into tb values('蔡佩琦', 164, '是')
insert into tb values('蔡佩琦', 165, '是')
insert into tb values('蔡佩琦', 166, '是')
insert into tb values('蔡佩琦', 167, '是')
insert into tb values('蔡佩琦', 168, '否')
insert into tb values('夏国平', 163, '是')
insert into tb values('夏国平', 164, '是')
insert into tb values('夏国平', 165, '是')
insert into tb values('夏国平', 166, '是')
insert into tb values('夏国平', 167, '是')
insert into tb values('夏国平', 168, '是')
insert into tb values('夏国平', 169, '未答')
insert into tb values('夏国平', 170, '未答')
go
select customername ,
max(case px when 1 then answer else '' end) [1],
max(case px when 2 then answer else '' end) [2],
max(case px when 3 then answer else '' end) [3],
max(case px when 4 then answer else '' end) [4],
max(case px when 5 then answer else '' end) [5],
max(case px when 6 then answer else '' end) [6],
max(case px when 7 then answer else '' end) [7],
max(case px when 8 then answer else '' end) [8],
max(case px when 9 then answer else '' end) [9],
max(case px when 10 then answer else '' end) [10]
from
(
select px = (select count(1) from tb where customername = t.customername and questionId < t.questionId) + 1,* from tb t
) m
group by customername
drop table tb/*
customername 1 2 3 4 5 6 7 8 9 10
------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
贝凤丽 是 是 是 否 是 是
蔡春燕 是 是 是 是 是 是
蔡佩琦 是 是 是 是 是 否
夏国平 是 是 是 是 是 是 未答 未答 (4 行受影响)*//*
customername 1 2 3 4 5 6 7 8 9 10
------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
贝凤丽 是 是 是 否 是 是
蔡春燕 是 是 是 是 是 是
蔡佩琦 是 是 是 是 是 否
夏国平 是 是 是 是 是 是 未答 未答 (4 行受影响)
*/
CustomerName 10 18 1 19 16 13 2 14 8 20 17 15 12 9
CHOOSOONHENG 是 是 是 是 是 是
敖志端 是 是 是 是 是 是
白 是 是 是 是 是 是
贝凤丽 是 是 是 是 是 是
蔡春燕 是 是 是 是 是 是
蔡佩琦 是 是 是 否 是 是
蔡伟 是 是 是 是 是 是
曹慧 是 是 是 是 是 是
曹凌俊 是 是 是 是 是 是
常志远 是 是 是 是 是 是
陈惠明 是 是 否 是 是 是
陈键 是 是 是 是 是 是
陈金良 是 是 是 是 是 是
陈金珠 是 是 是 是 是 是
陈俊荣 是 是 是 是 是 是
陈美枝 是 是 是
陈绮云 是 是 是 是 是 是
陈泉 是 是 是 是 是 是
陈统义 否
陈艳 是 否 是 是 是 否
陈永生 否 是 是 否 否 是
陈蕴蔚 是 是 是 是 是 是
崔晓波 是 是 是 是 是 是
戴树人 是 是 是 是 是 是
单国强 未答 是 是 是 是 是 是
董洪娣 是 是 是 是 是 是
董萌 是 是 是 是 是 是
杜红梅 是 是 是 是 是 是
杜书卷 是 是 是 是 是 是
范华萍 是 是 是 是 是 是
范玉华 是 是 是 是 是 是
范志华 是 是 是 是 是 是
方巧芬 是 是 是 是 是 是
冯东苗 是 是 是 是 是 是
冯建东 是 是 是 是 是 是
冯琼丽 是 是 是 是 是 是
冯晓萍 是 是 是 是 是 是
高凤霞 是 是 是 是 是 是
高顺妹 是 是 是 是 是 是
葛立娟 是 是 是 是 是 是
葛学易 是 是 是 是 是 是
龚玲珍 是 是 是 是 是 是
龚顺兴 否 是 否 否 是 是
龚羽 是 是 是 是 是 是
顾惠贤 是 是 是 是 是 是
顾建新 是 是 是 是 是 是
顾振宇 否 是 是 否 否 是
官园园 是 是 是 是 是 是
管巧云 是 是 是 是 是 是
郭长斌 是 是 是 是 是 是
郭汉华 是 是 是 是 是 是
韩建元 是 是 是 是 是 是
韩淑英 是 是 是
郝刚 是 是 是 是 是 是
洪斌 是 是 是 是 是 是
洪美榆 是 是 是 是 是 是
侯林娣 是 是 是 是 是 是
胡宏宇 是 是 是 是 是 是
胡惠珍 是 是 是 是 是 是
胡敬亚 是 是 是 是 是 是
胡平 是 是 是 是 是 是
胡文毅 是 是 是 是 是 是
花怀彪 是 是 是 是 是 是
华贵兰 是 是 是 是 是 是
华萍 否 是 否 是 否 是
黄炳仁 是 是 是 是 是 是
黄国丽 是 是 是 是 是 是
黄惠民 是 是 是 是 是 是
黄健 是 是 是 是 是 是
黄佩 是 是 是 是 是 是
黄启成 未答 是 是 是 是 是 否
黄琴 是 是 是 是 是 是
黄雄顺 是 是 是 是 是 是
黄祖菁 是 是 是 是 是 是
计文龙 是 是 是 是 是 是
纪晓然 是 是 是
江 是 是 是 是 是 是
姜宏 是 是 是 是 是 是
--如果是SQL2005的話比較好解決create table tb(
CustomerName varchar(10),
questionId int,
answer varchar(10))insert tb select '贝凤丽', 163, '是'
union all select '贝凤丽', 164, '是'
union all select '贝凤丽', 165, '是'
union all select '贝凤丽', 166, '否'
union all select '贝凤丽', 167, '是'
union all select '贝凤丽', 168, '是'
union all select '蔡春燕', 163, '是'
union all select '蔡春燕', 164, '是'
union all select '蔡春燕', 165, '是'
union all select '蔡春燕', 166, '是'
union all select '蔡春燕', 167, '是'
union all select '蔡春燕', 168, '是'
union all select '蔡佩琦', 163, '是'
union all select '蔡佩琦', 164, '是'
union all select '蔡佩琦', 165, '是'
union all select '蔡佩琦', 166, '是'
union all select '蔡佩琦', 167, '是'
union all select '蔡佩琦', 168, '否'
union all select '夏国平', 163, '是'
union all select '夏国平', 164, '是'
union all select '夏国平', 165, '是'
union all select '夏国平', 166, '是'
union all select '夏国平', 167, '是'
union all select '夏国平', 168, '是'
union all select '夏国平', 169, '未答'
union all select '夏国平', 170, '未答'declare @s varchar(8000)
select @s = isnull(@s, '') + ', max(case questionId when ' + cast(questionId as varchar) +
' then answer else '''' end) as [' + cast(id as varchar) + ']'
from (select distinct questionId, dense_rank() over(order by questionId) as id from tb) tselect @s = 'select CustomerName,' + stuff(@s, 1, 1, '') +
' from tb group by CustomerName'exec(@s)drop table tb/*
CustomerName 1 2 3 4 5 6 7 8
------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
贝凤丽 是 是 是 否 是 是
蔡春燕 是 是 是 是 是 是
蔡佩琦 是 是 是 是 是 否
夏国平 是 是 是 是 是 是 未答 未答(4 行受影响)
*/