Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jetana
Starting Member
1 Post |
 Posted - 2007-04-07 : 12:34:23
|
| Howdy,don't know where to start; it sounds trivial but i need some tip/hint on this one. it might need some dynamic sql with ref cursors but I'm not sure about the simpliest way...this is the case:i got this+++++++++++|Parent| Child|+++++++++++| 0600 |0700|| 0600 |0701|| 0600 |0702|+++++++++++and i like to transpose this to a table/query, looking like this.+++++++++++++++++++++++++|Parent | Child1 | Child2 | Child3|+++++++++++++++++++++++++| 0600 | 0700 | 0701 | 0702|+++++++++++++++++++++++++knowing the fact that I don't know how many children each parent has.any idea's? it will be highly appriciated...thanks a lot. |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-04-07 : 23:09:11
|
[code]declare @table table( id int, Parent varchar(10), Child varchar(10))insert into @table(Parent, Child)select '0600', '0700' union allselect '0600', '0701' union allselect '0600', '0702' union allselect '0800', '0900' union allselect '0800', '0901'update tset id = (select count(*) from @table x where x.Parent = t.Parent and x.Child <= t.Child)from @table tselect Parent, Child1 = max(case when id = 1 then Child end), Child2 = max(case when id = 2 then Child end), Child3 = max(case when id = 3 then Child end)from @table tgroup by Parent[/code]
KH |
 |
|
|
|
|
|
|
|
pivot query