Post reply

Multi Level Grouping

  • January 18, 2011 at 12:47 am

    #233421

    The recordset in question is the folllowing:

    IF OBJECT_ID('TempDB..#mytesttable','U') IS NOT NULL

    DROP TABLE #mytesttable

    CREATE TABLE #mytesttable

    (

    Rennerid INT,

    Jaarid varchar(6),

    Achternaam varchar(40),

    Koersid INT,

    Koersnaam varchar(40),

    Klasse varchar(40),

    Annee INT,

    Eindklassering varchar(70),

    Code varchar(30)

    )

    /*SET IDENTITY_INSERT #mytesttable ON*/

    INSERT INTO #mytesttable

    (Rennerid, Jaarid, Achternaam, Koersid, Koersnaam, Klasse, Annee, Eindklassering, Code)

    SELECT '1000', '196999', 'Zoetemelk', '65', 'De Ronde van Luxemburg', 'Kleine Ronden', '1970', '4e De Ronde van Luxemburg', 'Klassement' UNION ALL

    SELECT '1000', '196999', 'Zoetemelk', '71', 'Parijs-Nice', 'Etappekoersen', '1970', '18e Parijs-Nice', 'Klassement' UNION ALL

    SELECT '1000', '196999', 'Zoetemelk', '73', 'Parijs-Luxemburg', 'Etappekoersen', '1970', '15e Parijs-Luxemburg', 'Klassement' UNION ALL

    SELECT '1000', '196999', 'Zoetemelk', '76', 'Criterium du Dauphiné Libéré', 'Etappekoersen', '1970', '7e Criterium du Dauphiné Libéré', 'Klassement' UNION ALL

    SELECT '1000', '197000', 'Zoetemelk', '55', 'Tour de France', 'Grote Ronden', '1970', '2e Tour de France', 'Klassement' UNION ALL

    SELECT '1000', '197002', 'Zoetemelk', '10', 'Rund Um Den Henninger Turm', 'Klassiekers', '1970', '2e Rund Um Den Henninger Turm', 'Klassement' UNION ALL

    SELECT '1000', '197011', 'Zoetemelk', '5', 'Omloop Het Volk', 'Klassiekers', '1970', '11e Omloop Het Volk', 'Klassement' UNION ALL

    SELECT '1000', '197011', 'Zoetemelk', '73', 'Parijs-Luxemburg', 'Etappekoersen', '1970', '1e in de 2Be Etappe van de Parijs-Luxemburg', 'Etappe Etappekoers' UNION ALL

    SELECT '1000', '197023', 'Zoetemelk', '11', 'Het Kampioenschap van Zürich', 'Klassiekers', '1970', '23e Het Kampioenschap van Zürich', 'Klassement' UNION ALL

    SELECT '1000', '197024', 'Zoetemelk', '7', 'Parijs-Tours', 'Klassiekers', '1970', '24e Parijs-Tours', 'Klassement' UNION ALL

    SELECT '1000', '197099', 'Zoetemelk', '65', 'De Ronde van Luxemburg', 'Kleine Ronden', '1971', '4e De Ronde van Luxemburg', 'Klassement' UNION ALL

    SELECT '1000', '197100', 'Zoetemelk', '55', 'Tour de France', 'Grote Ronden', '1971', '2e Tour de France', 'Klassement' UNION ALL

    SELECT '1000', '197100', 'Zoetemelk', '57', 'Vuelta a Espana', 'Grote Ronden', '1971', '6e Vuelta a Espana', 'Klassement' UNION ALL

    SELECT '1000', '197102', 'Zoetemelk', '85', 'Grand Prix des Nations', 'Tijdritten', '1971', '2e Grand Prix des Nations', 'Klassement' UNION ALL

    SELECT '1000', '197104', 'Zoetemelk', '9', 'De Waalse Pijl', 'Klassiekers', '1971', '4e De Waalse Pijl', 'Klassement' UNION ALL

    SELECT '1000', '197111', 'Zoetemelk', '8', 'De Ronde van Lombardije', 'Klassiekers', '1971', '11e De Ronde van Lombardije', 'Klassement' UNION ALL

    SELECT '1000', '197115', 'Zoetemelk', '65', 'De Ronde van Luxemburg', 'Kleine Ronden', '1971', '1e in de 4Be Etappe van de De Ronde van Luxemburg', 'Etappe Kleine Ronde' UNION ALL

    SELECT '1000', '197118', 'Zoetemelk', '57', 'Vuelta a Espana', 'Grote Ronden', '1971', '1e in de 16e etappe Vuelta', 'Etappe Vuelta' UNION ALL

    SELECT '1000', '197121', 'Zoetemelk', '4', 'Luik-Bastenaken-Luik', 'Klassiekers', '1971', '21e Luik-Bastenaken-Luik', 'Klassement' UNION ALL

    SELECT '1000', '197121', 'Zoetemelk', '14', 'Het Wereldkampioenschap', 'Wereldkampioenschap', '1971', '21e Het Wereldkampioenschap', 'Klassement' UNION ALL

    SELECT '1000', '197124', 'Zoetemelk', '1', 'Parijs-Roubaix', 'Klassiekers', '1971', '24e Parijs-Roubaix', 'Klassement' UNION ALL

    SELECT '1000', '197199', 'Zoetemelk', '71', 'Parijs-Nice', 'Etappekoersen', '1972', '10e Parijs-Nice', 'Klassement' UNION ALL

    SELECT '1000', '197199', 'Zoetemelk', '76', 'Criterium du Dauphiné Libéré', 'Etappekoersen', '1972', '20e Criterium du Dauphiné Libéré', 'Klassement' UNION ALL

    SELECT '1000', '197200', 'Zoetemelk', '55', 'Tour de France', 'Grote Ronden', '1972', '5e Tour de France', 'Klassement' UNION ALL

    SELECT '1000', '197202', 'Zoetemelk', '85', 'Grand Prix des Nations', 'Tijdritten', '1972', '2e Grand Prix des Nations', 'Klassement' UNION ALL

    SELECT '1000', '197203', 'Zoetemelk', '15', 'De Amstel Gold Race', 'Klassiekers', '1972', '3e De Amstel Gold Race', 'Klassement' UNION ALL

    SELECT '1000', '197205', 'Zoetemelk', '14', 'Het Wereldkampioenschap', 'Wereldkampioenschap', '1972', '5e Het Wereldkampioenschap', 'Klassement' UNION ALL

    SELECT '1000', '197206', 'Zoetemelk', '8', 'De Ronde van Lombardije', 'Klassiekers', '1972', '6e De Ronde van Lombardije', 'Klassement' UNION ALL

    SELECT '1000', '197211', 'Zoetemelk', '9', 'De Waalse Pijl', 'Klassiekers', '1972', '11e De Waalse Pijl', 'Klassement' UNION ALL

    SELECT '1000', '197215', 'Zoetemelk', '2', 'De Ronde van Vlaanderen', 'Klassiekers', '1972', '15e De Ronde van Vlaanderen', 'Klassement' UNION ALL

    SELECT '1000', '197218', 'Zoetemelk', '4', 'Luik-Bastenaken-Luik', 'Klassiekers', '1972', '18e Luik-Bastenaken-Luik', 'Klassement' UNION ALL

    SELECT '1000', '197221', 'Zoetemelk', '11', 'Het Kampioenschap van Zürich', 'Klassiekers', '1972', '21e Het Kampioenschap van Zürich', 'Klassement' UNION ALL

    SELECT '1000', '197299', 'Zoetemelk', '71', 'Parijs-Nice', 'Etappekoersen', '1973', '2e Parijs-Nice', 'Klassement' UNION ALL

    SELECT '1000', '197299', 'Zoetemelk', '76', 'Criterium du Dauphiné Libéré', 'Etappekoersen', '1973', '3e Criterium du Dauphiné Libéré', 'Klassement' UNION ALL

    SELECT '1000', '197300', 'Zoetemelk', '55', 'Tour de France', 'Grote Ronden', '1973', '1e in de 0e etappe Tour', 'Etappe Tour' UNION ALL

    SELECT '1000', '197300', 'Zoetemelk', '55', 'Tour de France', 'Grote Ronden', '1973', '4e Tour de France', 'Klassement' UNION ALL

    SELECT '1000', '197303', 'Zoetemelk', '85', 'Grand Prix des Nations', 'Tijdritten', '1973', '3e Grand Prix des Nations', 'Klassement' UNION ALL

    SELECT '1000', '197304', 'Zoetemelk', '15', 'De Amstel Gold Race', 'Klassiekers', '1973', '4e De Amstel Gold Race', 'Klassement' UNION ALL

    SELECT '1000', '197305', 'Zoetemelk', '2', 'De Ronde van Vlaanderen', 'Klassiekers', '1973', '5e De Ronde van Vlaanderen', 'Klassement' UNION ALL

    SELECT '1000', '197305', 'Zoetemelk', '14', 'Het Wereldkampioenschap', 'Wereldkampioenschap', '1973', '5e Het Wereldkampioenschap', 'Klassement' UNION ALL

    SELECT '1000', '197306', 'Zoetemelk', '55', 'Tour de France', 'Grote Ronden', '1973', '1e in de 4e etappe Tour', 'Etappe Tour' UNION ALL

    SELECT '1000', '197309', 'Zoetemelk', '4', 'Luik-Bastenaken-Luik', 'Klassiekers', '1973', '9e Luik-Bastenaken-Luik', 'Klassement' UNION ALL

    SELECT '1000', '197317', 'Zoetemelk', '71', 'Parijs-Nice', 'Etappekoersen', '1973', '1e in de 7Be Etappe van de Parijs-Nice', 'Etappe Etappekoers' UNION ALL

    SELECT '1000', '197330', 'Zoetemelk', '76', 'Criterium du Dauphiné Libéré', 'Etappekoersen', '1973', '1e in de 2Be Etappe van de Criterium du Dauphiné Libéré', 'Etappe Etappekoers' UNION ALL

    SELECT '1000', '197399', 'Zoetemelk', '71', 'Parijs-Nice', 'Etappekoersen', '1974', '1e Parijs-Nice', 'Klassement'

    The output should be the following:

    Zoetemelk (grouping level 1)

    1970 (grouping level 2)

    Grote Ronden (grouping level 3)

    1e 13e Etappe Tour de France (data)

    1e 15e Etappe Tour de France

    2e Tour de France

    Etappekoersen (grouping level 3)

    1e 1e Etappe Parijs-Nice

    1e 5r Etappe Parijs-Nice

    1971 (grouping level 2)

    etc.

    Is this an impossible question, let me know. Further investigation makes no sense then. In all other cases, help would be great.

    Thanks,

    Robert

    12e Parijs-Nice

  • January 18, 2011 at 12:52 am

    #1274505

    Robert,

    That looks like a reporting format you're trying to output. Are you attempting to do this in SSRS or via a T-SQL query? (Psst, SSRS will be easier. 🙂 )

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

  • January 18, 2011 at 12:55 am

    #1274506

    I'm using a T-SQL query. I'm not familiar with SSRS. Can you explain that a bit further?

  • January 18, 2011 at 1:14 am

    #1274515

    r_slot (1/18/2011)

    I'm using a T-SQL query. I'm not familiar with SSRS. Can you explain that a bit further?

    SQL Server Reporting Services. Think Crystal Reports or Access Reports.

    Basically, you're looking to do 'heading grouping'. T-SQL doesn't do this well, if at all. The reason for this is because T-SQL tends to table/denormalize all information without trying to do a mainframe data flow.

    The results of this query wouldn't be directly consumable by most products either, and especially not anything else in the database. You will most likely want to look into dumping this to something else to make it pretty.

    If you have MS Access available, I would recommend hooking a front end up to the SQL tables, and dumping the results to a report there as a starting point. You'll see how headers can be used to group information and get it sorted out. From there, it's not a much further reach to go to SSRS. The reason I recommend going to Access to get your feet wet is to avoid dealing with publishing, service rights, and other possible items you'll need to get familiar with simultaneously.

    There is no simple method in T-SQL to perform the task you want, however, and you're looking at writing some very intricate set/loop iterative code to get the results to that format.

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

  • January 18, 2011 at 1:20 am

    #1274519

    Thanks Craig for your answer. I have a front-end Access-2007 and I know what the report possibilities are. I was just wondering if things could be done in sql server. Your answer is cristal clear: no.

    With respect to the reporting services: I am using SQL Server 2005 EE - is there a reporting module available or is that only for the full-blown version of SQL Server 2005? If there is, perhaps an technical article could explain the ins and outs?

    Robert.

  • January 18, 2011 at 1:25 am

    #1274521

    r_slot (1/18/2011)

    Thanks Craig for your answer. I have a front-end Access-2007 and I know what the report possibilities are. I was just wondering if things could be done in sql server. Your answer is cristal clear: no.

    With respect to the reporting services: I am using SQL Server 2005 EE - is there a reporting module available or is that only for the full-blown version of SQL Server 2005? If there is, perhaps an technical article could explain the ins and outs?

    Robert.

    Heh, sorry I couldn't give you a happier answer there.

    EE: Enterprise Edition or Express Edition?

    I'm assuming Express since you mention the full-blown version. 😉

    Two quick articles I found regarding SQL Express and Reporting Services. Unfortunately, I don't know the details to your answer in this regard for the Express version, but these should answer it:

    http://technet.microsoft.com/en-us/library/cc281020.aspx

    http://technet.microsoft.com/en-us/library/ms365166.aspx

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

  • January 18, 2011 at 1:30 am

    #1274524

    You were right: express edition. The articles are looking promising.

    Thanks for your help.

    Robert

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

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