一维转二维类型错误
以下是表格内容：
IDStaffIDStaffweekworkday worktimeDepartmentJude
1200003張三52013-8-197:30:00 車間 1
2200003張三52013-8-1912:05:00車間 2
3200003張三52013-8-1913:44:00車間 3
4200003張三52013-8-1917:33:00車間 4
5200003張三52013-8-1918:45:00車間 5
6200003張三52013-8-1920:20:00車間 6
7200003張三52013-8-1920:21:00車間 0
8200004李四52013-8-197:24:00 車間 1
9200004李四52013-8-1912:15:00車間 2
10200004李四52013-8-1913:54:00車間 11
11200004李四52013-8-1917:43:00車間 4
12200004李四52013-8-1918:35:00車間 5
13200004李四52013-8-1920:10:00車間 6
14200004李四52013-8-1920:20:00車間 0
表格设计：
ID int4
StaffID varchar12
Staff varchar20
week int4
workday datetime8
worktime datetime8
Department varchar20
Jude varchar2
一维转二维代码：
declare @sql varchar(8000)
set @sql='select StaffID,Staff,Convert(Varchar(10),workday,120),Convert(Varchar(10),worktime,108)'
select @sql=@sql+',['+dd+']=max(case Jude when '''+dd+''' then Convert(Varchar(10),worktime,108) else 0 end)'
from (select distinct dd=Jude from Attendance) ss
set @sql=@sql+' from Attendance group by StaffID,Staff,Convert(Varchar(10),workday,120),Convert(Varchar(10),worktime,108)'
exec(@sql)
Syntax error converting the varchar value '07:30:00' to a column of data type int.
"select
StaffID,Staff,Convert(Varchar(10),workday,120),
Convert(Varchar(10),worktime,108),[0]=max(case Jude when '0' then Convert(Varchar(10),worktime,108) else 0 end),
[1]=max(case Jude when '1' then Convert(Varchar(10),worktime,108) else 0 end),
[11]=max(case Jude when '11' then Convert(Varchar(10),worktime,108) else 0 end),
[2]=max(case Jude when '2' then Convert(Varchar(10),worktime,108) else 0 end),
[3]=max(case Jude when '3' then Convert(Varchar(10),worktime,108) else 0 end),
[4]=max(case Jude when '4' then Convert(Varchar(10),worktime,108) else 0 end),
[5]=max(case Jude when '5' then Convert(Varchar(10),worktime,108) else 0 end),
[6]=max(case Jude when '6' then Convert(Varchar(10),worktime,108) else 0 end)
from
Attendance
group by
StaffID,Staff,Convert(Varchar(10),workday,120),Convert(Varchar(10),worktime,108)
"
if OBJECT_ID('tempdb..#temp', 'u') is not null drop table #temp;
go
create table #temp( [ID] INT, [StaffID] varchar(12), [Staff] varchar(20), [week] INT, [workday] DATETIME, [worktime] DATETIME, [Department] varchar(20), [Jude] varchar(2));
insert #temp
select '1','200003','張三','5','2013-8-19','7:30:00','車間','1' union all
select '2','200003','張三','5','2013-8-19','12:05:00','車間','2' union all
select '3','200003','張三','5','2013-8-19','13:44:00','車間','3' union all
select '4','200003','張三','5','2013-8-19','17:33:00','車間','4' union all
select '5','200003','張三','5','2013-8-19','18:45:00','車間','5' union all
select '6','200003','張三','5','2013-8-19','20:20:00','車間','6' union all
select '7','200003','張三','5','2013-8-19','20:21:00','車間','0' union all
select '8','200004','李四','5','2013-8-19','7:24:00','車間','1' union all
select '9','200004','李四','5','2013-8-19','12:15:00','車間','2' union all
select '10','200004','李四','5','2013-8-19','13:54:00','車間','11' union all
select '11','200004','李四','5','2013-8-19','17:43:00','車間','4' union all
select '12','200004','李四','5','2013-8-19','18:35:00','車間','5' union all
select '13','200004','李四','5','2013-8-19','20:10:00','車間','6' union all
select '14','200004','李四','5','2013-8-19','20:20:00','車間','0'
--SQL:
--#1.在case语句中的多个when和else中的数据类型,系统会默认转换为优先级最高的那个数据类型.但字符和数字类型运算时,默认转换为数字类型.
--#2.楼主的语句中多了个时间列,结果应该不是楼主想要的。正确的结果参考如下：
declare @sql varchar(8000)
set @sql='select StaffID,Staff,[DATE]=Convert(Varchar(10),workday,120)'
select @sql=@sql+',['+dd+']=max(case Jude when '''+dd+''' then Convert(Varchar(10),worktime,108) else '''' end)'
from (select distinct dd=Jude from #temp) ss
set @sql=@sql+' from #temp group by StaffID,Staff,Convert(Varchar(10),workday,120)'
--PRINT @sql
exec(@sql)
/*
StaffIDStaffDATE011123456
200003張三2013-08-1920:21:0007:30:0012:05:0013:44:0017:33:0018:45:0020:20:00
200004李四2013-08-1920:20:0007:24:0013:54:0012:15:0017:43:0018:35:0020:10:00
*/