超前聚结

Question

如果可能的话，请有人帮我一下。我有以下数据：

ID|Dia|Wid|BM1|BM2 |BM3 |BO|OFF|SEAT|NUT |R1|R2|R3|R4|R5|R6|R7 |R8 |R9 |
154|17|8.5|120|NULL|NULL|8 |54| Con |Acor|  |  |  |  |  |BO|   |   |   |
156|18|9.5|120|NULL|NULL|8 |5 | Deg |ZOOM|  |  |  |  |  |BO|OFF|   |NUT|    
179|19|10 |120|NULL|NULL|8 |79| STAR|Acor|  |  |  |  |  |BO|   |SEA|   |

我想显示这样的数据：

ID|Dia|Wid|BM1|BM2 |BM3 |BO|OFF|SEAT|NUT  |R1|R2 |R3 |R4|R5|R6|R7 |R8|R9|
154|17|8.5|120|NULL|NULL|8 |54 |Con |Acor |BO|   |   |  |  |  |   |  |  |   
156|18|9.5|120|NULL|NULL|8 |5  |Deg |ZOOM |BO|OFF|NUT|  |  |  |   |  |  |
179|19|10 |120|NULL|NULL|8 |79 |STAR|Acor |BO|SEA|   |  |  |  |   |  |  |   

所有来自Rn字段的值都应该移到左边，在字段之间不留下任何空白。例如，只有在填充r2 r1时，列r3才有值，同样，只有当r1和r2填充时，才能填充列r3。

Answer 1

SQL server的一个想法

;WITH ordered AS
(
    SELECT t.ID, o.Ordering, o.Value FROM table t
    OUTER APPLY 
    (
        SELECT 
            ROW_NUMBER() OVER (ORDER BY Id) AS Ordering, 
            Value 
        FROM 
            (VALUES
                (1, R1), (2, R2), (3, R3), (4, R4), (5, R5), (6, R6), (7, R7), (8, R8), (9, R9)
            ) data(Id, Value) 
        WHERE NULLIF(Value, '') IS NOT NULL
    ) AS o
)
SELECT 
    *, 
    (SELECT Value FROM ordered WHERE ID = t.ID AND Ordering = 1) AS R1,
    (SELECT Value FROM ordered WHERE ID = t.ID AND Ordering = 2) AS R2,
    (SELECT Value FROM ordered WHERE ID = t.ID AND Ordering = 3) AS R3,
    (SELECT Value FROM ordered WHERE ID = t.ID AND Ordering = 4) AS R4,
    (SELECT Value FROM ordered WHERE ID = t.ID AND Ordering = 5) AS R5,
    (SELECT Value FROM ordered WHERE ID = t.ID AND Ordering = 6) AS R6,
    (SELECT Value FROM ordered WHERE ID = t.ID AND Ordering = 7) AS R7,
    (SELECT Value FROM ordered WHERE ID = t.ID AND Ordering = 8) AS R8,
    (SELECT Value FROM ordered WHERE ID = t.ID AND Ordering = 9) AS R9
FROM table t

SQL Fiddle