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: 1004356 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)

Jeff Moden SSC Guru Points: 1004356 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)

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: 1004356 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)

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

CustomerRegionQuarterSales

123ON11000

12322000

12331500

12343000

222BC15000

I got the above output.

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

karthik

Jeff Moden SSC Guru Points: 1004356 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)

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