How to separate text words from a comma column?

Asked

Viewed 1,716 times

4

There is a ready function in Firebird 2.5 that is the inverse of the list function?

When you make a

select list(campo,',') from tabela

is returned all field values in a single line separated by comma, I wanted a function that separates the field varchar in several columns

tabelax
codigo      informacao
 1          'alfa, beta, gama'
 2          'delta, peta, omega'

by doing the select codigo,comando(informacao,',') as inf from tabelax he returns:

codigo  inf
  1     alfa
  1     beta
  1     gama
  2     delta
  2     peta
  2     omega
firebird

  • Pivot and unpivot operations, I found this (https://www.mail-archive.com/[email protected]/msg06635.html) on Google

  • sorry, but either and I didn’t understand the operation of the pivot or he doesn’t do what I need

  • In case it would be unpivot, the example there is indeed not good, this post ( http://stackoverflow.com/questions/22064313/how-to-do-a-sql-grouping-by-multiple-columns-including-days ) can help you

  • I understood the pivot/unpivot is to convert row to column and vice versa. and this command tbm is not supported by Firebird.

  • Yes, but you find pages that quote how to simulate for Firebird.

1 answer

1


Unfortunately Firebird does not have a command for this requiring the creation of a Storeprocedure to do this it is necessary to create a strore Procedure and use it in sql.

Example:

set term ^ ;

create or alter procedure f_retorna_lista(in_id integer,in_lista varchar(550))
returns (out_id integer, out_str varchar(100) )
as
declare variable var_pos integer;
declare variable var_next_pos integer;
declare variable var_length integer;
begin
  var_length = char_length(:in_lista);
  var_pos = 1;
  while (var_pos <= var_length) do
  begin
  var_next_pos = position(',',:in_lista,:var_pos);
  if (:var_next_pos = 0) then
    var_next_pos = var_length+1;
  out_str = trim(substring(:in_lista from :var_pos for (:var_next_pos - :var_pos)));
  out_id = in_id;
  suspend;
  var_pos = var_next_pos+1;
  end
end^

where I executed select t.codigo, sp.out_str from tabelax t left join (f_retorna_lista(codigo,informacao)) sp on sp.out_id = t.codigo

Browser other questions tagged firebird

You are not signed in. Login or sign up in order to post.