locked
Format Parent child record to show one-to-many and display parent record only once RRS feed

  • Question

  • Here is my desired format to display .....

    1            Rule 1               detail 1

                    detail 2

                    detail 3

                    detail 4

    2            Rule 2               detail 1

        detail 2

        detail 3

    Here is the script to create test data. 

    create table myrule 
    (ruleid int,
    [desc] [varchar](20) COLLATE Latin1_General_CI_AI 
    )

    create table myruledetail 
    (ruleid int,
    [desc] [varchar](20) COLLATE Latin1_General_CI_AI 
    )
    insert into myrule (ruleid, [desc]) values(1, ' Rule 1')
    insert into myruledetail (ruleid, [desc]) values(1, ' detail 1')
    insert into myruledetail (ruleid, [desc]) values(1, ' detail 2')
    insert into myruledetail (ruleid, [desc]) values(1, ' detail 3')
    insert into myruledetail (ruleid, [desc]) values(1, ' detail 4')

    insert into myrule (ruleid, [desc]) values(2, ' Rule 2')
    insert into myruledetail (ruleid, [desc]) values(2, ' detail 1')
    insert into myruledetail (ruleid, [desc]) values(2, ' detail 2')
    insert into myruledetail (ruleid, [desc]) values(2, ' detail 3')

    If I use query below, I get extra parent record which I want to suppress

    select myrule.ruleid, myrule.[desc], myruledetail.[desc] from myrule
    join myruledetail on myruledetail.ruleid= myrule.ruleid


    Thursday, April 12, 2012 6:23 PM

Answers

  • I'd prefer do it in UI too. But certainly one can do it in SQL. Change formatting as needed

    select
    	case rn when 1 then cast(ruleid as varchar(5)) else '' end ruleid
    	,case rn when 1 then rdesc else '' end ruledescr
    	,[desc]
    from 
     (select myrule.ruleid, myrule.[desc] as rdesc, myruledetail.[desc]
     , ROW_NUMBER() over (partition by myrule.ruleid order by myruledetail.[desc]) rn
     from myrule
     join myruledetail on myruledetail.ruleid= myrule.ruleid) x 
     order by x.ruleid, rn


    Serg

    Thursday, April 12, 2012 6:57 PM

All replies

  • This needs to be done in UI or report. You can do this in Crystal Report or SSRS. 

    Best Wishes, Arbi; Please vote if you find this posting was helpful or Mark it as answered.

    • Proposed as answer by Chuck Pedretti Thursday, April 12, 2012 7:12 PM
    Thursday, April 12, 2012 6:30 PM
  • I'd prefer do it in UI too. But certainly one can do it in SQL. Change formatting as needed

    select
    	case rn when 1 then cast(ruleid as varchar(5)) else '' end ruleid
    	,case rn when 1 then rdesc else '' end ruledescr
    	,[desc]
    from 
     (select myrule.ruleid, myrule.[desc] as rdesc, myruledetail.[desc]
     , ROW_NUMBER() over (partition by myrule.ruleid order by myruledetail.[desc]) rn
     from myrule
     join myruledetail on myruledetail.ruleid= myrule.ruleid) x 
     order by x.ruleid, rn


    Serg

    Thursday, April 12, 2012 6:57 PM
  • Thank you Serg. 

    I wanted to avoid UI changes for this one. But many thanks for your solution. 

    Thursday, April 12, 2012 7:31 PM