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 |
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] |
 |
|
|
|
|