views:

230

answers:

2

Have a temp table with schema: ID | SeqNo | Name

ID - Not unique
SeqNo - Int (can be 1,2 or 3). Sort of ID+SeqNo as Primary key
Name - Any text

And sample data in the table like this

1 | 1 | RecordA  
2 | 1 | RecordB  
3 | 1 | RecordC  
1 | 2 | RecordD  
4 | 1 | RecordE  
5 | 1 | RecordF  
3 | 1 | RecordG

Need to select from this table and output like

1 | RecordA/RecordD  
2 | RecordB  
3 | RecordC/RecordG  
4 | RecordE  
5 | RecordF

Need to do this without cursor.

+2  A: 

If you know SeqNo will never be more than 3:

select Id, Names = stuff(
    max(case when SeqNo = 1 then '/'+Name else '' end)
  + max(case when SeqNo = 2 then '/'+Name else '' end)
  + max(case when SeqNo = 3 then '/'+Name else '' end)
  , 1, 1, '')
from table1 
group by Id

Otherwise, something like this is the generic solution to an arbitrary number of items:

select Id, Names = stuff((
  select '/'+Name from table1 b
  where a.Id = b.Id order by SeqNo
  for xml path (''))
  , 1, 1, '')
from table1 a
group by Id

Or write a CLR UDA.

Edit: had the wrong alias on the correlated table!

Edit2: another version, based on Remus's recursion example. I couldn't think of any way to select only the last recursion per Id, without aggregation or sorting. Anybody know?

;with
  myTable as (
    select * from (
      values 
        (1, 1, 'RecordA')  
      , (2, 1, 'RecordB')  
      , (3, 1, 'RecordC')  
      , (1, 2, 'RecordD')  
      , (4, 1, 'RecordE')  
      , (5, 1, 'RecordF')  
      , (3, 2, 'RecordG')
      ) a (Id, SeqNo, Name)
    )    
, anchor as (
    select id, name = convert(varchar(max),name), seqno
    from myTable where seqno=1
    )
, recursive as (
    select id, name, seqno
    from anchor
    union all
    select t.id, r.name + '/' + t.name, t.seqno
    from myTable t
    join recursive  r on t.id = r.id and r.seqno+1 = t.seqno
    )
select id, name = max(name) 
from recursive
group by id;
---- without aggregation, we get 7 rows:
--select id, name
--from recursive;
Peter
+1 Like the second example (works with 1,2 changed to 1,1)
Andomar
Thanks for the edit!
Peter
+2  A: 

If SeqNo is limited to 1,2,3:

select id, a.name + coalesce('/'+b.name, '') + coalesce('/'+c.name, '')
from myTable a
left outer join myTable b on a.id=b.id and b.seqno = 2
left outer join myTable c on a.id=c.id and c.seqno = 3
where a.seqno = 1;

If SeqNo is open ended you can deploy a recursive cte:

;with anchor as (
   select id, name, seqno
      from myTable
      where seqno=1)
, recursive as (
   select id, name, seqno
      from anchor
      union all
   select t.id, r.name + '/' + t.name, t.seqno
      from myTable t
      join recursive  r on t.id = r.id and r.seqno+1 = t.seqno)
select id, name from recursive;
Remus Rusanu
@Remus, version 1 includes 2 self-joins. You can accomplish the same thing w/out joins using MAX(CASE ...) GROUP BY. For version 2, you need to somehow filter recursive for the last seqno row per id, or MAX(name) GROUP BY id.
Peter
2 self-joins = three reads, and I doubt the clustered index starts on seqno, so that means three table scans, vs at most one table scan for MAX(CASE ...) GROUP BY.
Peter
@PeteR: I like your solution actually more than mine and you got my upvote, but don't jump to conclusions about performance. A solution with three parallel scans and hash join may work faster than an intermediate spool for aggregates, the optimizer works in mysterious ways. As for the recursive filter, I think the join condition on the recursive part covers that (r.seqno+1 = t.seqno).
Remus Rusanu
re: parallelization vs intermediate spool: I would not have thought of this. Will keep it in mind, thanks. re: recursion: in the final SELECT (return to client), you will still have all values for seqno, not just the terminal one. The last time I tried accumulation via recursion, I couldn't find a simple way to identify it, without aggregation or sorting. Any ideas?
Peter
+1 Needed a comma before "recursive" :)
Andomar
@Peter: Gotch ya. The query needs to return only the leaf rows of the recursive tree, not the intermediate branches. I'll think about it.
Remus Rusanu