0

I'm working with mysql. Need to create a view where the query must include some MIN(case when then else end) statements.
I don't know how many Case statements do I have to write, because this value depends upon one of the tables related to the view, say table B. So my view must have a dynamic number of cases inside the select statement. I want to know how can it be achieved. I think it can be achieved by counting the number of rows in table B, then setting this value as a variable (@q) and then using a while loop inside the select statement to dynamically create as many MIN(case when then else end) statements required. Thanks for the help.

digitai
  • 1,870
  • 2
  • 20
  • 37
  • What you want to do cannot be done. You cannot define a view with a variable number of columns. – Gordon Linoff May 16 '13 at 02:22
  • Thanks, is there any way to achieve this, in the same way MSSQL's pivot does?. By the way I have your book in my shelf. – digitai May 16 '13 at 02:28
  • 2
    . . The "normal" way to do this in MySQL is to have one column with the values concatenated together using `group_concat()`. You'll need to post more information about the problem, though, to figure out a solution. (Hope you find the book helpful ;) – Gordon Linoff May 16 '13 at 02:29
  • Ok, the context of my work is a survey management app, where users can create their own surveys and assign different weights or values to the questions they ask for. The view is intended to customize each user's data report based on their previous definition of questions, answers and values, which is done in table _B_. That way, I need to customize the view generation which a dynamic number of pivots or case statements. When I worked with MSSQL server I solved this kind of need with pivot. Thanks. – digitai May 16 '13 at 02:58
  • u could read from this link: http://stackoverflow.com/questions/12004603/mysql-pivot-row-into-dynamic-number-of-columns – dArc May 16 '13 at 04:06
  • 1
    @datelligent . . . It sounds like you want to dynamically create a view for a particular table. That is, given a table `B`, create a view specific to `B` with a particular set of columns. Is that a correct understanding? – Gordon Linoff May 16 '13 at 13:10
  • I've been working around with @dArc suggestion: Yes indeed I need to create a view with two related tables. I'll post code on next comment. due to character limitation. – digitai May 16 '13 at 23:01
  • `SET @sql = NULL; Select group_concat(distinct(concat('min(if(b.id_ift_question=''', b.id_ift_question, ''', b.computo*a.ift_answer, NULL) as "', b.id_ift_question, '"'))) into @sql From datellig_ift.a02_view_respuestas a Left Join datellig_ift.ift_computo_4 b On a.id_man_medicion = b.ift_survey And a.id_ift_question = b.id_ift_question;` – digitai May 16 '13 at 23:03
  • `SET @sql=CONCAT('Select a.id_man_medicion, a.id_ift_oltp_01, a.id_ift_token, a.token, a.emision_date, a.emision_hour, a.id_dim_brand, a.brand, a.id_dim_city, a.city, a.id_dim_pos, a.pos, a.bill_amount, Sum(Case When a.id_ift_question = "13" Then a.ift_answer Else Null End) As id_gender, Min(Case When a.id_ift_question = "13" Then a.answer_string Else Null End) As gender, Sum(Case When a.id_ift_question = "14" Then a.ift_answer Else Null End) As id_age, Min(Case When a.id_ift_question = "14" Then a.answer_string Else Null End) As age_range', @sql, '` – digitai May 16 '13 at 23:05
  • `From datellig_ift.a02_view_respuestas a Left Join datellig_ift.ift_computo_4 b On a.id_man_medicion = b.ift_survey And a.id_ift_question = b.ift_question group By a.id_ift_oltp_01, a.token, a.emision_date, a.emision_hour'); ` – digitai May 16 '13 at 23:05
  • `PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; ` – digitai May 16 '13 at 23:06

0 Answers0