Post reply

Help with pivot like sql problem

  • September 21, 2009 at 4:42 am

    #141897

    Hi Group

    I have one of those problems that just seems so easy, but has me stumped.

    I have a table called 'Reports' that has the following structure:

    CREATE TABLE [dbo].[Reports](

    [ReportID] [int] IDENTITY(1,1) NOT NULL,

    [ReportName] [nvarchar](425) NOT NULL,

    [ReportArea] [nvarchar](100) NOT NULL

    )

    ...

    populated with the following data:

    INSERT INTO [Reports] (ReportName, ReportArea) Values ('Arrears 1', 'Arrears')

    INSERT INTO [Reports] (ReportName, ReportArea) Values ('Arrears 2', 'Arrears')

    INSERT INTO [Reports] (ReportName, ReportArea) Values ('General 1', 'General')

    INSERT INTO [Reports] (ReportName, ReportArea) Values ('General 2', 'General')

    INSERT INTO [Reports] (ReportName, ReportArea) Values ('Income 1', 'Income')

    so, different reports can be categorised as belonging to a particular report area.

    I want to use this data to generate a menu like table structure, with the following type of output.

    General Arrears Income

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

    General 1 Arrears 1 Income 1

    General 2 Arrears 2

    This data set will be used as input to an SSRS report that will act as the main report list for a logged in user.

    (My real database contains details of which report is available for viewing be a particular user, but for now I just want to try and solve the general case)

    I've been looking at the PIVOT functionality as it conceptually does what I want but I have the following 2 specific problem:

    1. There is NO aggregation here at all.

    2. In reality there will be be more (or less) Report Areas at run time, so I would want different column lists depending on the Report Areas found.

    Any ideas? I'm open to suggestions for different approaches.

    Many thanks in advance for your input.

    Martin

  • September 21, 2009 at 5:16 am

    #1056144

    Sounds like a job for Jeff Modens Dynamic CrossTab[/url].

    Please follow the link and let us know if you get stuck again.


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • September 21, 2009 at 5:32 am

    #1056146

    Lutz.

    Many thanks for the link. I shall take a look and report back ...

    Martin

Viewing 3 posts - 1 through 2 (of 2 total)

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