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.
| Author |
Topic |
|
ganny
Yak Posting Veteran
51 Posts |
 Posted - 2009-10-14 : 05:29:04
|
| Hi,I would like to compress the values to show only once in the table. For example the table is like this:TicketNo SeatNo Amount============================AA1 11 100AA1 12 100BB2 22 200BB2 23 200BB2 25 200CC3 41 150CC3 44 150The expected output of the table should be:TicketNo SeatNo Amount============================AA1 11 100AA1 12 -BB2 22 200BB2 23 -BB2 25 -CC3 41 150CC3 44 -Kindly assist me. |
|
|
YellowBug
Aged Yak Warrior
616 Posts |
Posted - 2009-10-14 : 08:25:07
|
| This can easily be done in the presentation layer. Most reporting tools have an identical suppress option.Do you really require this in SQL? |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2009-10-14 : 08:31:41
|
quote:
Originally posted by ganny
Hi,I would like to compress the values to show only once in the table. For example the table is like this:TicketNo SeatNo Amount============================AA1 11 100AA1 12 100BB2 22 200BB2 23 200BB2 25 200CC3 41 150CC3 44 150The expected output of the table should be:TicketNo SeatNo Amount============================AA1 11 100AA1 12 -BB2 22 200BB2 23 -BB2 25 -CC3 41 150CC3 44 -Kindly assist me.
Where do you want to show data?MadhivananFailing to plan is Planning to fail |
|
|
|
ganny
Yak Posting Veteran
51 Posts |
Posted - 2009-10-15 : 01:05:45
|
| Hi, i would like to show in sql since the other application based on this result.Pls help if any posibilities. |
|
|
|
Nageswar9
Aged Yak Warrior
600 Posts |
Posted - 2009-10-15 : 01:20:51
|
| Hi Try this once.SELECT TicketNo,SeatNo,CASE WHEN rid = 1 THEN Amount ELSE NULL END AS 'Amount' FROM ( SELECT *,ROW_NUMBER() OVER( PARTITION BY TicketNo ORDER BY TicketNo ) AS rid FROM @temp) t |
|
|
|
|
|
|
compress the values..