如何基于对列的单个比较将sql表数据从多行转换为仅一行

debugcn 发表于 Dev

埃德加姆策

我有了一个表12行的代表在某一个月，该表的结构具有代表月份的数字，所以公司的款项给定的公司January = [1]，February = [2]等等。

CREATE TABLE [dbo].[TblMonths](
    [Uid] int not null IDENTITY Primary KEY,
    [Id] [float] NULL,
    [Company] [nvarchar](255) NULL,
    [Days] [float] NULL,
    [Pay] [float] NULL,
    [1] [float] NULL,
    [2] [float] NULL,
    [3] [float] NULL,
    [4] [float] NULL,
    [5] [float] NULL,
    [6] [float] NULL,
    [7] [float] NULL,
    [8] [float] NULL,
    [9] [float] NULL,
    [10] [float] NULL,
    [11] [float] NULL,
    [12] [float] NULL
) ;

insert into [TblMonths] values
(2, 'COMPANY_A',    17,     3   ,0, 0   ,51,0   ,0, 0   ,0  ,0, 0   ,0, 0,  0),
(2, 'COMPANY_A',    18.6,   3   ,0, 0   ,0, 0   ,0, 0   ,56 ,0, 0   ,0, 0,  0),
(2, 'COMPANY_A',    19.2,   5   ,0, 0   ,0, 0   ,0, 0   ,0  ,0, 96  ,0, 0,  0),
(2, 'COMPANY_A',    19.75,  4   ,0, 0   ,0, 0   ,79,0   ,0  ,0, 0   ,0, 0,  0),
(2, 'COMPANY_A',    20,     2   ,0, 0   ,0, 40  ,0, 0   ,0  ,0, 0   ,0, 0,  0),
(2, 'COMPANY_A',    22.5,   2   ,0, 0   ,0, 0   ,0, 0   ,0  ,0, 0   ,0,45,  0),
(2, 'COMPANY_A',    23.5,   2   ,47,0   ,0, 0   ,0, 0   ,0  ,0, 0   ,0, 0,  0),
(2, 'COMPANY_A',    25,     2   ,0, 0   ,0, 0   ,0, 0   ,0  ,0, 0   ,0, 0,  50),
(2, 'COMPANY_A',    26,     2   ,0, 52  ,0, 0   ,0, 0   ,0  ,0, 0   ,0, 0,  0),
(2, 'COMPANY_A',    26.6,   3   ,0, 0   ,0, 0   ,0, 0   ,0  ,80,0   ,0, 0,  0),
(2, 'COMPANY_A',    28.5,   2   ,0, 0   ,0, 0   ,0, 0   ,0  ,0, 0   ,57,0,  0),
(2, 'COMPANY_A',    29.3,   3   ,0, 0   ,0, 0   ,0, 88  ,0  ,0, 0   ,0, 0,  0),
(3, 'COMPANY_B',    13.8,   5   ,0, 0   ,69,0   ,0, 0   ,0  ,0, 0   ,0, 0,  0),
(3, 'COMPANY_B',    15,     2   ,0, 0   ,0, 0   ,0, 0   ,30 ,0, 0   ,0, 0,  0),
(3, 'COMPANY_B',    16,     1   ,0, 0   ,0, 0   ,0, 0   ,0  ,0, 0   ,0,16,  0),
(3, 'COMPANY_B',    20,     3   ,0, 0   ,0, 0   ,0, 0   ,0  ,60,0   ,0, 0,  0),
(3, 'COMPANY_B',    20,     4   ,0, 0   ,0, 0   ,0, 0   ,0  ,0, 0   ,0, 0,  80),
(3, 'COMPANY_B',    20.5,   2   ,0, 0   ,0, 41  ,0, 0   ,0  ,0, 0   ,0, 0,  0),
(3, 'COMPANY_B',    23.25,  4   ,0, 0   ,0, 0   ,0, 0   ,0  ,0,93   ,0, 0,  0),
(3, 'COMPANY_B',    23.3,   3   ,0, 0   ,0, 0   ,70,0   ,0  ,0, 0   ,0, 0,  0),
(3, 'COMPANY_B',    23.5,   2   ,47,0   ,0, 0   ,0, 0   ,0  ,0, 0   ,0, 0,  0),
(3, 'COMPANY_B',    28,     1   ,0, 28  ,0, 0   ,0, 0   ,0  ,0, 0   ,0, 0,  0),
(3, 'COMPANY_B',    29,     3   ,0, 0   ,0, 0   ,0, 87  ,0  ,0, 0   ,0, 0,  0),
(3, 'COMPANY_B',    29.3,   3   ,0, 0   ,0, 0   ,0, 0   ,0  ,0, 0   ,88,0,  0);

我想将每个Company_X 的所有12行转换为仅1，当月份列的值不同于..."Days"0

ID  COMPANY     JAN     FEB     MAR     APR     MAR     JUN     JUL     AGO     SEP     OCT     NOV     DEC
2   COMPANY_A   23.5    26      17      20      19.75   29.3    18.6    26.6    19.2    28.5    22.5    25
3   COMPANY_B   23.5    28      13.8    20.5    23.3    29      15      20      23.25   29.3    16      20

比如on row 7,在january它的值是47，所以我想在列的值"Days"是23.5，在所有其他行id=2，有0这就是为什么我想要搜索的值不同0。

在的情况下，february上排9它的值是52和列Days有26

在march第一行的值为51的情况下，我想取列"Days"上的值为17的值，所以我会得到类似的结果：

ID  COMPANY     JAN     FEB     MAR...     
2   COMPANY_A   23.5    26      17

这样做的脚本如何？

xQbert

如果我们可以假设对于一个公司和 ID，没有一个月会有超过 1 个非零值...如果我们不能假设您是否希望它们 Sum() 在一起？最小值或最大值？只需相应地调整以下内容。注意：如果是 min，我们可能不得不在 else 上省略 0，并在 min 之后合并 0。

现在，如果您想要在没有第 1-12 列的值不是 0 的情况下为零……我们在每个 case 语句中添加一个 else 。

RexTester 演示

SELECT ID
     , Company
     , max(case when [1] <> 0 then Days else 0 end) as JAN
     , max(case when [2] <> 0 then Days else 0 end) as FEB
     , max(case when [3] <> 0 then Days else 0 end) as MAR
     , max(case when [4] <> 0 then Days else 0 end) as APR
     , max(case when [5] <> 0 then Days else 0 end) as MAY
     , max(case when [6] <> 0 then Days else 0 end) as JUN
     , max(case when [7] <> 0 then Days else 0 end) as JUL
     , max(case when [8] <> 0 then Days else 0 end) as AUG
     , max(case when [9] <> 0 then Days else 0 end) as SEP
     , max(case when [10] <> 0 then Days else 0 end) as OCT
     , max(case when [11] <> 0 then Days else 0 end) as NOV
     , max(case when [12] <> 0 then Days else 0 end) as DEC
FROM tblMonths
GROUP BY ID, Company;

给我们：

+----+----+-----------+------+-----+------+------+-------+------+------+------+-------+------+------+-----+
|    | ID |  Company  | JAN  | FEB | MAR  | APR  |  MAY  | JUN  | JUL  | AUG  |  SEP  | OCT  | NOV  | DEC |
+----+----+-----------+------+-----+------+------+-------+------+------+------+-------+------+------+-----+
|  1 |  2 | COMPANY_A | 23,5 |  26 |   17 |   20 | 19,75 | 29,3 | 18,6 | 26,6 |  19,2 | 28,5 | 22,5 |  25 |
|  2 |  3 | COMPANY_B | 23,5 |  28 | 13,8 | 20,5 |  23,3 |   29 |   15 |   20 | 23,25 | 29,3 |   16 |  20 |
+----+----+-----------+------+-----+------+------+-------+------+------+------+-------+------+------+-----+

当然，我不习惯两个月的命名MAR和一个月的命名，AGO所以我调整了它们。

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。