Concatenate column value in sqlserver 2008 R2

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

Concatenate column value in sqlserver 2008 R2

Author

Topic

pradeepbliss
Starting Member

28 Posts

Posted - 2014-10-07 : 08:10:54

Table Item.ItemCategory:

CategoryID Category BelongsTo

35 Electronics 0

39 Digital Camera 35

40 DSLR Lens 39

41 Canon 40

Expected Output:

CategoryID Category BelongsTo

41 canon->Dslr Lens->Digital camera->Electronics 40

Explanation:

As i need to concatenate column value with dependent belongings just like
if i pass canon categoryid in where conidition,so desired result should be
canon->Dslr Lens->Digital camera->Electronics becuase As 'canon' belongs
to categoryid- 40(DSLR Lens) then 40 belongs to categoryid-39(Digital camera)
then 39 belongs to categoryid-35(Electronics) in single select statement...

pradeepbliss
Starting Member

28 Posts

Posted - 2014-10-07 : 09:43:12

solution:
;with category as
(

SELECT categoryid, category,belongsto, categoryid as cbelogns,
CAST((category) AS VARCHAR(1000)) AS 'Cg'
FROM item.itemcategory
WHERE belongsto=0

UNION ALL

SELECT t.categoryid, t.category,t.belongsto, a.cbelogns,
CAST((a.Cg + '->' + CAST((t.category) AS VARCHAR(1000))) AS VARCHAR(1000)) AS 'Cg'
FROM item.itemcategory AS t
JOIN category AS a
ON t.belongsto = a.categoryid and t.CategoryID in(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,37,38,39,40,41,42,43,45)
)
select * from category where categoryid=43

categoryid category belongsto cbelogns Cg
43 Nixon Bag 42 35 Electronics->Digital Camera->DSLR Lens->Canon ->Canon Bag->Nixon Bag

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources