往表插入数据，行转列，高手帮忙看看

Question

INSERT INTO HospitalStat_tb
(
ID,
ProvinceId,
CityId,
CountyId,
HospitalId,
[1],
[2],
[3],
[4],
[5],
[6],
[7],
[8],
[9],
[10],
[11],
[12],
[13],
[14],
[15],
[16],
[17],
[18],
[19],
[20],
[21],
[22],
[23],
[24],
[25]
)  

With Test(Scores,MeasureTarget3Id,Id)as (
  SELECT     CONVERT(float,sum(Scores))/count(DISTINCT(Answer_tb.UserId)) AS Scores, dbo.MeasureTarget3_tb.MeasureTarget3Id, Examination_tb.HospitalId
FROM         dbo.Answer_tb INNER JOIN
                      dbo.Examination_tb ON dbo.Answer_tb.ExaminationId = dbo.Examination_tb.ExaminationId
                      JOIN Hospital_tb ON Hospital_tb.HospitalId = dbo.Examination_tb.HospitalId  INNER JOIN
                      dbo.ExaminationChange_tb ON dbo.Answer_tb.ExaminationChangeId = dbo.ExaminationChange_tb.ExaminationChangeId LEFT OUTER JOIN
                      dbo.MeasureTarget3_tb ON dbo.Examination_tb.MeasureTarget3Id = dbo.MeasureTarget3_tb.MeasureTarget3Id
WHERE  Type=1 and CountyId=1 AND Scores>0
GROUP BY  dbo.MeasureTarget3_tb.MeasureTarget3Id, Examination_tb.HospitalId
 ) 
 SELECT * FROM Test PIVOT (sum(scores) FOR MeasureTarget3Id IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25])) AS T
   With Test(Scores,MeasureTarget3Id,Id)as (
  SELECT     CONVERT(float,sum(Scores))/count(DISTINCT(Answer_tb.UserId)) AS Scores, dbo.MeasureTarget3_tb.MeasureTarget3Id, Examination_tb.HospitalId
FROM         dbo.Answer_tb INNER JOIN
                      dbo.Examination_tb ON dbo.Answer_tb.ExaminationId = dbo.Examination_tb.ExaminationId
                      JOIN Hospital_tb ON Hospital_tb.HospitalId = dbo.Examination_tb.HospitalId  INNER JOIN
                      dbo.ExaminationChange_tb ON dbo.Answer_tb.ExaminationChangeId = dbo.ExaminationChange_tb.ExaminationChangeId LEFT OUTER JOIN
                      dbo.MeasureTarget3_tb ON dbo.Examination_tb.MeasureTarget3Id = dbo.MeasureTarget3_tb.MeasureTarget3Id
WHERE  Type=1 and CountyId=1 AND Scores>0
GROUP BY  dbo.MeasureTarget3_tb.MeasureTarget3Id, Examination_tb.HospitalId
 ) 
 SELECT * FROM Test PIVOT (sum(scores) FOR MeasureTarget3Id IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25])) AS T
这个sql是正确的，能查询出来数据错误提示如下：请高手帮忙看看，谢谢了

Answer 1

With Test(Scores,MeasureTarget3Id,Id)as (        
         SELECT     CONVERT(float,sum(Scores))/count(DISTINCT(Answer_tb.UserId)) AS Scores, dbo.MeasureTarget3_tb.MeasureTarget3Id, Examination_tb.HospitalId
FROM         dbo.Answer_tb INNER JOIN
                      dbo.Examination_tb ON dbo.Answer_tb.ExaminationId = dbo.Examination_tb.ExaminationId
                      JOIN Hospital_tb ON Hospital_tb.HospitalId = dbo.Examination_tb.HospitalId  INNER JOIN
                      dbo.ExaminationChange_tb ON dbo.Answer_tb.ExaminationChangeId = dbo.ExaminationChange_tb.ExaminationChangeId LEFT OUTER JOIN
                      dbo.MeasureTarget3_tb ON dbo.Examination_tb.MeasureTarget3Id = dbo.MeasureTarget3_tb.MeasureTarget3Id
                WHERE  Type=1 and CountyId=1 AND Scores>0
                GROUP BY  dbo.MeasureTarget3_tb.MeasureTarget3Id, Examination_tb.HospitalId
         ) 
         SELECT * into HospitalStat_tb FROM Test PIVOT (sum(scores) FOR MeasureTarget3Id IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25])) AS T这样吧

Answer 2

INSERT INTO HospitalStat_tb(
            ID,
       ProvinceId,
            CityId,
            CountyId,
            HospitalId,
            [1],
            [2],
            [3],
            [4],
            [5],
            [6],
            [7],
            [8],
            [9],
            [10],
            [11],
            [12],
            [13],
            [14],
            [15],
            [16],
            [17],
            [18],
            [19],
            [20],
            [21],
            [22],
            [23],
            [24],
            [25]
        )  
        
   SELECT * FROM (
SELECT     CONVERT(float,sum(Scores))/count(DISTINCT(Answer_tb.UserId))
AS Scores, dbo.MeasureTarget3_tb.MeasureTarget3Id, Examination_tb.HospitalId
FROM   dbo.Answer_tb INNER JOIN
  dbo.Examination_tb ON dbo.Answer_tb.ExaminationId = dbo.Examination_tb.ExaminationId
  JOIN Hospital_tb ON Hospital_tb.HospitalId = dbo.Examination_tb.HospitalId  INNER JOIN
  dbo.ExaminationChange_tb ON dbo.Answer_tb.ExaminationChangeId = dbo.ExaminationChange_tb.ExaminationChangeId LEFT OUTER JOIN
  dbo.MeasureTarget3_tb ON dbo.Examination_tb.MeasureTarget3Id = dbo.MeasureTarget3_tb.MeasureTarget3Id
WHERE  Type=1 and CountyId=1 AND Scores>0
GROUP BY  dbo.MeasureTarget3_tb.MeasureTarget3Id, Examination_tb.HospitalId
 )  as Test PIVOT (sum(scores) FOR MeasureTarget3Id IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25])) AS T
       SQL code
With Test(Scores,MeasureTarget3Id,Id)as (        
         SELECT     CONVERT(float,sum(Scores))/count(DISTINCT(Answer_tb.UserId)) AS Scores, dbo.MeasureTarget3_tb.MeasureTarget3Id, Examination_tb.HospitalId
FROM         dbo.Answer_tb INNER JOIN
                      dbo.Examination_tb ON dbo.Answer_tb.ExaminationId = dbo.Examination_tb.ExaminationId
                      JOIN Hospital_tb ON Hospital_tb.HospitalId = dbo.Examination_tb.HospitalId  INNER JOIN
                      dbo.ExaminationChange_tb ON dbo.Answer_tb.ExaminationChangeId = dbo.ExaminationChange_tb.ExaminationChangeId LEFT OUTER JOIN
                      dbo.MeasureTarget3_tb ON dbo.Examination_tb.MeasureTarget3Id = dbo.MeasureTarget3_tb.MeasureTarget3Id
                WHERE  Type=1 and CountyId=1 AND Scores>0
                GROUP BY  dbo.MeasureTarget3_tb.MeasureTarget3Id, Examination_tb.HospitalId
         ) 
         SELECT * FROM Test PIVOT (sum(scores) FOR MeasureTarget3Id IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25])) AS T

Answer 3

把With去掉 直接用PIVOT 

Answer 4

额，当时没注意看，以为你直接拷的我的sql，不好意思了，明天试下，谢谢了