stored procedure - parse string of id's and select

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

SQL Server 2005 Forums

Transact-SQL (2005)

stored procedure - parse string of id's and select

Author

Topic

cwfontan
Yak Posting Veteran

87 Posts

Posted - 2009-11-02 : 13:18:35

is there a way to parse a string of ID's seperated by "-" and get the description for each id and return in a new column? from a stored procedure



table1

id  |  depotID  |  custID  |  moveSequence  
1      586           203        224-1481-224 
2      586           203        224-1685-224
3      586           198        224-1481-386-224
4      586           789        224-369-898-224


table2
id   |  description
224      myAddress224
369      myAddress369
386      myAddress386
898      myAddress898
1481     myAddress1481
1685     myAddress1685


stpred procedure return 

id  |  depotID  |  custID  |  moveSequence      |  loc1       |     loc2       |     loc3        |        loc4
1      586           203        224-1481-224       myAddress224     myAddress1481     myAddress224
2      586           203        224-1685-224       myAddress224     myAddress1685     myAddress224
3      586           198        224-1481-386-224   myAddress224     myAddress1481     myAddress386   myAddress224

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-11-02 : 13:53:30

http://www.sqlteam.com/article/parsing-csv-values-into-multiple-rows

Jim

Everyday I learn something that somebody else already knew

cwfontan
Yak Posting Veteran

87 Posts

Posted - 2009-11-02 : 14:41:48

is there a way to split it into columns?

cwfontan
Yak Posting Veteran

87 Posts

Posted - 2009-11-02 : 15:54:48

from what I researched i could do some sort of dynamic sql to see how many rows for a sepecific ID and use pivot and create columns etc.

any help on that?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-11-02 : 16:50:19

You're on the right track. This code assumes you have a function in place (something like jimf's post) that returns (rowid, val)


declare @table1 table (id int, depotID int, custID int, moveSequence  varchar(200))
insert @table1
select 1,      586,           203,        '224-1481-224'
union all select 2,      586,           203,        '224-1685-224'
union all select 3,      586,           198,        '224-1481-386-224'
union all select 4,      586,           789,        '224-369-898-224'

declare @table2 table (id int, description varchar(20))
insert @table2
select 224,      'myAddress224'
union all select 369,      'myAddress369'
union all select 386,      'myAddress386'
union all select 898,      'myAddress898'
union all select 1481,     'myAddress1481'
union all select 1685,    'myAddress1685'

select id
       ,depotid
       ,custid
       ,moveSequence
       ,max([1]) as loc1
       ,max([2]) as loc2
       ,max([3]) as loc3
       ,max([4]) as loc4
from   (
       select t1.id
              ,t1.depotid
              ,t1.custid
              ,t1.moveSequence
              ,ca.rowid
              ,ca.val as moves
              ,t2.description
       from   @table1 t1
       cross apply dbo.fn_ParseStr(moveSequence, '-') ca
       left outer join @table2 t2
              on  t2.id = ca.val
       ) d
pivot (
       max(description) for rowid in ([1],[2],[3],[4])
) p
group by id
       ,depotid
       ,custid
       ,moveSequence

output:
id          depotid     custid      moveSequence         loc1                 loc2                 loc3                 loc4
----------- ----------- ----------- ----------------------------------------- -------------------- -------------------- --------------------
1           586         203         224-1481-224         myAddress224         myAddress1481        myAddress224         NULL
2           586         203         224-1685-224         myAddress224         myAddress1685        myAddress224         NULL
3           586         198         224-1481-386-224     myAddress224         myAddress1481        myAddress386         myAddress224
4           586         789         224-369-898-224      myAddress224         myAddress369         myAddress898         myAddress224

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources