SELECT * FROM dbo.S_MRP_Part
SELECT * FROM dbo.S_MRP_DayCounts
ID Part CreateDate
----------- -------------------------------------------------- -----------------------
1 00-00129 2011-07-29 00:00:00.000(1 行受影响)ID HID MRPDate Counts
----------- ----------- -------------------------------------------------- ----------------------
1 1 Total 99
2 1 06 5 2011 12:00AM 0
3 1 06 6 2011 12:00AM 22
4 1 2011-08-01 33(3 行受影响)
希望能够根据 MRPDate进行列转行:
HID Part Total 06 5 2011 12:00A 06 6 2011 12:00AM 2011-08-01
----------- ----------- ------ -------------------- -------------------- ----------
1 00-00129 99 0 22 33
注:MRPDate 是不固定的(会有特殊情况如Total),所以不能像 语文 数学 那样。
SELECT * FROM dbo.S_MRP_DayCounts
ID Part CreateDate
----------- -------------------------------------------------- -----------------------
1 00-00129 2011-07-29 00:00:00.000(1 行受影响)ID HID MRPDate Counts
----------- ----------- -------------------------------------------------- ----------------------
1 1 Total 99
2 1 06 5 2011 12:00AM 0
3 1 06 6 2011 12:00AM 22
4 1 2011-08-01 33(3 行受影响)
希望能够根据 MRPDate进行列转行:
HID Part Total 06 5 2011 12:00A 06 6 2011 12:00AM 2011-08-01
----------- ----------- ------ -------------------- -------------------- ----------
1 00-00129 99 0 22 33
注:MRPDate 是不固定的(会有特殊情况如Total),所以不能像 语文 数学 那样。
set @sql = 'select a.hid,b.Part '
select @sql = @sql + ' , max(case when MRPDate=''' + MRPDate + ''' then Counts else null end) [' + MRPDate + ']'
from S_MRP_DayCounts
set @sql = @sql + ' from S_MRP_DayCounts a,S_MRP_Part b where a.hid=b.id group by a.hid,b.Part'
exec(@sql)
----------- ----------- -------------------------------------------------- ----------------------
1 1 Total 99
2 1 06 5 2011 12:00AM 0
3 1 06 6 2011 12:00AM 22
4 1 2011-08-01 33楼主你这是几列啊!
declare @sql varchar(8000)这个8000什么意思,
如果我有 几百万 条数据会不会有问题?