-4

My input for table

id  value
1    23
1    22
1    24
2    55
2    44

my output should be

1 23|22|24
2 55|44

Please help in providing query which can run in sybase database.

markp-fuso
  • 28,790
  • 4
  • 16
  • 36
vijay
  • 1
  • 1
  • Is there a maximum number of values for an id? Why version of Sybase are you using? – Gordon Linoff Jun 21 '18 at 11:12
  • Do you want those values in a single column or in three different columns –  Jun 21 '18 at 11:13
  • which Sybase db product (ASE? IQ? SQLAnywhere? Advantage?)? SQLAnywhere/IQ have some pivot capabilities that aren't available in ASE ... so at a minimum we need to know which Sybase product (and version would be good to know, too) – markp-fuso Jun 21 '18 at 16:24
  • @Gordon -- there is maximum vale for an id, for one id there could be 3 records for other id there could be 4 records. – vijay Jun 21 '18 at 18:19
  • @a horse -- values coming single row pipe separated would be preferred else value is single rows with different columns should be a workaround option. – vijay Jun 21 '18 at 18:21
  • @markp we are using sybase ase 15.7 version – vijay Jun 21 '18 at 18:21
  • ASE does not provide support for `list()`, `group_concant()` nor `FOR XML/PATH`, so you'll need to use a looping construct, eg, create a user-defined function that cursors through the `value`s for a given `id` - keep in mind that you can either a) create a function specific to this table/column set or b) create a (more) generic UDF but then the main query's function call may be a bit more convoluted (see [generic UDF](https://stackoverflow.com/a/44184241/7366100)) – markp-fuso Jun 21 '18 at 18:47

1 Answers1

-1

You can do this with row_number(), which should be available in your version of Sybase:

select id,
       stuff((max(case when seqnum = 1 then '|' + value else '' end) +
              max(case when seqnum = 2 then '|' + value else '' end) +
              max(case when seqnum = 3 then '|' + value else '' end) +
              max(case when seqnum = 4 then '|' + value else '' end)
             ), 1, 1, '') as vals
from (select t.*, row_number() over (partition by id order by value) as seqnum
      from t
     ) t
group by id;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786