Geek Answers Handbook

Selecting and joining rows in a table in an SQL stored procedure

There is a temporary table with the scheme: ID | SeqNo | Name

ID - Not unique
SeqNo - Int (may be 1.2 or 3). Type ID + SeqNo as the primary key
Name - any text

And example data in a table like this

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

You need to choose from this table and display how

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

You need to do this without a cursor.

0
source share
3 answers

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, you can expand the 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;
+3
source

If you know that 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 a general solution for an arbitrary number of elements:

 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 UR CLR.

Edit: had the wrong alias in the correlated table!

Edit2: another version based on Remus recursion example. I could not think of any way to select only the last recursion on Id without aggregation or sorting. Somebody knows?

 ;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;
+3
source

The decision is good. I have a similar problem, but here I use two different tables. eg:

 table1 1 | 1 | 2 | 3 | 3 | 1 | 4 | 2 | 5 | 1 | 1 | 2 | 1 | 3 | 4 | 1 | 5 | 2 | 2 | 2 | 4 | 3 | table2 1 | RecordA 2 | RecordB 3 | RecordC

I want to get data from two tables and display them in the following format.

 1 | RecordA,RecordB,RecordC| 2 | RecordB,RecordC| 3 | RecordA | 4 | RecordA,RecordB,RecordC| 5 | RecordA,RecordB |
0
source

More articles:


All Articles