Need Help on This Problem

  • 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...

  • 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

  • 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
  • hi yes i have variable number of till..

    which i am getting in variable ... using

    count statement from other table...

  • 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
  • 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

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

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