How to write query for this concept? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2012 Forums

Transact-SQL (2012)

How to write query for this concept?

Author

Topic

MuraliPilla
Starting Member

1 Post

Posted - 2014-12-26 : 06:36:03

Hi All,

I have a Table Like This

CatId     Name             ParentCatId
1          Automobile                0
2          Communications         0
3           Bikes                       1
4           Cars                      1
5          Vodafone                   2

Here I am storing ParentIds also in same table
now i need the following output

CatId         Name                ParentCategory
1              Automobile              Parent
2              Communications       Parent
3              Bikes                      Automobile
4              Cars                       Automobile
5              Vodafone               Communications

How can i write the query to get above output?

Thanks,
Murali.

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2014-12-26 : 07:19:36

this will give the required output,but this is not the complete solution
select catid,
name,
case when p.parentcatid=0 then 'Parent'
when parentcatid=1 then (select name from product where catid=1)
when parentcatid=2 then (select name from product where catid=2)
when parentcatid=3 then (select name from product where catid=3) end as parentcat from product p

Javeed Ahmed
https://www.linkedin.com/pub/javeed-ahmed/25/5b/95

MuralikrishnaVeera
Posting Yak Master

129 Posts

Posted - 2014-12-26 : 07:24:42

may also done without sub query but i gave my try


CREATE TABLE #temp(CatId  int,Name varchar(1024),ParentCatId int)
INSERT INTO #temp
SELECT 1,'Automobile',0 UNION ALL
SELECT 2 ,'Communications',0 UNION ALL
SELECT 3 ,'Bikes',1 UNION ALL
SELECT 4 ,'Cars',1 UNION ALL
SELECT 5 ,'Vodafone',2

SELECT    t1.catId
		 ,t1.name
		 ,CASE WHEN t1.ParentCatid = 0 THEN 'Parent' ELSE (SELECT name FROM #temp WHERE catID = t1.ParentCatId)
		  END    AS Parent 
FROM	  #temp  AS t1
LEFT JOIN #temp  AS t2
ON        t1.Catid = t2.Catid


DROP TABLE dbo.#temp

---------------
Murali Krishna

You live only once ..If you do it right once is enough.......

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources