Problem with suppresing name - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Problem with suppresing name

Author

Topic

iktheone
Yak Posting Veteran

66 Posts

Posted - 2005-10-05 : 03:07:32

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-05 : 03:15:40

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

iktheone
Yak Posting Veteran

66 Posts

Posted - 2005-10-05 : 03:26:14

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.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-05 : 04:52:16

Which is your Front End Application?

Madhivanan

Failing to plan is Planning to fail

iktheone
Yak Posting Veteran

66 Posts

Posted - 2005-10-05 : 05:05:03

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.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-05 : 05:15:32

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

iktheone
Yak Posting Veteran

66 Posts

Posted - 2005-10-05 : 05:29:45

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')

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-10-05 : 05:34:30

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...

iktheone
Yak Posting Veteran

66 Posts

Posted - 2005-10-05 : 05:59:31

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)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-05 : 06:29:46

See if this helps you
http://sqlteam.com/forums/topic.asp?TOPIC_ID=44471

Madhivanan

Failing to plan is Planning to fail

iktheone
Yak Posting Veteran

66 Posts

Posted - 2005-10-05 : 07:36:57

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-05 : 07:51:05

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

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-10-05 : 08:57:36

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.

iktheone
Yak Posting Veteran

66 Posts

Posted - 2005-10-05 : 09:11:59

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-05 : 09:17:33

Well. I think you understand it now clearly

Madhivanan

Failing to plan is Planning to fail

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-10-05 : 10:57:41

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

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-10-05 : 14:39:52

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.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-10-06 : 00:04:09

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

iktheone
Yak Posting Veteran

66 Posts

Posted - 2005-10-06 : 02:45:58

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)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-10-06 : 07:32:25

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.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-10 : 09:50:56

iktheone,
It seems you edited your question
what did you add in your original post?

Madhivanan

Failing to plan is Planning to fail

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources