Need Help on This Problem

Question

Need Help on This Problem

dilip.aim11

Mr or Mrs. 500

Points: 574
More actions
April 26, 2012 at 7:42 am

#259983

Hello
I have One table called Test_Sales_Report
CREATE TABLE [dbo].[Test_Sales_Report](
[Sales] [varchar](50) NULL,
[Amount] [decimal](18, 2) NULL,
[Till] [int] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[Test_Sales_Report] ([Sales], [Amount], [Till]) VALUES (N'Sales1', CAST(200.00 AS Decimal(18, 2)), 1)
INSERT [dbo].[Test_Sales_Report] ([Sales], [Amount], [Till]) VALUES (N'Sales2', CAST(300.00 AS Decimal(18, 2)), 1)
INSERT [dbo].[Test_Sales_Report] ([Sales], [Amount], [Till]) VALUES (N'sales3', CAST(44.00 AS Decimal(18, 2)), 2)
INSERT [dbo].[Test_Sales_Report] ([Sales], [Amount], [Till]) VALUES (N'sales4', CAST(55.00 AS Decimal(18, 2)), 1)
INSERT [dbo].[Test_Sales_Report] ([Sales], [Amount], [Till]) VALUES (N'sales5', CAST(77.00 AS Decimal(18, 2)), 3)
INSERT [dbo].[Test_Sales_Report] ([Sales], [Amount], [Till]) VALUES (N'sales6', CAST(99.00 AS Decimal(18, 2)), 2)
how can i show data in this formate...
Sales Till1 Till2 Till3
Sales1 200 0 0
Sales2 300
sales3 0 44
sales4
sales5 77..
.
.
.
.................................
One approach i can do this in my Front end ..
i want to know can i do this from sql server ...
Please suggest me...

Viewing 6 posts - 1 through 5 (of 5 total)

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

Ant-Green SSC Guru Points: 113507 More actions · Answer 1

personally you should do this at the application layer

if you want or have no other choice than to do it at the SQL layer, take a look at cross-tabs or the pivot command, the links in my signature block will help

Mark Cowne One Orange Chip Points: 26954 More actions · Answer 2

SELECT Sales,

SUM(CASE WHEN Till=1 THEN Amount ELSE 0 END) AS Till1,

SUM(CASE WHEN Till=2 THEN Amount ELSE 0 END) AS Till2,

SUM(CASE WHEN Till=3 THEN Amount ELSE 0 END) AS Till3

FROM Test_Sales_Report

GROUP BY Sales

ORDER BY Sales;

If you have a variable number of Tills then you will need to use dynamic SQL.

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

dilip.aim11 Mr or Mrs. 500 Points: 574 More actions · Answer 3

hi yes i have variable number of till..

which i am getting in variable ... using

count statement from other table...

Mark Cowne One Orange Chip Points: 26954 More actions · Answer 4

dilip.aim11 (4/26/2012)
hi yes i have variable number of till..
which i am getting in variable ... using
count statement from other table...

Have a look at dynamic cross tabs here

http://www.sqlservercentral.com/articles/Crosstab/65048/

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

GSquared SSC Guru Points: 260824 More actions · Answer 5

Can your presentation layer cope with a dataset that returns different columns each time it's run? That's usually pretty complex to set up.

The easier way to do this kind of thing is use a front-end that has pivot capabilities. Like Excel, or Reporting Services, or Crystal Reports. Any of those can do pivoting very, very easily.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon