SQL查询水平显示和连接[重复]

Question

Possible Duplicate:
Join two tables (with a 1-M relationship) where the second table needs to be ‘flattened’ into one row

Although I took a intro to sql course, I cannot figure this one out at the moment.

I have two tables :

table1:

    user_id date  value
    ids     dates values

table2:

    user_id user_names
    ids     names

I would like a MYSQL query that would display the following results:

    /   name1  name2  name3 etc
  date1 val1-1 val1-2 val1-3 
  date2 val2-1 val2-2 val2-3

I remember there is a way to do that, right? any help would be appreciated.

Answer 1

Rotating data in this fashion is called a pivot. Unfortunately MySQL does not have a pivot function so you will have to replicate it using an aggregate function and a CASE expression.

Sample Data: used for this query

CREATE TABLE Table1
    (`user_id` int, `date` datetime, `value` int)
;

INSERT INTO Table1
    (`user_id`, `date`, `value`)
VALUES
    (1, '2013-01-01 00:00:00', 100),
    (2, '2013-01-01 00:00:00', 200),
    (1, '2013-01-02 00:00:00', 500)
;

CREATE TABLE Table2
    (`user_id` int, `user_names` varchar(4))
;

INSERT INTO Table2
    (`user_id`, `user_names`)
VALUES
    (1, 'John'),
    (2, 'Tim')
;

If you know all of the values to turn into columns (for your example the names), then you could hard-code them and the SQL will be similar to this:

select 
  date,
  max(case when rownum = 1 then value end) as John,
  max(case when rownum = 2 then value end) as Tim
from
(
  select date, value, user_names,
    @row:=case when @prev=date then @row else 0 end + 1 as rownum,
    @prev:=date 
  from
  (
    select t1.date, t1.value, t2.user_names
    from table1 t1
    inner join table2 t2
      on t1.user_id = t2.user_id
    order by date, user_names
  ) d, (SELECT @row:=0, @prev:=null) r
  order by date, user_names
) src
group by date

See SQL Fiddle with Demo. As you can see, I had to implement user variables to assign a row number to each name within the date. This tells you how many distinct name values there will be to turn into columns.

For your situation though, you have an unknown number of names for each date, so you will need to use dynamic SQL within a prepared statement.

In this case the code will be similar to this:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'max(case when rownum = ',
      rownum,
      ' then value end) AS `',
      user_names, '`'
    )
  ) INTO @sql
from
(
  select date, value, user_names,
    @row:=case when @prev=date then @row else 0 end + 1 as rownum,
    @prev:=date 
  from
  (
    select t1.date, t1.value, t2.user_names
    from table1 t1
    inner join table2 t2
      on t1.user_id = t2.user_id
    order by date, user_names
  ) d, (SELECT @row:=0, @prev:=null) r
  order by date, user_names
) src;


SET @sql = CONCAT('SELECT date, ', @sql, ' 
                  from
                  (
                    select date, value, user_names,
                      @row:=case when @prev=date then @row else 0 end + 1 as rownum,
                      @prev:=date 
                    from
                    (
                      select t1.date, t1.value, t2.user_names
                      from table1 t1
                      inner join table2 t2
                        on t1.user_id = t2.user_id
                      order by date, user_names
                    ) d, (SELECT @row:=0, @prev:=null) r
                    order by date, user_names
                  ) src
                  group by date');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

See SQL Fiddle with Demo. Both versions will give the result:

|                           DATE | JOHN |    TIM |
--------------------------------------------------
| January, 01 2013 00:00:00+0000 |  100 |    200 |
| January, 02 2013 00:00:00+0000 |  500 | (null) |