MySQL 工作台:创建组 ID 和数据透视表

Question

我有下表，其中包含一些记录。

表： tbl_group_test

CREATE TABLE tbl_group_test
(
    YearID varchar(20),
    ProID varchar(20),
    CodeSecID varchar(20),
    CodeMerID int,
    val int,
    cid varchar(20)
);

记录插入：

INSERT INTO tbl_group_test VALUES
('2017000001','4PO251','1IJ25',1,0,'a'),
('2017000002','4PO241','1IJ25',1,0,'a'),
('2017000003','4PO272','1IJ25',1,0,'a'),
('2017000004','4PO243','1IJ25',1,0,'a'),
('2017000005','4PO276','1IJ25',1,0,'a'),
('2017000006','4PO251','1IJ25',1,0,'a'),
('2017000007','4PO249','1IJ25',1,0,'a'),
('2017000008','4PO278','1IJ25',1,0,'a'),
('2017000009','4PO240','1IJ25',1,0,'a'),
('2017000010','4PO290','1IJ25',1,0,'a'),
('2017000011','4PO251','1IJ25',1,0,'b'),
('2017000012','4PO241','1IJ25',1,0,'b'),
('2017000013','4PO272','1IJ25',1,0,'b'),
('2017000014','4PO243','1IJ25',1,0,'b'),
('2017000015','4PO276','1IJ25',1,0,'b'),
('2017000016','4PO251','1IJ25',1,0,'b'),
('2017000017','4PO241','1IJ25',1,0,'b'),
('2017000018','4PO272','1IJ25',1,0,'b'),
('2017000019','4PO243','1IJ25',1,0,'b'),
('2017000020','4PO276','1IJ25',1,0,'b');

备注：现在我想为每 10 条记录创建一个组 ID，并希望显示基于该组 ID 的数据透视表。

预期成果：

Group ID    YearID1       YearID2 .....          YearID10     CodeSecID      CodeMerID    val    cid 
1           2017000001    2017000002             2017000010   1IJ25          1            0      a
2           2017000011    2017000012             2017000020   1IJ25          1            0      b

Answer 1

select 
        max(case when s.col = 1 then s.yearid else '' end) as yrid1,
        max(case when s.col = 2 then s.yearid else '' end) as yrid2,
        max(case when s.col = 3 then s.yearid else '' end) as yrid3,
        max(case when s.col = 4 then s.yearid else '' end) as yrid4,
        max(case when s.col = 5 then s.yearid else '' end) as yrid5,
        max(case when s.col = 6 then s.yearid else '' end) as yrid6,
        max(case when s.col = 7 then s.yearid else '' end) as yrid7,
        max(case when s.col = 8 then s.yearid else '' end) as yrid8,
        max(case when s.col = 9 then s.yearid else '' end) as yrid9,
        max(case when s.col = 10 then s.yearid else '' end) as yrid10,
        max(codesecid) codesecid,
        max(codemerid) codemerid,
        max(val) val,
        max(cid) cid
from
(    
select t.* ,
        if(@rn > 9, @rn := 1,@rn:=@rn +1) col,
        if(@rn = 1, @block:=@block + 1,@block:=@block) block
from (select @block:=0,@rn:=0) rn, tbl_group_test t
order   by yearid
) s
group by s.block