This requirement is still is design phase an we are can change design of a table.

actual requirement is to retrieve data for multiple portfolio for reporting purpose.

After adding following records

SELECT 'P2',NULL,'P4' UNION ALL

SELECT 'P4','T6',NULL

if i ask for portfolio P1's hierarchy

O/P should looks something like this

PortFolio Trade ChildPortfolio ChildTrade

P1 T1 NULL NULL

P1 T4 NULL NULL

P1 NULL P2 T2

P1 NULL P2 T3

P1 NULL P3 T5

P2 NULL P4 T6

Thanks

this helped

sorry you are correct.

Thanks

ddl looks good

O/P i am looks at is all dates when flag changes it's value

so in case of

id = 1 dates are

3/1/2013 and 4/1/2013

and for id 2 date is...

I used tried both way ony with telly table workedout grate perfomance wise.

Thanks once again fro your help

Thanks all for you help

this worked out grate for me

Yes id did help resolve my issue

Once again thanks for your help.

Thanks Jacob

This works for me

Thanks for your Help

This is not Homwork.

I I have similar Business rqeuirement . and to get answer from foram readers I just simplify my xml.

My original XML is really complex...

I am running this query

declare @Filt XML='<Filter>

<List>

<Node1>100</Node1>

<Node2>10</Node2>

</List>

<List>

<Node1>200</Node1>

<Node2>

<Val>1</Val>

<Val>2</Val>

<Val>3</Val>

</Node2>

</List>

</Filter>'

SELECT

x.i.value('Node1[1]', 'varchar(1000)') AS Node1,

x.i.value('Node2[1]', 'varchar(1000)') AS Node2

FROM @Filt.nodes('Filter/*') x(i)

Whish gives me o/p as

Node1 Node2

100 10

200 123

and I am...

Thanks

That helpd me

yes My o/p is same as this

I want o/p something like this

dt ...

yes thats what I my O/P is

I wat o/p as

dt ...

DENSE_RANK () is not gives me desire result