Displaying the fields only once

Question

Post reply

Displaying the fields only once

Meera

SSCommitted

Points: 1966
More actions
July 3, 2008 at 4:38 am
Go to Answer
#184281

Hi There!
i have a query like this,
Select Distinct Table1.name, Table1.Code, Table2.Place, Table2.Marks From Table1 inner join Table2 on Table1.code = Table2.code
which results,
Name1/N1/Nagpur/114
Name1/N1/Nagpur/154
Name1/N1/Nagpur/123
i want it to display as
Name1/N1/Nagpur/114
/154
/123
is it possible ? how to achieve it?
Regds,
Dhana
Jeff Moden

SSC Guru

Points: 1004365
More actions
July 3, 2008 at 10:25 pm
Answer
#838203

Vijaya Kadiyala (7/3/2008)
hi,
You can't implement this logic in DB side
I agree that formatting for display should, indeed, be done in the GUI.... BUT, gosh, you really need to be sure before you say things like that. Not only can it be implemented on the DB side, it is particularly easy to do so in SQL Server 2005 or any RDBMS that has RANK or ROW_NUMBER available... and in a set based manner, to boot! 😉
Here's the classic "Blue Car" example written in SQL Server 2005 T-SQL...
--===== Supress the auto-display of rowcounts for appearance
SET NOCOUNT ON
--===== Setup the test data... not part of the solution
DECLARE @data TABLE (PersonID VARCHAR(10), CarID VARCHAR(10))
INSERT INTO @data (PersonID, CarID)
SELECT 'Person1','BlueCar' UNION ALL
SELECT 'Person1','GreenCar' UNION ALL
SELECT 'Person1','YellowCar' UNION ALL
SELECT 'Person2','RedCar' UNION ALL
SELECT 'Person2','BlueCar'
;WITH
cteDisplayControl AS
(
SELECT RANK() OVER (PARTITION BY PersonID ORDER BY PersonID,CarID) AS DisplayKey,
PersonID, CarID
FROM @data
)
SELECT CASE DisplayKey
WHEN 1 THEN PersonID
ELSE ''
END AS PersonID,
CarID
FROM cteDisplayControl
Output from above code...
[font="Courier New"]PersonID CarID
---------- ----------
Person1 BlueCar
GreenCar
YellowCar
Person2 BlueCar
RedCar[/font]
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

steveb. SSC-Forever Points: 46733 More actions · Answer 1

This is something that should probably be left up to your front-end application.

MannySingh SSChampion Points: 12465 More actions · Answer 2

Can u post your Table(s) Structure here, with sample inserts

Maninder
www.dbanation.com

Vijaya Kadiyala SSCrazy Eights Points: 9119 More actions · Answer 3

hi,

You can't implement this logic in DB side, it would be easier to have this logic in Front-end application.

Thanks -- Vj

http://dotnetvj.blogspot.com

Thanks -- Vijaya Kadiyala
www.dotnetvj.com
SQL Server Articles For Beginers

RBarryYoung SSC Guru Points: 143329 More actions · Answer 4

Well, actually, you can do it on the DB side, but it is a lot more trouble than it's worth.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 5

New Commer (7/3/2008)
Hi There!
i have a query like this,
Select Distinct Table1.name, Table1.Code, Table2.Place, Table2.Marks From Table1 inner join Table2 on Table1.code = Table2.code
which results,
Name1/N1/Nagpur/114
Name1/N1/Nagpur/154
Name1/N1/Nagpur/123
i want it to display as
Name1/N1/Nagpur/114
/154
/123
is it possible ? how to achieve it?
Regds,
Dhana

Dhana...

Take a look at the "Blue Car" example code I posted above... this is an easy thing to do. Do take heed of the warnings that I and many of the others have posted. Formatting should be done in the GUI because it does take extra clock cycles... the fewer clock cycles you make the server do, the better it can handle "real business". 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Meera SSCommitted Points: 1966 More actions · Answer 6

Thts Very great let me try out ur query. Thks for ur Reply!

Perry Whittle SSC Guru Points: 234013 More actions · Answer 7

New Commer (7/3/2008)
Hi There!
i have a query like this,
Select Distinct Table1.name, Table1.Code, Table2.Place, Table2.Marks From Table1 inner join Table2 on Table1.code = Table2.code
which results,
Name1/N1/Nagpur/114
Name1/N1/Nagpur/154
Name1/N1/Nagpur/123
i want it to display as
Name1/N1/Nagpur/114
/154
/123
is it possible ? how to achieve it?
Regds,
Dhana

the select distinct is displaying the data correctly as each row is unique. The ssuggestion by Jeff seems your best way using RANK and CTE's

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉