Geek Answers Handbook

MySQL Fixed Column Summary

Below is my operator SELECT, which changes my data well.

My Details Looks Like:

col_a | col_b | col_c | col_d   | Score
-------------------------------------
stuff | stuff | stuff | null    |  5
stuff | stuff | stuff | title_a |  3
stuff | stuff | stuff | title_x |  4

My current Pivot statement is as follows:

SELECT `col_a`, `col_b`, `col_c`,
    MAX(CASE `col_d` WHEN 'title_a' THEN `col_d` end) AS 'Title',
    MAX(CASE `col_d` WHEN 'title_a' THEN `score` end) AS 'Score'
    MAX(CASE `col_d` WHEN 'title_x' THEN `col_d` end) AS 'Title',
    MAX(CASE `col_d` WHEN 'title_x' THEN `score` end) AS 'Score'
    .....

This gives me the following results:

col_a | col_b | col_c | Title   | Score | Title   | Score
---------------------------------------------------------
stuff | stuff | stuff | title_a |   3   | title_x |   4

What I would like to do is check for more titles, but I want to have only four columns in the vault. There will only ever be a maximum of 2 lines that require a turn before writing above. But col_dcan contain any title.

For example, I tried the following:

Now my data is as follows:

col_a | col_b | col_c | col_d    | Score
-------------------------------------
stuff | stuff | stuff | null     |  5
stuff | stuff | stuff | title_a  |  3
stuff | stuff | stuff | title_x  |  4
stuff | stuff | stuff | null     |  5
stuff | stuff | stuff | title_a  |  3
stuff | stuff | stuff | title_bx |  4

Now my Pivot statement looks like this:

SELECT `col_a`, `col_b`, `col_c`,
    MAX(CASE `col_d` WHEN 'title_a' THEN `col_d` end) AS 'Title',
    MAX(CASE `col_d` WHEN 'title_a' THEN `score` end) AS 'Score'
    MAX(CASE `col_d` WHEN 'title_x' THEN `col_d` end) AS 'Title',
    MAX(CASE `col_d` WHEN 'title_x' THEN `score` end) AS 'Score'
    MAX(CASE `col_d` WHEN 'title_bx' THEN `col_d` end) AS 'Second Title',
    MAX(CASE `col_d` WHEN 'title_bx' THEN `score` end) AS 'Score'
    .....

, , , , , title_a title_bx, , null.

, :

col_a | col_b | col_c | Title   | Score | Title    | Score
---------------------------------------------------------
stuff | stuff | stuff | title_a |   3   | title_x  |   4
stuff | stuff | stuff | title_a |   3   | title_bx |   4

, , col_d 4 .

+4
2

, MySQL , Title/Score. , , , col_d title_a, .

:

select a.col_a, a.col_b, a.col_c,
  max(case when a.col_d = 'title_a' then a.col_d end) title1,
  max(case when a.col_d = 'title_a' then a.score end) score1,
  max(case when na.col_d <> 'title_a' then na.col_d end) title2,
  max(case when na.col_d <> 'title_a' then na.score end) score2
from yourtable a
left join
(
  -- need to generate a row number value for the col_d rows
  -- that aren't equal to title_a
  select n.col_a, n.col_b, n.col_c, n.col_d,
    n.score,
    @num:=@num+1 rownum
  from yourtable n
  cross join
  (
    select @num:=0
  ) d
  where n.col_d <> 'title_a'
  order by  n.col_a, n.col_b, n.col_c, n.col_d
) na
  on a.col_a = na.col_a
  and a.col_b = na.col_b
  and a.col_c = na.col_c
  -- in the event you have more than 2 row only return 2
  and na.rownum <= 2  
where a.col_d = 'title_a'  
group by a.col_a, a.col_b, a.col_c, na.rownum;

SQL Fiddle with Demo. :

| COL_A | COL_B | COL_C |  TITLE1 | SCORE1 |   TITLE2 | SCORE2 |
|-------|-------|-------|---------|--------|----------|--------|
| stuff | stuff | stuff | title_a |      3 | title_bx |      4 |
| stuff | stuff | stuff | title_a |      3 |  title_x |      4 |

, 2 , :

select distinct a.col_a, a.col_b, a.col_c,
  a.col_d title1,
  a.score score1,
  na.col_d title2,
  na.score score2
from yourtable a
left join
(
  select n.col_a, n.col_b, n.col_c, n.col_d,
    n.score
  from yourtable n
  where n.col_d <> 'title_a'
) na
  on a.col_a = na.col_a
  and a.col_b = na.col_b
  and a.col_c = na.col_c
where a.col_d = 'title_a';

. SQL Fiddle with Demo. :

| COL_A | COL_B | COL_C |  TITLE1 | SCORE1 |   TITLE2 | SCORE2 |
|-------|-------|-------|---------|--------|----------|--------|
| stuff | stuff | stuff | title_a |      3 |  title_x |      4 |
| stuff | stuff | stuff | title_a |      3 | title_bx |      4 |

, col_a, col_b col_c, , , .

:. , col_d, , , MySQL , t . , NTILE. NTILE . 2 .

SO, Quassnoi NTILE . 2- , ( ) ntile.

:

select 
  x.col_a,
  x.col_b,
  x.col_c,
  max(case when x.splitgroup = 1 then x.col_d end) as Title1,
  max(case when x.splitgroup = 1 then x.Score end) as Score1,
  max(case when x.splitgroup = 2 then x.col_d end) as Title2,
  max(case when x.splitgroup = 2 then x.Score end) as Score2
from
(
  select src.col_a, src.col_b, src.col_c, src.col_d, src.score,
    src.splitGroup,
    @row:=case when @prev=src.splitGroup then @row else 0 end +1 rownum,
    @prev:=src.splitGroup
  from
  (
    -- mimic NTILE function by splitting the total count of rows
    -- over the number of columns we want (2)
    select d.col_a, d.col_b, d.col_c, d.col_d, d.score, 
      FLOOR((@r * @n) / cnt) + 1 AS splitGroup
    from
    (
      select a.col_a, a.col_b, a.col_c, a.col_d, a.score, grp.cnt
      from yourtable a
      inner join 
      (
        select col_a, col_b, col_c, count(*) as cnt
        from yourtable
        where col_d is not null
        group by col_a, col_b, col_c
      ) grp
        on a.col_a = grp.col_a
        and a.col_b = grp.col_b
        and a.col_c = grp.col_c
      where a.col_d is not null
      order by a.col_a, a.col_b, a.col_c
    ) d
    cross join
    (
      -- @n is equal to the number of new pivoted columns we want
      select @n:=2, @group1:='N', @group2:='N', @group3:='N'
    ) v
    WHERE 
      CASE 
        WHEN @group1 <> col_a AND @group2<> col_b AND @group3 <> col_c 
          THEN @r := -1 
          ELSE 0 END IS NOT NULL
      AND (@r := @r + 1) IS NOT NULL
  ) src
  cross join
  (
    -- these vars are used to get the row number once the data is split
    -- this will be needed for the aggregate/group by on the final select
    select @row:=0, @prev:=1
  ) v2
  order by src.splitGroup
) x
group by x.col_a, x.col_b, x.col_c, x.rowNum;

SQL Fiddle with Demo. :

| COL_A | COL_B | COL_C |   TITLE1 | SCORE1 |   TITLE2 | SCORE2 |
|-------|-------|-------|----------|--------|----------|--------|
| stuff | stuff | stuff |  title_a |      3 | title_tt |      1 |
| stuff | stuff | stuff | title_bx |      0 | title_qq |      1 |
| stuff | stuff | stuff |  title_x |      4 |  title_a |      8 |
| stuff | stuff | stuff | title_yy |      3 |  title_h |      4 |
| stuff | stuff | stuff |  title_a |      2 |  title_o |      6 |
+8

. - :

SELECT `col_a`, `col_b`, `col_c`,
MAX(CASE WHEN `col_d` IN('title_a','title_x','title_bx') THEN `col_d` end) AS 'Title',
MAX(CASE WHEN `col_d` IN('title_a','title_x','title_bx') THEN `score` end) AS 'Score'
...
0

More articles:


All Articles