Display record in customize view

Question

I have two tables named

style:

===================
   id  |  style
===================
    1  |  x
===================
    2  |  y

size :

===========================================
   id  |  size_name  | style_id  | Quantity
===========================================
    1  |  2T         |  1        |  200
===========================================
    2  |  3T         |  1        |  100
===========================================
    3  |  4T         |  2        |  250
===========================================
    4  |  2T         |  2        |  500

Using this two tables I want a report like below:

=============================================
  Style  |              size                      
=============================================
         |    2T   |    3T    |    4T    |    
=============================================
   x     |    200  |    100   |    0     |
=============================================
   y     |    500  |     0    |   250    |

Thanks in advance.Please help

then start with what you can do, then post what is not working for you on your code. — Prix, Jun 29 '13 at 09:10
It's pretty easy if you break down the output and take one row at a time..think logically. You will get the solution. To nudge you in a right direction, use `DISTINCT sizename` query to get the second row of your customizable table — asprin, Jun 29 '13 at 09:16
We at stackoverflow expect people to show some effort, you do know how to connect to a database ? you do know how to loop and read the values ? start by writing what you know until you get to the part you don't know then post back what you have done. [**Also take your time to visit the TOUR**](http://stackoverflow.com/about) — Prix, Jun 29 '13 at 09:19

score 1 · Answer 1 · answered Jun 29 '13 at 09:34

1

Static query (if you have known and limited numbers of size_name)

SELECT st.Style
  ,SUM(CASE WHEN s.size_name = '2T' THEN Quantity ELSE 0 END) AS `2T`
  ,SUM(CASE WHEN s.size_name = '3T' THEN Quantity ELSE 0 END) AS `3T`
  ,SUM(CASE WHEN s.size_name = '4T' THEN Quantity ELSE 0 END) AS `4T`
FROM Size s
JOIN Style st ON s.style_id = st.id
GROUP BY st.Style;

Dynamic query (if you do not know the number of size_name)

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'SUM(CASE WHEN `size_name` = ''',
      `size_name`,
      ''' THEN Quantity ELSE 0 END) AS `',
      `size_name`, '`'
    )
  ) INTO @sql
FROM Size;

SET @sql = CONCAT('SELECT st.Style , ', @sql, '
                  FROM Size s
                  JOIN Style st ON s.style_id = st.id
                   GROUP BY st.Style 
                  ');

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

Output:

╔══════╦═════╦═════╦═════╗
║ TYLE ║ 2T  ║ 3T  ║ 4T  ║
╠══════╬═════╬═════╬═════╣
║ x    ║ 200 ║ 100 ║   0 ║
║ y    ║ 500 ║   0 ║ 250 ║
╚══════╩═════╩═════╩═════╝

See this SQLFiddle

answered Jun 29 '13 at 09:34

Himanshu

31,810
31
111
133

Its not working un mysql,i copy and paste it in mysql but showing an empty result set. – Wolverine Jun 29 '13 at 11:00
WHAT SHOULD I DO NOW. – Wolverine Jun 29 '13 at 11:00
@Wolverine - It's working fine. Did you check [this SQLFiddle](http://sqlfiddle.com/#!2/4880c/1)? – Himanshu Jun 29 '13 at 11:02
Yah,this cide is working in sqlFiddle but not in local server.What to do? – Wolverine Jun 29 '13 at 11:11
@Wolverine - Not possible. You probably missing something. Is your tables name, columns name and sample data same as given in this example? – Himanshu Jun 29 '13 at 11:13
Showing this "Unknown prepared statement handler (stmt) given to EXECUTE" – Wolverine Jun 29 '13 at 11:14
Is it running in wamp server? – Wolverine Jun 29 '13 at 11:15
yes,all are same like example.... – Wolverine Jun 29 '13 at 11:16
@Wolverine - Same issue is [here](http://stackoverflow.com/questions/12270953/combine-multiple-rows-into-one-mysql#comment-16458111). You need to do it with separate queries. One to build the `stmt` and one do execute it. See the [comment of the OP here](http://stackoverflow.com/questions/12270953/combine-multiple-rows-into-one-mysql#comment-16660514) – Himanshu Jun 29 '13 at 11:24
I dint get this solution,can you help by providing another query which is something like static,whcih working perfectly? @ hims056 – Wolverine Jun 29 '13 at 11:34

echo_Me · Answer 2 · 2013-06-29T09:53:44.177

0

try this

  select 
  style , 
  max(case when size_name = '2T' then Quantity else 0 end) as '2T' ,
  max(case when size_name = '3T' then Quantity else 0 end) as '3T' ,
  max(case when size_name = '4T' then Quantity else 0 end) as '4T'
  from style inner join size
  on size.style_id = style.id
  group by style.id

demo here

edited Jun 29 '13 at 09:53

answered Jun 29 '13 at 09:25

echo_Me

37,078
5
58
78

size name isnot static,its dynamic,If dynamic what will be the query @ echo Samir – Wolverine Jun 29 '13 at 10:02

Display record in customize view

2 Answers2

See this SQLFiddle