SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Problem with suppresing name
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

iktheone
Yak Posting Veteran

66 Posts

Posted - 10/05/2005 :  03:07:32  Show Profile  Reply with Quote
Query:-
select
"Employee"=e.empname,
"Dos"=s.dos,
"Amount"=sum(s.salary)
from Empmaster e
JOIN Salary s on
e.empid=s.empid
group by empname,dos with rollup

Result Set:-
Imran 2005-08-01 5000
Imran 2005-09-01 5000
Imran 2005-10-01 7000
Imran NULL 17000
Raja 2005-08-01 5000
Raja 2005-09-01 7000
Raja 2005-10-01 7000
Raja NULL 19000
NULL NULL 36000

I want the result set to be like this. what do i do?
Imran 2005-08-01 5000
2005-09-01 5000
2005-10-01 7000
Total 17000
Raja 2005-08-01 5000
2005-09-01 7000
2005-10-01 7000
Total 19000
Total 36000

Edited by - iktheone on 10/10/2005 09:47:12

madhivanan
Premature Yak Congratulator

India
22744 Posts

Posted - 10/05/2005 :  03:15:40  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Sounds that "Suppress if duplicated"

Where do you want to show these data?
Are you using Reports?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

iktheone
Yak Posting Veteran

66 Posts

Posted - 10/05/2005 :  03:26:14  Show Profile  Reply with Quote
No actually im not using any reports. I just want it to be display on the screen. I tried using select-case,while loop,if statement. but to no avail. im just a begener! so please do something.
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22744 Posts

Posted - 10/05/2005 :  04:52:16  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Which is your Front End Application?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

iktheone
Yak Posting Veteran

66 Posts

Posted - 10/05/2005 :  05:05:03  Show Profile  Reply with Quote
Im not using any front end. Im just using SQL Server 2000. I want the stuff to be displayed in the Query Analyser.

Imran,
"We are not here because we are free, we are here because we are not free."-THE MATRIX.
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22744 Posts

Posted - 10/05/2005 :  05:15:32  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by iktheone

Im not using any front end. Im just using SQL Server 2000. I want the stuff to be displayed in the Query Analyser.

Imran,
"We are not here because we are free, we are here because we are not free."-THE MATRIX.


What is the purpose of showing this in Query Analyser?
The proper way of showing this is to use Reports
What is your exact requirement?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

iktheone
Yak Posting Veteran

66 Posts

Posted - 10/05/2005 :  05:29:45  Show Profile  Reply with Quote
Actually iam a DB Trainee Programmer.Im learning SQL Server. I really dont know reports. This is just training so my Team Leader has asked me to show this query using rollup. What ever i do, its in Query Analyser. These are just examples not a real project or anything. I just want to suppress those names in the "Empname" column and the other columns should be the same. Here are both the tables. u can try that query on these tables and u'll know what i want.

--Table Empmaster:-
create table Empmaster
(
empid int identity(1,1) constraint pkempid primary key clustered,
empname varchar(10),
empsalary numeric
)

insert Empmaster(empname,empsalary)values('Imran',5000)
insert Empmaster(empname,empsalary)values('Raja',5000)
insert Empmaster(empname,empsalary)values('Ram',8000)

--Table Salary:-
create table Salary
(
salid int identity(1,1) constraint pksalid primary key clustered,
empid int constraint fkempid foreign key references Empmaster(empid),
dos varchar(10),
salary numeric
)

insert Salary(empid,dos,salary)values('1','2005-08-01','5000')
insert Salary(empid,dos,salary)values('2','2005-08-01','5000')
insert Salary(empid,dos,salary)values('1','2005-09-01','5000')
insert Salary(empid,dos,salary)values('2','2005-09-01','7000')
insert Salary(empid,dos,salary)values('1','2005-10-01','7000')
insert Salary(empid,dos,salary)values('2','2005-10-01','7000')
Go to Top of Page

jen
Flowing Fount of Yak Knowledge

Sweden
4110 Posts

Posted - 10/05/2005 :  05:34:30  Show Profile  Send jen a Yahoo! Message  Reply with Quote
you really can't "suppress" the column, a resultset will give you
equal dimensioned rows, if row1 has 2 columns, row2 and so forth will also have 2 columns

you can "cheat" if you do separate select statements

HTH

--------------------
keeping it simple...
Go to Top of Page

iktheone
Yak Posting Veteran

66 Posts

Posted - 10/05/2005 :  05:59:31  Show Profile  Reply with Quote
ahmm...can u please give a more detailed explaination. Like, can u tell me how exactly do u "Cheat" using seperate select statements(please give me the query!). Please, cause i spent the past 5 days on this and my team leader will be mad at me if i take any longer.

Imran,

"It is after all purpose that binds us all together. Although only a human mind can invent something as Insipid as love itself, without meaning or purpose as artificial as THE MATRIX itself."-Smith.(THE MATRX)
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22744 Posts

Posted - 10/05/2005 :  06:29:46  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
See if this helps you
http://sqlteam.com/forums/topic.asp?TOPIC_ID=44471

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

iktheone
Yak Posting Veteran

66 Posts

Posted - 10/05/2005 :  07:36:57  Show Profile  Reply with Quote
well i suppose u asked me to try this query given below. but its giving some error. and besides im working with 2 tables(Empmaster and Salary). Im combining and displaying the empname referenced from Empmaster for the given empid and dos(Date of Salary for 3 months) and the salary for the 3 months. where, in the 1st month both Imran and Raja's salary is 5000. In the 2nd month Imran's salary remains 5000 and Raja's salary becomes 7000. In the 3rd month Imran's salary becomes 7000 but Raja's salary remains 7000. I hope u understand the logic. So since im using 2 tables it becomes complicated to select distinct names. My team leader asked me to use some "if" condition where the name is compared with rest of the duplicate names and if it is same print it only once and the check the next name and so on.

This Query is not working:-
-------------------------
SELECT CASE WHEN EXISTS (SELECT * FROM test T2 WHERE T2.no = T1.no AND T2.desc > T1.desc)
THEN NULL
ELSE no
END,
desc
FROM test T1
ORDER BY no, desc
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22744 Posts

Posted - 10/05/2005 :  07:51:05  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
You should tell your team leader that this type of formations should not be done with query

Move the result of your query to other table and write query based on that table

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 10/05/2005 :  08:57:36  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
In SQL Server, you are working with DATA. And that alone. DATA is represented as a series of rows and columns. There will always be an equal number of columns per row -- i.e., a "square" resultset will always be returned from a SELECT. If multiple rows relate to the same "customer ID", then that ID is repeated over and over.

Always remember this -- you should never worry about formatting data or trying to make it "look" right in SQL Server, using any of the server tools ... your only goal is to return the accurate and correct data and not about how it is formatted.

Once the data leaves sql server and a client processes it to display it to the users, THAT is where you start using tools like report designers, ASP, datagrids, and so on, and you focus on presenting that data in a particular manner.

If your team leader doesn't know this, you need a new team leader. Try to explain this to him and/or bring it up in a conversation, and make sure that he is aware of this.

I will repeat myself because this is one of the most important concepts you can understand when working with sql server: Your ONLY goal is to generate accurate DATA within sql server, and you should never worry about presentation at the database layer and certainly not when using tools like "Query Analyzer".

Good luck.
Go to Top of Page

iktheone
Yak Posting Veteran

66 Posts

Posted - 10/05/2005 :  09:11:59  Show Profile  Reply with Quote
thanx, i understand. i'll clarify it with him. thanx for your time and support.

Regards,
Imran.

"Its not who you are that matters, Its what you do that defines you."-Batman Begins
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22744 Posts

Posted - 10/05/2005 :  09:17:33  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Well. I think you understand it now clearly

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 10/05/2005 :  10:57:41  Show Profile  Reply with Quote
Did anyone consider that maybe his Team Leader gave him this exercise as a training tool to learn how to work with result sets, not because it makes sense in the real world? Telling his Team Leader that he shouldn't do this in SQL may not be the best move. It is something that can be done in SQL, and that may be the best place to do it in some real world situations.

However, I'm not going to post an answer, just because it is a homework problem.




CODO ERGO SUM
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 10/05/2005 :  14:39:52  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
quote:
Originally posted by Michael Valentine Jones

Did anyone consider that maybe his Team Leader gave him this exercise as a training tool to learn how to work with result sets, not because it makes sense in the real world? Telling his Team Leader that he shouldn't do this in SQL may not be the best move. It is something that can be done in SQL, and that may be the best place to do it in some real world situations.

However, I'm not going to post an answer, just because it is a homework problem.

CODO ERGO SUM



I pretty much always agree with your points, but definitely not this one. Suppressing duplicates, indenting, and formatting like this should never be done with T-SQL. If a "team lead" wants you to figure out how to format nice looking reports using T-SQL and Query Analyzer, then than team lead doesn't know what the heck he is doing.
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 10/06/2005 :  00:04:09  Show Profile  Reply with Quote
I have to disagree with you on this. To say that formatting should never be done in SQL Server is taking a good rule of thumb, and turning it into an extreme position, especially when you do not know the nature of the applications they are running in that environment.

The real test has to be what is the best way to do the job that you are trying to accomplish, considering factors like computer resources, development cost, available tools, etc.

To give an example where if might be the best way to accomplish the job, a company runs an ETL process that is a scheduled SQL Server job. At the end of this process, several key people need to get a summary showing sales totals by product, with subtotals by department, and then a final total for the whole company. Using a grouping query with a rollup, it is fairly simple to produce the required information with acceptable formatting, and send it to the people that need it with SQL Mail. Why turn something that is simple into a complicated job by introducing a front-end application in this case?

Perhaps the Team Lead knows that he will be called on to do a lot of ad hoc analysis by writing queries in Query Analyzer, and needs him to understand how to use rollup and cube in these queries to show totals and subtotals. I don’t know what the true situation is there, and that is why I would be reluctant to make the following statement: "If a "team lead" wants you to figure out how to format nice looking reports using T-SQL and Query Analyzer, then than team lead doesn't know what the heck he is doing."




quote:
Originally posted by jsmith8858
I pretty much always agree with your points, but definitely not this one. Suppressing duplicates, indenting, and formatting like this should never be done with T-SQL. If a "team lead" wants you to figure out how to format nice looking reports using T-SQL and Query Analyzer, then than team lead doesn't know what the heck he is doing.



CODO ERGO SUM
Go to Top of Page

iktheone
Yak Posting Veteran

66 Posts

Posted - 10/06/2005 :  02:45:58  Show Profile  Reply with Quote
Mr.Michael Valentine, we missed you!

If there is really a way to summarize. Then please let me know. Cause ive been breaking my head on the for the past one week. Now if i dont get this right, i might probably even be fired! Im just a begener. Whatever i learnt of SQL is all by myself. This is just one thing i couldnt figure out.

Imran,

"I know kung-fu!...Well show me!"-Neo,Morpheus(THE MATRIX)
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 10/06/2005 :  07:32:25  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
Micheal -- you have some good points, but rollups and totals have nothing to do with formatting.

Perhaps adding CODE tags to the original post with the requirements might help clarify the situation:


Result Set:-
Imran	2005-08-01	5000
Imran	2005-09-01	5000
Imran	2005-10-01	7000
Imran	NULL	        17000
Raja	2005-08-01	5000
Raja	2005-09-01	7000
Raja	2005-10-01	7000
Raja	NULL	        19000
NULL	NULL	        36000

I want the result set to be like this. 
what do i do?

Imran	2005-08-01	5000
	2005-09-01	5000
	2005-10-01	7000
	Total	        17000
Raja	2005-08-01	5000
	2005-09-01	7000
	2005-10-01	7000
	Total	        19000
	Total	        36000


He has the data he needs, he is trying to make it *look* nicer by indenting and suppressing the repeated group headers. This should be done in T-SQL/Query Analyzer?

I would hope that no one, other than a techie person, is receving emails generated solely by SQL Server.

Edited by - jsmith8858 on 10/06/2005 08:43:34
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22744 Posts

Posted - 10/10/2005 :  09:50:56  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
iktheone,
It seems you edited your question
what did you add in your original post?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000