asked on

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.

Marcel Hopman

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

romeiovasu

ASKER

some of them have only 4 in length what should i do for that.

pai_prasad

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

Marcel Hopman

Are there any other "markings" for recognising main Categories?

romeiovasu

ASKER

no that is the only one.

ASKER CERTIFIED SOLUTION

Marcel Hopman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial