当前位置: 代码迷 >> Sql Server >> 行转列 的查询有关问题
  详细解决方案

行转列 的查询有关问题

热度:32   发布时间:2016-04-27 15:26:31.0
行转列 的查询问题?
如题

create PROCEDURE [dbo].[sp_EduceAnswer]
AS
declare @ta table(researchId int ,questionId int,answer varchar(50),CustomerName varchar(100),customerCode varchar(50))
insert into @ta(researchId,questionId,answer,CustomerName,customerCode)
SELECT ca.researchId, ca.questionId,ca.answer, c.CustomerName,c.customerCode FROM Customer AS c 
inner JOIN (select distinct researchId,questionId,answer,customerCode from customerAnswers) ca ON c.customerCode = ca.customerCode 
order by questionId asc
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],
  max(case px when 11 then answer else '' end) [11]
from
(
  select px = (select count(1) from @ta where CustomerName = t.CustomerName and questionId < t.questionId) + 1,* from @ta t
) m
group by CustomerName

我现在要按 researchId,CustomerName,customerCode 查询!谢谢高手指点

------解决方案--------------------
SQL code
where researchId = t.researchId and CustomerName = t.CustomerName and customerCode = t.customerCode and questionId < t.questionId
  相关解决方案