/*

普通行列转换

(爱新觉罗.毓华 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 end



drop table tb1

[code=SQL]create table ta(CustomerName varchar(6),questionId int,answer varchar(6))

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  int



select @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

create table tb (CustomerName varchar(10) , questionId int, answer varchar(10))

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 customername



drop table tb



/*

customername 163        164        165        166        167        168        169        170

------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------

贝凤丽          是          是          是          否          是          是                     

蔡春燕          是          是          是          是          是          是                     

蔡佩琦          是          是          是          是          是          否                     

夏国平          是          是          是          是          是          是          未答         未答



(4 行受影响)

*/

最简单的办法　就是把你的select 加一个into进另一个中间表







SELECT       distinct       ca.answerId,       c.CustomerId,       c.CustomerName,c.customerCode,       ca.questionId,       ca.answer      

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  int



select @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

create table tb (CustomerName varchar(10) , questionId int, answer varchar(10))

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 行受影响)

*/