sql server 行转列

 

看了这个需求同事表示很麻烦，列是动态的，多一天多一个订单类型就多一列，我看了下，这不是sql行转列的节奏么。这个还真没搞过。大家pp这个设计是否合理：

1、模拟数据

2、确定思路，网上看了下动态sql实现，自己想了想好像还需要祭出游标神器，一番调式成功了。没有性能问题啊，呵呵，交货了。跟同事说了下思路，他表示看不惯游标里面嵌套游标。好吧，你自己实现吧，偶是有经验的运用游标，不是乱用，爱用不用。呵呵...

 

USE [SK_WMS_DB]
GO
/****** Object:  StoredProcedure [dbo].[sp_warehouse_sum_byOrderDate]    Script Date: 01/15/2014 17:16:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[sp_warehouse_sum_byOrderDate]
AS
BEGIN
   declare @sql varchar(max)
   declare @v_order_flag varchar(10)
   declare @v_dt Date
   declare @v_report_columnName varchar(60)
   --订单类型临时表
   SELECT distinct [OrderFlag] INTO #OrderFlag_Info FROM [SK_WMS_DB].[dbo].[Table_Report_Test] 
   ORDER BY [OrderFlag] DESC
   --订单日期临时表
   SELECT distinct OrderDT INTO #OrderDT_Info FROM [SK_WMS_DB].[dbo].[Table_Report_Test] 
   ORDER BY OrderDT
   --表头临时表
   SELECT CAST(NULL AS DATE) ORDER_DT,
          CAST ('' AS varchar(60)) AS REP_COL_NAME,
          CAST ('' AS varchar(10)) AS OrderFlag
   INTO #REPORT_COLUMN

   declare cur_order_flag cursor for select OrderFlag from #OrderFlag_Info
   open cur_order_flag
   fetch next from cur_order_flag into @v_order_flag
    --根据订单类型生成列名
	while @@FETCH_STATUS = 0 
	   begin
		  declare cur_OrderDT cursor for select OrderDT from #OrderDT_Info 
		  open cur_OrderDT
		  fetch next from cur_OrderDT into @v_dt 
		  --日期+订单类型
		  while @@FETCH_STATUS = 0 
			 begin
				INSERT INTO #REPORT_COLUMN
				select @v_dt,
					   CAST(substring(CONVERT(char(10),@v_dt,102),6,5)+ @v_order_flag as varchar(60)),
					   @v_order_flag
				fetch next from cur_OrderDT into @v_dt 
			 end       
		  close cur_OrderDT
		  deallocate cur_OrderDT   
	      
		  INSERT INTO #REPORT_COLUMN  
		  select '1901-01-01',CAST(RTRIM(LTRIM(@v_order_flag))+'小计'  as varchar(60)),@v_order_flag      
		  fetch next from cur_order_flag into @v_order_flag               
	   end
    close cur_order_flag
    deallocate cur_order_flag   

    declare @colText varchar(max)
	declare cur_tbl cursor for SELECT REP_COL_NAME FROM #REPORT_COLUMN 
                               WHERE ORDER_DT IS NOT NULL
                           
    --返回结果临时表
	SELECT CAST ('' AS VARCHAR(255)) AS 仓库名称,
		   CAST ('' AS VARCHAR(255)) AS 库内区域名称,
		   CAST ('' AS VARCHAR(255)) AS 物料号码,
		   CAST ('' AS VARCHAR(255)) AS 品名,
		   CAST ('' AS VARCHAR(255)) AS 包装         
	INTO #Result_Report
    --循环增加列                       
    open cur_tbl
    fetch next from cur_tbl into @v_report_columnName 
    while @@FETCH_STATUS = 0 
     begin
       SET @colText = RTRIM(LTRIM(@v_report_columnName))
       SET @sql = 'ALTER TABLE #Result_Report ADD ['+@colText+'] [numeric](18, 2) default 0'
       EXEC(@sql)
       fetch next from cur_tbl into @v_report_columnName 
     end     
    close cur_tbl
    deallocate cur_tbl  
    --清空临时表记录
    DELETE FROM #Result_Report
    --开始准备数据,每个仓库循环一次
    declare @WareHouse varchar(60)
    declare @qty numeric(18,2)
    declare cur_data_fill 
    cursor for SELECT distinct [WareHouseName] FROM [SK_WMS_DB].[dbo].[Table_Report_Test]
    --填充记录                  
    open cur_data_fill
    fetch next from cur_data_fill into @WareHouse 
    while @@FETCH_STATUS = 0 
    begin
       insert into #Result_Report (仓库名称,库内区域名称,物料号码,品名,包装)
       select @WareHouse,'' 库内区域名称,'' 物料号码,'' 品名,'' 包装
              
       declare cur_sum_qty 
       cursor for  
       SELECT REP_COL_NAME,ORDER_DT,OrderFlag FROM #REPORT_COLUMN
       WHERE ORDER_DT IS NOT NULL    
       open cur_sum_qty
       fetch next from cur_sum_qty into @v_report_columnName,@v_dt,@v_order_flag
       while @@FETCH_STATUS = 0
          begin
             IF @v_dt <> '1901-01-01'
               SELECT @qty = isnull(SUM(t.Qty),0) FROM dbo.Table_Report_Test t
               WHERE t.WareHouseName  = @WareHouse 
                  AND t.OrderDT = @v_dt
                  AND t.OrderFlag = @v_order_flag
             ELSE 
               SELECT @qty = isnull(SUM(t.Qty),0) FROM dbo.Table_Report_Test t
               WHERE t.WareHouseName  = @WareHouse                 
                  AND t.OrderFlag = @v_order_flag
                   
             SELECT @sql = 'UPDATE #Result_Report SET ['+LTRIM(RTRIM(@v_report_columnName))+'] = '+CAST(@qty AS VARCHAR) +
                           ' WHERE 仓库名称 = '''+@WareHouse+''''
             EXEC(@sql) 
             fetch next from cur_sum_qty into @v_report_columnName,@v_dt,@v_order_flag
          end 
       close cur_sum_qty
       deallocate cur_sum_qty 
       fetch next from cur_data_fill into @WareHouse 
    end                     
    close cur_data_fill
    deallocate cur_data_fill  

    --返回结果集
    SELECT * FROM #Result_Report
END

 3、成果：

写在这里自己备忘,也给同行看看还有更好的方法不,可以随便喷...