CODEWITHSUNDEEP
sqlsybase
Svip
Svip

Reputation: 3128

How to combine the values of the same field from several rows into one string in a one-to-many select?

Imagine the following two tables:

create table MainTable (
  MainId integer not null, -- This is the index
  Data varchar(100) not null
)

create table OtherTable (
  MainId integer not null,    -- MainId, Name combined are the index.
  Name varchar(100) not null,
  Status tinyint not null
)

Now I want to select all the rows from MainTable, while combining all the rows that match each MainId from OtherTable into a single field in the result set.

Imagine the data:

MainTable:
1, 'Hi'
2, 'What'

OtherTable:
1, 'Fish', 1
1, 'Horse', 0
2, 'Fish', 0

I want a result set like this:

MainId, Data,   Others
1,      'Hi',   'Fish=1,Horse=0'
2,      'What', 'Fish=0'

What is the most elegant way to do this?

(Don't worry about the comma being in front or at the end of the resulting string.)

Upvotes: 0

Views: 268

Answers (2)

Svip
Svip

Reputation: 3128

Well, here is how I implemented it in Sybase 13.x. This code has the advantage of not being limited to a number of Names.

create proc
as
  declare
    @MainId int,
    @Name   varchar(100),
    @Status tinyint

  create table #OtherTable (
    MainId     int          not null,
    CombStatus varchar(250) not null
  )

  declare OtherCursor cursor for
    select
        MainId, Name, Status
      from
        Others

  open OtherCursor
    fetch OtherCursor into @MainId, @Name, @Status
    while (@@sqlstatus = 0) begin -- run until there are no more
      if exists (select 1 from #OtherTable where MainId = @MainId) begin
        update #OtherTable
          set CombStatus = CombStatus + ','+@Name+'='+convert(varchar, Status)
          where
            MainId = @MainId
      end else begin
        insert into #OtherTable (MainId, CombStatus)
          select
            MainId = @MainId,
            CombStatus = @Name+'='+convert(varchar, Status)
      end

      fetch OtherCursor into @MainId, @Name, @Status
    end
  close OtherCursor

  select
      mt.MainId,
      mt.Data,
      ot.CombStatus
    from
      MainTable mt
        left join #OtherTable ot
          on mt.MainId = ot.MainId

But it does have the disadvantage of using a cursor and a working table, which can - at least with a lot of data - make the whole process slow.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269633

There is no really elegant way to do this in Sybase. Here is one method, though:

select 
  mt.MainId, 
  mt.Data,
  Others = stuff((
             max(case when seqnum = 1 then ','+Name+'='+cast(status as varchar(255)) else '' end) +
             max(case when seqnum = 2 then ','+Name+'='+cast(status as varchar(255)) else '' end) +
             max(case when seqnum = 3 then ','+Name+'='+cast(status as varchar(255)) else '' end)
           ), 1, 1, '')
from MainTable mt 
  left outer join
    (select 
       ot.*, 
       row_number() over (partition by MainId order by status desc) as seqnum
     from OtherTable ot
    ) ot
    on mt.MainId = ot.MainId
group by
  mt.MainId, md.Data

That is, it enumerates the values in the second table. It then does conditional aggregation to get each value, using the stuff() function to handle the extra comma. The above works for the first three values. If you want more, then you need to add more clauses.

Upvotes: 1

Related Questions