Solved

sql query

Posted on 2007-11-15
6
174 Views
Last Modified: 2010-03-19
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.

0
Comment
Question by:romeiovasu
  • 3
  • 2
6 Comments
 
LVL 6

Expert Comment

by:Marcel Hopman
Comment Utility
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
 

Author Comment

by:romeiovasu
Comment Utility
some of them have only 4 in length what should i do for that.
0
 
LVL 10

Expert Comment

by:pai_prasad
Comment Utility
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 6

Expert Comment

by:Marcel Hopman
Comment Utility
Are there any other "markings" for recognising main Categories?
0
 

Author Comment

by:romeiovasu
Comment Utility
no that is the only one.
0
 
LVL 6

Accepted Solution

by:
Marcel Hopman earned 500 total points
Comment Utility

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

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now