How can you group this:

F1 F2 F3 -------------------------- 1 null null null 2 null null null 3 4 null null null 5 null null null 6

In it:

 F1 F2 F3 ---------------- 1 2 3 4 5 6
  • Set sorted? - pegoopik
  • Only one field is filled in each line - Vitaliy
  • one
    @Developer, this is not enough. Why, for example, the first line should be 1 2 3, and not, say, 1 5 3 or 4 2 3? - msi

2 answers 2

If you have only three, then something like this.

Data:

 declare @data table (row_num int, F1 int, F2 int, F3 int); insert into @data values (1, 1, null, null) ,(2, null, 2, null) ,(3, null, null, 3) ,(4, 4, null, null) ,(5, null, 5, null) ,(6, null, null, 6) ;

Request:

 ;with data as ( select rn = row_num - 1, * from @data ) select F1 = max(F1), F2 = max(F2), F3 = max(F3) from data group by rn / 3;

Result:

 F1 F2 F3 ----------- ----------- ----------- 1 2 3 4 5 6

The order of the rows still needs to be defined - either explicitly (as in the example) or indirectly (using the row_number() over (order by ...) function), otherwise there is no guarantee that the rows will be grouped as necessary.

    If there are no additional restrictions, i.e. values ​​are not sorted and can be any, then it can be done in three readings like this:

     DECLARE @T TABLE(F1 INT, F2 INT, F3 INT) INSERT @T VALUES (1,null,null), (null,2,null), (null,null,3), (4,null,null), (7,5,null), (8,null,6), (null,null,null), (null,null,9), (10,null,null) SELECT F1, F2, F3 FROM( SELECT ROW_NUMBER()OVER(ORDER BY F1)N1, F1 FROM @T WHERE F1 IS NOT NULL )T1 FULL JOIN( SELECT ROW_NUMBER()OVER(ORDER BY F2)N2, F2 FROM @T WHERE F2 IS NOT NULL )T2 ON N1=N2 FULL JOIN( SELECT ROW_NUMBER()OVER(ORDER BY F3)N3, F3 FROM @T WHERE F3 IS NOT NULL )T3 ON N3=ISNULL(N1,N2) --результат: /* F1 F2 F3 1 2 3 4 5 6 7 NULL 9 8 NULL NULL 10 NULL NULL */

    If there is only one non-NULL value in one line, then it can be grouped in one reading:

     DECLARE @T TABLE(F1 INT, F2 INT, F3 INT) INSERT @T VALUES (1,null,null), (null,2,null), (null,null,3), (4,null,null), (null,5,null), (8,null,null), (11,null,null), (null,null,9), (null,null,99), (10,null,null) SELECt GRP, F1, F2, F3 FROM( SELECT *, ROW_NUMBER()OVER(PARTITION BY [Type] ORDER BY Value)GRP FROM( SELECT CASE WHEN F1 IS NOT NULL THEN F1 WHEN F2 IS NOT NULL THEN F2 WHEN F3 IS NOT NULL THEN F3 END Value, CASE WHEN F1 IS NOT NULL THEN 'F1' WHEN F2 IS NOT NULL THEN 'F2' WHEN F3 IS NOT NULL THEN 'F3' END [Type] FROM @T )T )T PIVOT( MAX(Value) FOR [Type] IN (F1, F2, F3) )pvt --результат: /* GRP F1 F2 F3 1 1 2 3 2 4 5 9 3 8 NULL 99 4 10 NULL NULL 5 11 NULL NULL */