求个sql语句，谢谢大家了！
inv_code price amount amount_money date
111000018.11119.4076.240000 2012-02-23 11:37:36.510
111000018.11110.110.890000 2012-02-23 11:28:23.510
1110000110.00003.0030.000000 2012-02-23 11:13:48.557
1110000110.00002.0020.000000 2012-02-23 11:06:23.103
1110000110.000013.00130.0000002012-02-23 10:59:02.227
1110000110.000010.00100.0000002012-02-22 15:43:08.010
1110000220.0000100.002000.0000002012-02-07 15:26:09.577
1110000210.0000100.001000.0000002012-02-07 15:25:28.593
1210000110.000020.00200.0000002012-02-24 14:11:09.603
通过sql写成下面的格式
只要该材料编码的前3行
inv_code content1 content2 content3
11100001 8.1111,9.40,76.240000 8.1111,0.11,0.890000 10.0000,3.00,30.000000
11100002 20.0000，100.00，2000.000000 10.0000，100.00，1000.000000 NULL
12100001 10.0000,20.00,200.000000 NULL NULL
[解决办法]
declare @t table ( inv_code int,price numeric(6,4),amount numeric(5,2), amount_money numeric(10,6),date datetime)insert into @tselect 11100001,8.1111,9.40,76.240000,'2012-02-23 11:37:36.510' union allselect 11100001,8.1111,0.11,0.890000,'2012-02-23 11:28:23.510' union allselect 11100001,10.0000,3.00,30.000000,'2012-02-23 11:13:48.557' union allselect 11100001,10.0000,2.00,20.000000,'2012-02-23 11:06:23.103' union allselect 11100001,10.0000,13.00,130.000000,'2012-02-23 10:59:02.227' union allselect 11100001,10.0000,10.00,100.000000,'2012-02-22 15:43:08.010' union allselect 11100002,20.0000,100.00,2000.000000,'2012-02-07 15:26:09.577' union allselect 11100002,10.0000,100.00,1000.000000,'2012-02-07 15:25:28.593' union allselect 12100001,10.0000,20.00,200.000000,'2012-02-24 14:11:09.603';with maco as(select row_number() over (partition by inv_code order by date desc) as rid,* from @t),m as (select * from maco where rid<4)select inv_code, ltrim(max(case when rid=1 then price else null end))+','+ ltrim(max(case when rid=1 then amount else null end))+','+ ltrim(max(case when rid=1 then amount_money else null end)) as content1 , ltrim(max(case when rid=2 then price else null end))+','+ ltrim(max(case when rid=2 then amount else null end))+','+ ltrim(max(case when rid=2 then amount_money else 0 end)) as content2 , ltrim(max(case when rid=3 then price else null end))+','+ ltrim(max(case when rid=3 then amount else null end))+','+ ltrim(max(case when rid=3 then amount_money else null end)) as content3 from m group by inv_code/*inv_code content1 content2 content3----------- ------------------------------- ----------------------------- -------------------------11100001 8.1111,9.40,76.240000 8.1111,0.11,0.890000 10.0000,3.00,30.00000011100002 20.0000,100.00,2000.000000 10.0000,100.00,1000.000000 NULL12100001 10.0000,20.00,200.000000 NULL NULL*/
[解决办法]
--inv_code price amount amount_money date--11100001 8.1111 9.40 76.240000 2012-02-23 11:37:36.510--11100001 8.1111 0.11 0.890000 2012-02-23 11:28:23.510--11100001 10.0000 3.00 30.000000 2012-02-23 11:13:48.557-- 11100001 10.0000 2.00 20.000000 2012-02-23 11:06:23.103-- 11100001 10.0000 13.00 130.000000 2012-02-23 10:59:02.227-- 11100001 10.0000 10.00 100.000000 2012-02-22 15:43:08.010--11100002 20.0000 100.00 2000.000000 2012-02-07 15:26:09.577--11100002 10.0000 100.00 1000.000000 2012-02-07 15:25:28.593--12100001 10.0000 20.00 200.000000 2012-02-24 14:11:09.603if OBJECT_ID('tb') is not nulldrop table tbgocreate table tb(inv_code varchar(20), price dec(10,4), amount dec (18,2),amount_money dec(16,6) ,date varchar(50))insert into tb values('11100001' ,8.1111, 9.40, 76.240000 ,'2012-02-23 11:37:36.510'),('11100001', 8.1111, 0.11, 0.890000, '2012-02-23 11:28:23.510'),('11100001' ,10.0000, 3.00, 30.000000, '2012-02-23 11:13:48.557'),('11100001' ,10.0000, 2.00, 20.000000 ,'2012-02-23 11:06:23.103'),('11100001' ,10.0000, 13.00, 130.000000 ,'2012-02-23 10:59:02.227'),('11100001', 10.0000, 10.00 ,100.000000 ,'2012-02-22 15:43:08.010'),('11100002', 20.0000, 100.00 ,2000.000000 ,'2012-02-07 15:26:09.577'),('11100002', 10.0000, 100.00, 1000.000000 ,'2012-02-07 15:25:28.593'),('12100001', 10.0000, 20.00 ,200.000000 ,'2012-02-24 14:11:09.603')--通过sql写成下面的格式--只要该材料编码的前3行--inv_code content1 content2 content3--11100001 8.1111,9.40,76.240000 8.1111,0.11,0.890000 10.0000,3.00,30.000000--11100002 20.0000，100.00，2000.000000 10.0000，100.00，1000.000000 NULL--12100001 10.0000,20.00,200.000000 NULL NULL;with ct as (select *,rn=ROW_NUMBER()over(partition by inv_code order by getdate()) from tb)select inv_code,max (case rn when 1 then cast (price as varchar(20))+','+cast (amount as varchar(20)) +','+cast (amount_money as varchar(20)) else null end) as content1,max (case rn when 2 then cast (price as varchar(20))+','+cast (amount as varchar(20)) +','+cast (amount_money as varchar(20)) else null end) as content2,max ( case rn when 3 then cast (price as varchar(20))+','+cast (amount as varchar(20)) +','+cast (amount_money as varchar(20)) else null end) as content2 from ct t group by inv_codeinv_code content1 content2 content2-------------------- -------------------------- -------------------------- --------------------------11100001 8.1111,9.40,76.240000 8.1111,0.11,0.890000 10.0000,3.00,30.00000011100002 20.0000,100.00,2000.000000 10.0000,100.00,1000.000000 NULL12100001 10.0000,20.00,200.000000 NULL NULL警告: 聚合或其他 SET 操作消除了 Null 值。(3 行受影响)