sql query

CategoryNumber      EmployeeID      CategoryName
10101013                             1      Chargers
1010101310                             1      AC Chargers A/M
1010101311                             1      AC Charger OEM
1010101312                             1      Car Chargers A/M
1010101313                       1      Car Chargers OEM

i have records in a table like above the table strucutre is
categorynumber text
employeeid number
categorynumber text

i want to make report like

main category    subcategory    

how can i write a query for that.

romeiovasuAsked:
Who is Participating?
 
Marcel HopmanConnect With a Mentor Commented:

The solution is "ugly" but if there are no other ways to recognise the categories you can try this:
(The beter solution wil be to split up the CategoryNumber in the database: a Category and a subcategory field)

SELECT	(
	SELECT	CategoryName
	FROM	TableA
	WHERE	CategoryNumber = LEFT(CategoryNumber,7)
	) AS [Main Category]
	CategoryName AS subcategory
FROM	TableA
WHERE	LEN(CategoryNumber) > 7		
 
UNION
 
SELECT	(
	SELECT	CategoryName
	FROM	TableA
	WHERE	CategoryNumber = LEFT(CategoryNumber,4)
	) AS [Main Category]
	CategoryName AS subcategory
FROM	TableA
WHERE	LEN(CategoryNumber) > 4 
AND 	LEN(CategoryNumber) < 7	 

Open in new window

0
 
Marcel HopmanCommented:
Assuming the numbering CategoryNumber is consistant then you could do something like :

SELECT	(
	SELECT	CategoryName
	FROM	TableA
	WHERE	CategoryNumber = LEFT(CategoryNumber,7)
	) AS [Main Category]
	CategoryName AS subcategory
FROM	TableA
WHERE	LEN(CategoryNumber) > 7		

Open in new window

0
 
romeiovasuAuthor Commented:
some of them have only 4 in length what should i do for that.
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
pai_prasadCommented:
how do u define MainCategory as?

I want to make report like
main category    subcategory    
>> yeah ..but can u give sample data for us to understand the problem??

Thanks
Prasad
http://support.microsoft.com/kb/q555375
0
 
Marcel HopmanCommented:
Are there any other "markings" for recognising main Categories?
0
 
romeiovasuAuthor Commented:
no that is the only one.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.