answer plz - 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

answer plz

Author

Topic

raky
Aged Yak Warrior

767 Posts

Posted - 2008-08-21 : 06:39:07

hi,

My sp returns the following output

Empid empfname emplastname salary
1 james bond 60000
2 james charles 50000
3 prince charles 79000

i want output like this

empid empfname emplastname salary
1 james bond 60000
2 <emptyspace> charles 50000
3 prince charles 79000

if firstname of some records are equal then i need to display remaining records first name with empty space.

Thanks in advance..

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-21 : 06:40:42

Check "Suppress duplicate records" in your report application.

E 12°55'05.25"
N 56°04'39.16"

raky
Aged Yak Warrior

767 Posts

Posted - 2008-08-21 : 06:45:06

quote:
Originally posted by Peso

Check "Suppress duplicate records" in your report application.

E 12°55'05.25"
N 56°04'39.16"

is there any way to achieve this from database side i.e, from backend

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-21 : 06:45:56

If its sql reporting service then option is called Hide Duplicates. you can get it by right clicking on cell where you display repeated data and clicking checkbox for Hide Duplicates. Also select the dataset name from dropdown box in bottom.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-21 : 06:46:35

quote:
Originally posted by raky

quote:
Originally posted by Peso

Check "Suppress duplicate records" in your report application.

E 12°55'05.25"
N 56°04'39.16"

is there any way to achieve this from database side i.e, from backend

this is purely a presentation issue and has to dealt with in your front end application.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-21 : 07:12:21

If you use Crystal Reports make use of it's Suppress if duplicated feature

Madhivanan

Failing to plan is Planning to fail

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-08-21 : 11:50:05

If you are using SQL 2005, you might be able to make use of the ROW_NUMBER() function and convert the rows that are not equal to one to a blank. For Example:

DECLARE @Yak TABLE (Empid INT, empfname VARCHAR(20), emplastname VARCHAR(20), salary MONEY)
INSERT @Yak
SELECT 1, 'james', 'bond', 60000
UNION ALL SELECT 2, 'james', 'charles', 50000
UNION ALL SELECT 3, 'prince', 'charles', 79000


SELECT
	EmpID,
	CASE
		WHEN RowNum = 1 THEN EmpFName
		ELSE ''
	END AS EmpFName,
	EmpLastName,
	Salary
FROM
(
	SELECT
		*,
		ROW_NUMBER() OVER (PARTITION BY empfname ORDER BY empfname) AS RowNum
	FROM @Yak
) AS Temp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources