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

PortFolioTradeChildPortfolioChildTrade

P1T1NULLNULL

P1T4NULLNULL

P1NULLP2T2

P1NULLP2T3

P1NULLP3T5

P2NULLP4T6

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

Node1Node2

10010

200123

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