CREATE TABLE ##TABLE(TM4 VARCHAR(20),TM5 VARCHAR(60),TM6 VARCHAR(60),TM9 VARCHAR(4),TL4 VARCHAR(40),TM10 NUMERIC(17,6),TM14 VARCHAR(8),TM12 VARCHAR(255))
INSERT INTO ##TABLE
--TM4:物料编码，TM5:物料名称，TM6:物料规格，TM9:物料单位，TL4:供应商编码，TM10:单价，TM14:生效日期,TM12:备注
SELECT '05000100101','ABS','750SW','kg','011079',15.100000,'20120531','' UNION ALL
SELECT '05000100101','ABS','750SW','kg','015110',18.200000,'20110111','失效' UNION ALL
SELECT '05000100101','ABS','750SW','kg','015118',18.450000,'20110325','' UNION ALL
SELECT '05000100101','ABS','750SW','kg','015118',17.300000,'20110726','' UNION ALL
SELECT '05000100101','ABS','750SW','kg','015118',16.150000,'20111013','' UNION ALL
SELECT '05000100101','ABS','750SW','kg','015142',14.650000,'20111214','' UNION ALL
SELECT '05000100101','ABS','750SW','kg','015142',15.600000,'20120203','' UNION ALL
SELECT '05000100101','ABS','750SW','kg','015142',15.800000,'20120302','' UNION ALL
SELECT '05000100101','ABS','750SW','kg','015142',15.850000,'20120412',''

CREATE TABLE ##TABLE(TM4 VARCHAR(20),TM5 VARCHAR(60),TM6 VARCHAR(60),TM9 VARCHAR(4),TL4 VARCHAR(40),TM10 NUMERIC(17,6),TM14 VARCHAR(8),TM12 VARCHAR(255))
INSERT INTO ##TABLE
--TM4:物料编码，TM5:物料名称，TM6:物料规格，TM9:物料单位，TL4:供应商编码，TM10:单价，TM14:生效日期,TM12:备注
SELECT '05000100101','ABS','750SW','kg','011079',15.100000,'20120531','' UNION ALL
SELECT '05000100101','ABS','750SW','kg','015110',18.200000,'20110111','失效' UNION ALL
SELECT '05000100101','ABS','750SW','kg','015118',18.450000,'20110325','' UNION ALL
SELECT '05000100101','ABS','750SW','kg','015118',17.300000,'20110726','' UNION ALL
SELECT '05000100101','ABS','750SW','kg','015118',16.150000,'20111013','' UNION ALL
SELECT '05000100101','ABS','750SW','kg','015142',14.650000,'20111214','' UNION ALL
SELECT '05000100101','ABS','750SW','kg','015142',15.600000,'20120203','' UNION ALL
SELECT '05000100101','ABS','750SW','kg','015142',15.800000,'20120302','' UNION ALL
SELECT '05000100101','ABS','750SW','kg','015142',15.850000,'20120412',''

go

DECLARE @s NVARCHAR(4000),@i NVARCHAR(3)

Select TOP 1 @i=COUNT(*),@s='' from ##TABLE GROUP BY TM4,TM5,TM6,TM9,TL4 order by count(*) desc

WHILE @i>0
    SELECT @s=N',[TM10-'+@i+']=max(case when Row='+@i+N' then [TM10] end)'
            +N',[TM14-'+@i+']=max(case when Row='+@i+N' then [TM14] end)'
            +N',[TM12-'+@i+']=max(case when Row='+@i+N' then [TM12] end)'+@s,@i=@i-1


EXEC(N'SELECT TM4,TM5,TM6,TM9,TL4'+@s+N'
FROM (select *, row=row_number()over(partition by TM4,TM5,TM6,TM9,TL4 order by TM4,TM5,TM6,TM9,TL4)from ##TABLE) as a
GROUP BY TM4,TM5,TM6,TM9,TL4')
go
/*
TM4    TM5    TM6    TM9    TL4    TM10-1    TM14-1    TM12-1    TM10-2    TM14-2    TM12-2    TM10-3    TM14-3    TM12-3    TM10-4    TM14-4    TM12-4
05000100101    ABS    750SW    kg    011079    15.100000    20120531        NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL
05000100101    ABS    750SW    kg    015110    18.200000    20110111    失效    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL
05000100101    ABS    750SW    kg    015118    18.450000    20110325        17.300000    20110726        16.150000    20111013        NULL    NULL    NULL
05000100101    ABS    750SW    kg    015142    14.650000    20111214        15.600000    20120203        15.800000    20120302        15.850000    20120412    
*/

------解决方案--------------------

SQL code

if OBJECT_ID('T_Table','U') is not null drop table T_Table
go
CREATE TABLE T_Table
(
    [物料编码] VARCHAR(20),
    [物料名称] VARCHAR(60),
    [物料规格] VARCHAR(60),
    [物料单位] VARCHAR(4),
    [供应商编码] VARCHAR(40),
    [单价1] NUMERIC(17,6),
    [生效日期] VARCHAR(8),
    [单价2] VARCHAR(255)
)

INSER