Hiding repeating data

Question

Post reply

Hiding repeating data

sqlgreenhand

SSCommitted

Points: 1639
More actions
May 26, 2008 at 7:15 pm

#76995

Is it possible to hide data showing when running queries ?

Like this

Customer Region Quarter (07) Sales$

123 ON 1 1000

123 ON 2 2000

123 ON 3 1500

123 ON 4 3000

222 BC 1 5000

instead I prefer to have like this

Customer Region Quarter (07) Sales$

123 ON 1 1000

2 2000

3 1500

4 3000

222 BC 1 5000

Thanks for any ideas and I know something could be done under

Reporting Services. (but I would know if any luck at query level)

Viewing 12 posts - 1 through 11 (of 11 total)

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

Can it be done? Yes.

Should it be done? No.

This is a presentation problem and it should be handled by the presentation layer, NOT the data manipulation layer.

[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: 1000539 More actions · Answer 2

... providing that there is a presentation layer... 😉

--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)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

Francis... you already have the hardpart done... if the Quarter = 1 then show the column, otherwise show a blank. Simple case statement should do it.

--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)
Intro to Tally Tables and Functions

RBarryYoung SSC Guru Points: 143329 More actions · Answer 4

Jeff Moden (5/26/2008)

... providing that there is a presentation layer... 😉

Heh. Well, unless they're displaying with SSMS, there's *something* doing the presentation, otherwise, why try to format it?

[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: 1000539 More actions · Answer 5

No SSMS in 2k... no reporting service in 2k. Could be, it's a simple text report created by a scheduled job... makes a "great" presentation layer for those that don't actually have one.

--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)
Intro to Tally Tables and Functions

sqlgreenhand SSCommitted Points: 1639 More actions · Answer 6

sqlgreenhand

SSCommitted

Points: 1639

May 27, 2008 at 8:53 am

#820590

Thanks for all the suggestions and advices.

karthik M One Orange Chip Points: 29139 More actions · Answer 7

mmm... I do agree with Jeff and rbarryyoung , we can do it in presentation Layer.

But we can also do it in Data Layer. Here is the code,

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

create table #test

(

Customer int,

Region char(2),

Quarter int,

Sales int

)

Insert into #test

select 123,'ON',1,1000

union all

select 123,'ON',2,2000

Union all

Select 123,'ON',3,1500

union all

select 123,'ON',4,3000

Union all

Select 222,'BC',1,5000

Select Customer, Region = case when Quarter = 1 then Region else Null End ,Quarter,Sales from #test

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

karthik

karthik M One Orange Chip Points: 29139 More actions · Answer 8

karthik M

One Orange Chip

Points: 29139

May 28, 2008 at 4:03 am

#821055

Customer Region Quarter Sales

123 ON 1 1000

123 2 2000

123 3 1500

123 4 3000

222 BC 1 5000

I got the above output.

Jeff and rbarryyoung , kindly add your comments, if my approach is wrong.

karthik

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 9

Heh... no, the approach is not wrong... it's exactly what I suggested earlier. 😉

--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)
Intro to Tally Tables and Functions

karthik M One Orange Chip Points: 29139 More actions · Answer 10

karthik M

One Orange Chip

Points: 29139

May 28, 2008 at 6:45 am

#821135

ok.

karthik

johnsql-193053 SSCommitted Points: 1657 More actions · Answer 11



declare @t table(Customer int, Region varchar(2), Quarter tinyint, Sales money)

declare @t2 table(Customer int, Region varchar(2), Quarter tinyint, Sales money)

insert @t

select 		 123  	 ,     'ON'    	 ,    1      ,        1000

union all select 123     ,     'ON'      ,    2      ,        2000

union all select 123     ,     'ON'      ,    3      ,        1500

union all select 222     ,     'BC'      ,    1      ,        5000

union all select 123     ,     'ON'      ,    4      ,        3000

insert @t2

select * from @t

order by Customer, Quarter

select  nCustomer= case when b.customer is null then convert(varchar(50),a.customer) else '' end

	, nRegion = case when b.region is null then convert(varchar(50),a.region) else '' end

	, a.quarter, a.sales

from @t2 a

left join @t2 b on a.customer=b.customer and b.quarter=a.quarter-1