locked
print serials side by side RRS feed

  • Question

  • Hi all,

    I have a situation like i have 100 serials on an order,if i print the order the serials are printed in one column giving me 2 or 3 pages.

    is it possible to print them side by side?

    any help will be appreciated...

     


    Kind Regards, Warsha
    Tuesday, November 2, 2010 7:05 PM

Answers

  • Try

    ;with cte2 As
    (Select ser_no,seq_no,Row_Number() Over (partition by Seq_No Order By Ser_no) As rn
    From TableName ),
    cteFinal As
    (select (rn - 1) /5 As SortOrder,seq_no,
     STUFF((select ',' + cast(ser_no as varchar(20)) 
    from cte2 c3 Where c3.seq_no = c2.seq_no and (c3.rn - 1)/5 = (cte2.rn - 1)/5 for xml path('')),1,1,'') 
    as CombinedNumber
     from cte2 
     Group By ((rn - 1) / 5),seq_no)
    Select CombinedNumber,seq_no
    From cteFinal
    Order By SortOrder
    
    You need to partition by seq_no as this is your group ID field. And then inside this group use numbering.


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Proposed as answer by SqlRockss Monday, November 8, 2010 6:54 PM
    • Marked as answer by KJian_ Tuesday, November 9, 2010 5:16 AM
    Monday, November 8, 2010 1:53 PM

All replies

  • You can concatenate them into one line, or, say, 10 serials concatenated per record. If so, check this thread Relevant MSDN thread
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Tuesday, November 2, 2010 7:12 PM
  • Thank you!
    Kind Regards, Warsha
    Tuesday, November 2, 2010 7:27 PM
  • Printing and other display formatting is always done in the front end and never in the database.  This is the definition of a tiered architecture. 

     


    --CELKO-- Please post DDL, use ISO-11179 naming rules and format code so we can read it
    Tuesday, November 2, 2010 7:31 PM
  • hi,

    i have used the code mentioned in the link provided by Naom. however the code doesn't group, i have changed the way i thought it would be right but still it doesn't group.

    here is the data: 

    serial seq_no
    200 1
    201 1
    202 1
    203 1
    204 1
    205 1
    206 1
    300 2
    301 2

    desired result is

    number seq_no
    200,201,202,203,204 1
    205,206 1
    300,301 2

    but i get

    number seq_no
    200,201,202,203,204 1
    205,206,300,301 1
    205,206,300,301 2

    here is the code i used:

    With cte As
    (Select 1 as dummy, c.ser_no, b.seq_no
    From  c,  as b
     where  c.tkt_no = b.tkt_no and c.seq_no = b.seq_no and c.tkt_no = '28090'),
     cte2 As
    (Select ser_no,seq_no,Row_Number() Over (Order By dummy) As rn
    From cte ),
    cteFinal As
    (select (rn - 1) /5 As SortOrder,seq_no,
     STUFF((select ',' + cast(ser_no as varchar(20))
    from cte2 c3 Where (c3.rn - 1)/5 = (cte2.rn - 1)/5 for xml path('')),1,1,'')
    as CombinedNumber
     from cte2
     Group By ((rn - 1) / 5),seq_no)
    Select CombinedNumber,seq_no
    From cteFinal
    Order By SortOrder;

    I want 5 serials in one row. i have tried using the seq_no as the dummy (select b.seq_no as dummy) that also doesn't give me the desired result. How can i edit the code that give me my desired result?

    i will really appreciate any help. 


    Kind Regards, Warsha
    Monday, November 8, 2010 1:28 PM
  • Try

    ;with cte2 As
    (Select ser_no,seq_no,Row_Number() Over (partition by Seq_No Order By Ser_no) As rn
    From TableName ),
    cteFinal As
    (select (rn - 1) /5 As SortOrder,seq_no,
     STUFF((select ',' + cast(ser_no as varchar(20)) 
    from cte2 c3 Where c3.seq_no = c2.seq_no and (c3.rn - 1)/5 = (cte2.rn - 1)/5 for xml path('')),1,1,'') 
    as CombinedNumber
     from cte2 
     Group By ((rn - 1) / 5),seq_no)
    Select CombinedNumber,seq_no
    From cteFinal
    Order By SortOrder
    
    You need to partition by seq_no as this is your group ID field. And then inside this group use numbering.


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Proposed as answer by SqlRockss Monday, November 8, 2010 6:54 PM
    • Marked as answer by KJian_ Tuesday, November 9, 2010 5:16 AM
    Monday, November 8, 2010 1:53 PM
  • thank you Naom,it worked.


    Kind Regards, Warsha
    Monday, November 8, 2010 6:51 PM