expand fields to become column headers

Codewalkers Forums Sponsor:

March 5th, 2003, 08:32 AM

dizy

Join Date: Apr 2007

Location: davao city, philippines

Posts: 2

Time spent in forums: < 1 sec
Reputation Power: 0

expand fields to become column headers

hi, how can i convert fields into a row? i mean, i want to expand a certain column in such a way that there will be no redundancy in the column.

example from table:
TOOLS YEAR PURCHASE
hammer 2000 2
plier 2000 1
screw 2000 4
hammer 2001 3
plier 2001 1
screw 2001 4
hammer 2002 2
plier 2002 1
screw 2002 1

i want to convert it this way!
TOOLS 2000 2001 2002 sum(purchase)
hammer 2 3 2 7
plier 1 1 1 3
screw 4 4 1 9

in this way, it is much easy to understand. the column 2000 involves the sum of the tools in this year and so on. the sum(purchase) is the total number of purchase from year 2000, 2001, and 2002 of a certain tool.

our proposed statement would be to include an HTAB word in the query. for example:
select tools, htab(year) from product group by tool;

the htab would recognize the column to expand based from the fields.

i am making a udf for mysql and had a hard time doing this.

can somebody please help me with the codes for doing the udf to come up with such table? included here is my whole udf program code but not yet finished. thanks!

php Code:

Original - php Code

struct htab_data
{
  unsigned long long count;
  long long total;
};

my_bool
htab_init( UDF_INIT* initid, UDF_ARGS* args, char* message )
{
  struct htab_data*	data;

if (args->arg_count < 1)
  {
    strcpy(
	   message,
	   "wrong number of arguments: HTAB() requires one argument"
	   );
    return 1;
  }

if ((args->arg_type[0] != INT_RESULT))/* || (args->arg_type[1] != INT_RESULT*/
  {
    strcpy(
	   message,
	   "wrong argument type: HTAB() requires SOMETHING"
	   );
    return 1;
  }

/*
  **	force arguments to double.
  */
  /*args->arg_type[0]	= REAL_RESULT;
    args->arg_type[1]	= REAL_RESULT;*/

initid->maybe_null	= 0;		// The result may be null
  initid->decimals	= 0;		// We want 4 decimals in the result
  initid->max_length	= 20;		// 6 digits + . + 10 decimals

data = new struct htab_data;
  data->total = 0;

initid->ptr = (char*)data;

return 0;
}

void
avgcost_deinit( UDF_INIT* initid )
{
  delete initid->ptr;
}

void
htab_reset( UDF_INIT* initid, UDF_ARGS* args, char* is_null, char* message )
{
  struct htab_data* data = (struct htab_data*)initid->ptr;
  data->total = 0;
  data->count = 0;

*is_null = 0;
  htab_add( initid, args, is_null, message );
}

void
htab_add( UDF_INIT* initid, UDF_ARGS* args, char* is_null, char* message )
{
  if (args->args[0] && args->args[1])
  {
    struct htab_data* data	= (struct htab_data*)initid->ptr;
    long long quantity		= *((long long*)args->args[0]);
    long long newquantity	= data->total + quantity;
    //long long number	= *((long long*)args->args[1]);

data->count++;

if (   ((data->total >= 0) && (quantity < 0))
	   || ((data->total <  0) && (quantity > 0)) )
    {
      /*
      **	passing from + to - or from - to +
      */
      if (   ((quantity < 0) && (newquantity < 0))
	     || ((quantity > 0) && (newquantity > 0)) )
      {
	    data->total	= (newquantity);
      }
      /*
      **	sub q if totalq > 0
      **	add q if totalq < 0
      */
      else
      {
	    //number	  = data->total;
	    data->total  = (newquantity);
      }
    }
    else
    {
      data->total	+= (quantity);
    }

if (data->total== 0)
      data->total = 0;
  }
}

long long
htab( UDF_INIT* initid, UDF_ARGS* args, char* is_null, char* error )
{
  /* This is the function in which the fields defined in the
     htab() would be converted into a column head*/

struct htab_data* data = (struct htab_data*)initid->ptr;
  if (!data->count || !data->total)
  {
    *is_null = 1;
    return 0;
  }

*is_null = 0;
  return data->total;
}

 
 
struct htab_data
{
  unsigned long long count;
  long long total;
};
 
 
my_bool
htab_init( UDF_INIT* initid, UDF_ARGS* args, char* message )
{
  struct htab_data* data;
 
  if (args->arg_count < 1)
  {
    strcpy(
       message,
       "wrong number of arguments: HTAB() requires one argument"
       );
    return 1;
  }
 
  if ((args->arg_type[0] != INT_RESULT))/* || (args->arg_type[1] != INT_RESULT*/
  {
    strcpy(
       message,
       "wrong argument type: HTAB() requires SOMETHING"
       );
    return 1;
  }
 
  /*
  **    force arguments to double.
  */
  /*args->arg_type[0]   = REAL_RESULT;
    args->arg_type[1]   = REAL_RESULT;*/
 
  initid->maybe_null    = 0;        // The result may be null
  initid->decimals  = 0;    // We want 4 decimals in the result
  initid->max_length    = 20;      // 6 digits + . + 10 decimals
 
  data = new struct htab_data;
  data->total = 0;
 
  initid->ptr = (char*)data;
 
  return 0;
}
 
void
avgcost_deinit( UDF_INIT* initid )
{
  delete initid->ptr;
}
 
void
htab_reset( UDF_INIT* initid, UDF_ARGS* args, char* is_null, char* message )
{
  struct htab_data* data = (struct htab_data*)initid->ptr;
  data->total = 0;
  data->count = 0;
 
  *is_null = 0;
  htab_add( initid, args, is_null, message );
}
 
 
void
htab_add( UDF_INIT* initid, UDF_ARGS* args, char* is_null, char* message )
{
  if (args->args[0] && args->args[1])
  {
    struct htab_data* data  = (struct htab_data*)initid->ptr;
    long long quantity    = *((long long*)args->args[0]);
    long long newquantity   = data->total + quantity;
    //long long number  = *((long long*)args->args[1]);
 
    data->count++;
 
    if (   ((data->total >= 0) && (quantity < 0))
       || ((data->total <  0) && (quantity > 0)) )
    {
      /*
      **    passing from + to - or from - to +
      */
      if (   ((quantity < 0) && (newquantity < 0))
         || ((quantity > 0) && (newquantity > 0)) )
      {
        data->total = (newquantity);
      }
      /*
      **    sub q if totalq > 0
      **    add q if totalq < 0
      */
      else
      {
        //number      = data->total;
        data->total  = (newquantity);
      }
    }
    else
    {
      data->total   += (quantity);
    }
 
    if (data->total== 0)
      data->total = 0;
  }
}
 
 
long long
htab( UDF_INIT* initid, UDF_ARGS* args, char* is_null, char* error )
{
  /* This is the function in which the fields defined in the
     htab() would be converted into a column head*/
 
  struct htab_data* data = (struct htab_data*)initid->ptr;
  if (!data->count || !data->total)
  {
    *is_null = 1;
    return 0;
  }
 
  *is_null = 0;
  return data->total;
}

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: