Creating a tree from set of rows

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Creating a tree from set of rows

Author

Topic

kapiltulsan
Starting Member

2 Posts

Posted - 2015-02-09 : 08:41:32

Hi Friend,

I have following 4 rows in a table with field name as ID

ID
--
AA
BB
CC
DD

I require output as

ID NewColumn
-- ----------
AA AA
BB AA~BB
CC AA~BB~CC
DD AA~BB~CC~DD

can anyone suggest anything for getting an output as this

Thanks in advance

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-09 : 08:46:03

What is the relationship between the rows? Is it just the ID in ascending order?

kapiltulsan
Starting Member

2 Posts

Posted - 2015-02-09 : 23:32:13

Hi,

Actual scenario is as following

There are 2 tables
1. Table Name : TRNNUM Field Name : TRNID
2. Table Name : TRNPRD Field Name : TRNID, PRDID

Sample of tables
Table TRNNUM
TRNID
-----
1234
2565
3458

Table TRNPRD
TRNID PRDID
----- -----
1234 AA
1234 BB
1234 CC
2565 CC
2565 EE
2565 FF
2565 HH
3458 AA
3458 BB

Desired output is
TempTable

TRNID PRDID PRDSEQ
----- ----- ------
1234 AA START
1234 BB START:AA
1234 CC START:AA:BB
2565 CC START
2565 EE START:CC
2565 FF START:CC:EE
2565 HH START:CC:EE:FF
3458 AA START
3458 BB START:AA

Can you please suggest something.

Thanks in advance

waterduck
Aged Yak Warrior

982 Posts

Posted - 2015-02-10 : 03:12:06

[code]
declare @TRNNUM table(TRNID int)
insert into @TRNNUM select
1234 union all select
2565 union all select
3458

declare @TRNPRD table(TRNID int, PRDID varchar(2))
insert into @TRNPRD select
1234,'AA' union all select
1234,'BB' union all select
1234,'CC' union all select
2565,'CC' union all select
2565,'EE' union all select
2565,'FF' union all select
2565,'HH' union all select
3458,'AA' union all select
3458,'BB'

;with cte as(
select ROW_NUMBER() over (partition by TRNID order by PRDID) as rn, *
from @TRNPRD
)
select
a.TRNID,
B.PRDID,
'START' + ISNULL((select ':' + cast(PRDID as varchar(2)) from cte c where b.TRNID = c.TRNID and b.rn > c.rn for xml path('')), '')
from @TRNNUM a
join cte b
on a.TRNID = b.TRNID
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources