超难的行转列
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 ALLSELECT '05000100101','ABS','750SW','kg','015110',18.200000,'20110111','失效' UNION ALLSELECT '05000100101','ABS','750SW','kg','015118',18.450000,'20110325','' UNION ALLSELECT '05000100101','ABS','750SW','kg','015118',17.300000,'20110726','' UNION ALLSELECT '05000100101','ABS','750SW','kg','015118',16.150000,'20111013','' UNION ALLSELECT '05000100101','ABS','750SW','kg','015142',14.650000,'20111214','' UNION ALLSELECT '05000100101','ABS','750SW','kg','015142',15.600000,'20120203','' UNION ALLSELECT '05000100101','ABS','750SW','kg','015142',15.800000,'20120302','' UNION ALLSELECT '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 ALLSELECT '05000100101','ABS','750SW','kg','015110',18.200000,'20110111','失效' UNION ALLSELECT '05000100101','ABS','750SW','kg','015118',18.450000,'20110325','' UNION ALLSELECT '05000100101','ABS','750SW','kg','015118',17.300000,'20110726','' UNION ALLSELECT '05000100101','ABS','750SW','kg','015118',16.150000,'20111013','' UNION ALLSELECT '05000100101','ABS','750SW','kg','015142',14.650000,'20111214','' UNION ALLSELECT '05000100101','ABS','750SW','kg','015142',15.600000,'20120203','' UNION ALLSELECT '05000100101','ABS','750SW','kg','015142',15.800000,'20120302','' UNION ALLSELECT '05000100101','ABS','750SW','kg','015142',15.850000,'20120412',''goDECLARE @s NVARCHAR(4000),@i NVARCHAR(3)Select TOP 1 @i=COUNT(*),@s='' from ##TABLE GROUP BY TM4,TM5,TM6,TM9,TL4 order by count(*) descWHILE @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-1EXEC(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 aGROUP 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-405000100101 ABS 750SW kg 011079 15.100000 20120531 NULL NULL NULL NULL NULL NULL NULL NULL NULL05000100101 ABS 750SW kg 015110 18.200000 20110111 失效 NULL NULL NULL NULL NULL NULL NULL NULL NULL05000100101 ABS 750SW kg 015118 18.450000 20110325 17.300000 20110726 16.150000 20111013 NULL NULL NULL05000100101 ABS 750SW kg 015142 14.650000 20111214 15.600000 20120203 15.800000 20120302 15.850000 20120412 */
[解决办法]
if OBJECT_ID('T_Table','U') is not null drop table T_TablegoCREATE TABLE T_Table( [物料编码] VARCHAR(20), [物料名称] VARCHAR(60), [物料规格] VARCHAR(60), [物料单位] VARCHAR(4), [供应商编码] VARCHAR(40), [单价1] NUMERIC(17,6), [生效日期] VARCHAR(8), [单价2] VARCHAR(255))INSERT INTO T_Table--TM4:物料编码，TM5:物料名称，TM6:物料规格，TM9:物料单位，TL4:供应商编码，TM10:单价，TM14:生效日期,TM12:备注SELECT '05000100101','ABS','750SW','kg','011079',15.100000,'20120531','' UNION ALLSELECT '05000100101','ABS','750SW','kg','015110',18.200000,'20110111','失效' UNION ALLSELECT '05000100101','ABS','750SW','kg','015118',18.450000,'20110325','' UNION ALLSELECT '05000100101','ABS','750SW','kg','015118',17.300000,'20110726','' UNION ALLSELECT '05000100101','ABS','750SW','kg','015118',16.150000,'20111013','' UNION ALLSELECT '05000100101','ABS','750SW','kg','015142',14.650000,'20111214','' UNION ALLSELECT '05000100101','ABS','750SW','kg','015142',15.600000,'20120203','' UNION ALLSELECT '05000100101','ABS','750SW','kg','015142',15.800000,'20120302','' UNION ALLSELECT '05000100101','ABS','750SW','kg','015142',15.850000,'20120412',''go--定义变量存动态语句declare @sql varchar(max)--使用派生表with tb1 as( select *, --增加一列排序，这样直到每个供应商有一个单价 ROW_NUMBER() over (partition by [供应商编码] order by getdate()) as V_rank from T_Table)select --得到动态语句 @sql= ISNULL(@sql,'')+ ',max(case when V_rank ='+ltrim(v_rank)+' then 单价1 end) as 单价'+ltrim(v_rank)+char(10)+ ',max(case when V_rank ='+ltrim(v_rank)+' then [生效日期] end) as 生效日期'+ltrim(v_rank)+CHAR(10)from ( select distinct v_rank from tb1)a--查询信息set @sql=' ;with tb2 as ( select *, ROW_NUMBER() over (partition by [供应商编码] order by getdate()) as V_rank from T_Table ) select [物料编码], [物料名称], [物料规格], [物料单位], [供应商编码]'+ @sql+' from tb2 group by [物料编码],[物料名称],[物料规格],[物料单位],[供应商编码]'exec(@sql)/*(9 row(s) affected)物料编码 物料名称 物料规格 物料单位 供应商编码 单价1 生效日期1 单价2 生效日期2 单价3 生效日期3 单价4 生效日期4-------------------- ------------------------ ------------------------ ---- ---------------------------------------- --------------------------------------- -------- --------------------------------------- -------- --------------------------------------- -------- --------------------------------------- --------05000100101 ABS 750SW kg 011079 15.100000 20120531 NULL NULL NULL NULL NULL NULL05000100101 ABS 750SW kg 015110 18.200000 20110111 NULL NULL NULL NULL NULL NULL05000100101 ABS 750SW kg 015118 18.450000 20110325 17.300000 20110726 16.150000 20111013 NULL NULL05000100101 ABS 750SW kg 015142 14.650000 20111214 15.600000 20120203 15.800000 20120302 15.850000 20120412Warning: Null value is eliminated by an aggregate or other SET operation.(4 row(s) affected)*/
[解决办法]