SQL 行转列

数据表1

CREATE TABLE [YS_PlanStep] (
[ID] [int]  IDENTITY (1, 1)  NOT NULL,
[StepName] [varchar]  (200) NULL,
[DelayDays] [int]  NULL)
ALTER TABLE [YS_PlanStep] WITH NOCHECK ADD  CONSTRAINT [PK_YS_PlanStep] PRIMARY KEY  NONCLUSTERED ( [ID],[StepName] )
SET IDENTITY_INSERT [YS_PlanStep] ON
INSERT [YS_PlanStep] ([ID],[UserGroupID],[StepName],[DelayDays]) VALUES ( 1,N'物料核算',-52)
INSERT [YS_PlanStep] ([ID],[UserGroupID],[StepName],[DelayDays]) VALUES ( 2,N'工地返尺',-21)
INSERT [YS_PlanStep] ([ID],[UserGroupID],[StepName],[DelayDays]) VALUES ( 3,N'采购完成',-21)
INSERT [YS_PlanStep] ([ID],[UserGroupID],[StepName],[DelayDays]) VALUES ( 4,N'生产排产',-7)
INSERT [YS_PlanStep] ([ID],[UserGroupID],[StepName],[DelayDays]) VALUES ( 5,,N'物流发货',0)
SET IDENTITY_INSERT [YS_PlanStep] OFF

数据表2

CREATE TABLE [YS_Project_PlanStepInfo] (
[ID] [int]  IDENTITY (1, 1)  NOT NULL,
[ProjectID] [int]  NULL,
[PlanStepID] [int]  NULL)
ALTER TABLE [YS_Project_PlanStepInfo] WITH NOCHECK ADD  CONSTRAINT [PK_YS_Project_PlanStepInfo] PRIMARY KEY  NONCLUSTERED ( [ID],[ProjectID] )
SET IDENTITY_INSERT [YS_Project_PlanStepInfo] ON
INSERT [YS_Project_PlanStepInfo] ([ID],[ProjectID],[YSID],[PlanStepID],[IsComplete]) VALUES ( 1,81,1)
INSERT [YS_Project_PlanStepInfo] ([ID],[ProjectID],[YSID],[PlanStepID],[IsComplete]) VALUES ( 2,81,2)
INSERT [YS_Project_PlanStepInfo] ([ID],[ProjectID],[YSID],[PlanStepID],[IsComplete]) VALUES ( 3,81,3)
INSERT [YS_Project_PlanStepInfo] ([ID],[ProjectID],[YSID],[PlanStepID],[IsComplete]) VALUES ( 4,81,4)
INSERT [YS_Project_PlanStepInfo] ([ID],[ProjectID],[YSID],[PlanStepID],[IsComplete]) VALUES ( 5,81,5)
SET IDENTITY_INSERT [YS_Project_PlanStepInfo] OFF

查询语句-SQL2000

declare @sql varchar(8000)
select @sql=isnull(@sql+',','')+' sum(case StepName when '''+StepName+''' then p.DelayDays else 0 end) ['+StepName+']'
from(select distinct StepName from  YS_PlanStep )as a 
set @sql='select  ProjectID,YSID,'+@sql+' from YS_Project_PlanStepInfo as i inner join YS_PlanStep as p on p.ID=i.PlanStepID group by ProjectID,YSID'
print @sql
exec(@sql)

查询结果