一段关于行转列的SQL，要求有点复杂

Question

本帖最后由 zhfyfd_1 于 2014-10-30 15:01:46 编辑

Answer 1

你两个订单之间没有比较关系，多列表示没有意义，不如拼成一个字符串表示
订单明细号 BOM编号 主键序
---------- ------- ------------------
325375     75400   1,2,3,4,5
325376     24378   6,7,8,9,10

Answer 2

是这样的，我在给一个制造业上ERP，现在在做生产模块，工序必须要按照排序字段作为参照从左至右进行，那些个字段就是工序了。而且一道工序可能在一个产品中出现两次的。

Answer 3

首先我会考虑在程序端实现：
订单325375，直接生成列 1|2|4|10|9。
订单325376，1可以利用第1列，5不存在要添加列，依次类推同样的过程用游标来实现
WITH test(OrderID,ID,BomID,ProcessID,Sort) AS (
    SELECT 325375,1,75400,1,1 UNION ALL
    SELECT 325375,2,75400,2,2 UNION ALL
    SELECT 325375,3,75400,4,3 UNION ALL
    SELECT 325375,4,75400,10,4 UNION ALL
    SELECT 325375,5,75400,9,5 UNION ALL
    SELECT 325376,6,24378,1,1 UNION ALL
    SELECT 325376,7,24378,5,2 UNION ALL
    SELECT 325376,8,24378,2,3 UNION ALL
    SELECT 325376,9,24378,4,4 UNION ALL
    SELECT 325376,10,24378,9,5 UNION ALL
    SELECT 325377,11,39228,1,1 UNION ALL
    SELECT 325377,12,39228,2,2 UNION ALL
    SELECT 325377,13,39228,4,3 UNION ALL
    SELECT 325377,14,39228,8,4 UNION ALL
    SELECT 325377,15,39228,7,5 UNION ALL
    SELECT 325377,16,39228,9,6 
)
SELECT *
  INTO #tb1
  FROM test;CREATE TABLE #tb2 (
    OrderID int,
    ID int,
    BomID int,
    ColNo int
)CREATE TABLE #tb3 (
    ColNo int,
    ProcessID int
)DECLARE @lastOrderID int
DECLARE @ColNo int
DECLARE @maxColNo int
SET @lastOrderID = 0
SET @maxColNo = 0DECLARE @OrderID int
DECLARE @ID int
DECLARE @BomID int
DECLARE @ProcessID int
DECLARE @Sort intDECLARE c_tb1 CURSOR FOR 
    SELECT *
      FROM #tb1
  ORDER BY OrderID,SortOPEN c_tb1FETCH NEXT FROM c_tb1 
 INTO @OrderID,@ID,@BomID,@ProcessID,@SortWHILE @@FETCH_STATUS = 0
BEGIN
    IF (@lastOrderID <> @OrderID)
    BEGIN
        SET @ColNo = 0;
        SET @lastOrderID = @OrderID;
    END    SET @ColNo = (SELECT Min(ColNo)
                    FROM #tb3
                   WHERE ColNo > @ColNo
                     AND ProcessID = @ProcessID
                 );    IF (@ColNo IS NULL)
    BEGIN
        SET @maxColNo = @maxColNo + 1;
        INSERT INTO #tb3 VALUES (@maxColNo,@ProcessID);
        SET @ColNo = @maxColNo;
    END    INSERT INTO #tb2 VALUES(@OrderID,@ID,@BomID,@ColNo)    FETCH NEXT FROM c_tb1 
     INTO @OrderID,@ID,@BomID,@ProcessID,@Sort
ENDCLOSE c_tb1
DEALLOCATE c_tb1
--SELECT * FROM #tb2 ORDER BY OrderID,ColNo
--SELECT * FROM #tb3-- 以下为动态PIVOT
DECLARE @sql varchar(max),
        @columns varchar(max),
        @columnHeaders varchar(max)SET @columns = ''
SET @columnHeaders = ''  SELECT @columns = @columns + ', [' + Convert(varchar(11),ColNo) + ']',
         @columnHeaders = @columnHeaders + ', [' + Convert(varchar(11),ColNo) + '] AS [' + Convert(varchar(11),ProcessID) + ']'
    FROM #tb3
ORDER BY ColNoSET @sql = '
    SELECT OrderID AS [订单明细号],
           BomID AS [BOM编号],
           ' + STUFF(@columnHeaders,1,2,'') + '
      FROM #tb2
     PIVOT (
            Max (ID)
            FOR ColNo IN ( ' + STUFF(@columns,1,2,'') + ')
           ) AS p
  ORDER BY OrderID'
--PRINT @sql
EXEC (@sql)
 订单明细号     BOM编号           1           2           4          10           9           5           2           4           9           8           7           9
----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
     325375       75400           1           2           3           4           5        NULL        NULL        NULL        NULL        NULL        NULL        NULL
     325376       24378           6        NULL        NULL        NULL        NULL           7           8           9          10        NULL        NULL        NULL
     325377       39228          11          12          13        NULL        NULL        NULL        NULL        NULL        NULL          14          15          16

Answer 4

楼主可以问问http://bbs.landingbj.com/f-0-OTL-1-0.html