Pivot with Self Join??? 1

[thermidor]

All:

In SQL Server 2000, how can I query the data below to get the parent and all the children on one row with the children in order from left to right based on the values in the Pctage column?

Table:
Parent Child Pctage
--------------- -------------- --------
50985 35794 42.431
50985 35923 37.268
50985 35999 20.301
50986 39573 40.372
50986 35493 9.254
50986 91299 50.374


Desired result:
Parent ChildA ChildB ChildC
--------------- ------------- ------------- -------------
50985 35794 53923 35999
50986 91299 39573 35493

TIA,
Sven

 

[SQLDenis]

here we go

dll and dml

create table Blah (Parent     int,     Child  int,        Pctage decimal(5,3))


insert blah values(50985,           35794 ,         42.431)
insert blah values(50985,           35923 ,         37.268)
insert blah values(50985,           35999 ,         20.301)
insert blah values(50986,           39573 ,         40.372)
insert blah values(50986,           35493 ,         9.254)
insert blah values(50986,           91299 ,         50.374)

select statement

select t1.parent,
t1.child as ChildA,
t2.child as ChildB,
t3.child as ChildC
from blah t1 
join blah t2 on t1.parent =t2.parent
join blah t3 on t3.parent =t2.parent
and t3.parent =t1.parent
and t1.Pctage > t2.Pctage
and t2.Pctage > t3.Pctage
and t1.Pctage > t3.Pctage

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/