Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Odd row and even Row side by side. Expand / Collapse
Author
Message
Posted Wednesday, January 22, 2014 6:20 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 23, 2014 6:14 PM
Points: 3, Visits: 2
Would like to ask is there any method that can be used to show the odd row and even row side by side.

Eg:-Original result
RowNumber name dQty Price
---------------------------------------------------------
1 LCE4D10TBH18W24CS6 8 0
2 LCE4D10TBH24W24CS6 29 0
3 LCE4D10TBH36W24CS6 24 0
4 LCE4D10TBH36W36CS6 14 0
5 LCE9D10TBH06W36CS6 8 0

What i want to get is
RowNumber name dQty Price RowNumber name dQty Price
-----------------------------------------------------------------------------------------------------------------------
1 LCE4D10TBH18W24CS6 8 0 2 LCE4D10TBH24W24CS6 29 0
3 LCE4D10TBH36W24CS6 24 0 4 LCE4D10TBH36W36CS6 14 0
5 LCE9D10TBH06W36CS6 8 0
need help on how i can get these result?
Post #1533890
Posted Wednesday, January 22, 2014 6:36 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 6:17 AM
Points: 1,816, Visits: 5,913
Do you need to do this in a SQL query or is it destined for a report ? SSRS/Crystal/Excel?

MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1533896
    Posted Thursday, January 23, 2014 1:44 AM
    Forum Newbie

    Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

    Group: General Forum Members
    Last Login: Thursday, January 23, 2014 6:14 PM
    Points: 3, Visits: 2
    this result i need to present out at C1ReportDesigner
    Post #1533933
    Posted Thursday, January 23, 2014 2:33 AM


    SSCommitted

    SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

    Group: General Forum Members
    Last Login: Yesterday @ 6:17 AM
    Points: 1,816, Visits: 5,913
    thomaslee2020 (1/23/2014)
    this result i need to present out at C1ReportDesigner


    I don't know the details of their report designer, but assuming it can generate a matrix style report like SSRS, I usually create a new column in my dataset called display_column and one called display_row.

    The definition for each would be something like

    display_column = 1 - (row_number() over(order by myKey) % 2) -- this gives a "zero" for odd rows and a "one" for even rows
    display_row = row_number() over(order by myKey) / 2 -- gives row numbers that contain two real data rows per number

    the result of which is to get data like this

    display_row display_column
    1 0
    1 1
    2 0
    2 1
    ...


    This data can then be placed on a matrix style report with a row group on display_row and a column group on display_column.




    MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1533944
    Posted Thursday, January 23, 2014 2:41 AM


    SSCertifiable

    SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

    Group: General Forum Members
    Last Login: Today @ 4:28 AM
    Points: 5,488, Visits: 10,339
    Fairly simple in T-SQL, as well:

    SELECT
    a.RowNumber
    , a.name
    , a.dQty
    , a.Price
    , b.RowNumber
    , b.name
    , b.dQty
    , b.Price
    FROM
    MyTable a
    LEFT JOIN
    MyTable b ON a.RowNUmber + 1 = b.RowNumber and a.RowNumber%2 = 1

    John
    Post #1533945
    Posted Thursday, January 23, 2014 6:17 PM
    Forum Newbie

    Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

    Group: General Forum Members
    Last Login: Thursday, January 23, 2014 6:14 PM
    Points: 3, Visits: 2
    Seems easy and workable... i shall try out on this... hope it really gives me what i wanted... thanks in advance(if works)...

    John Mitchell-245523 (1/23/2014)
    Fairly simple in T-SQL, as well:

    SELECT
    a.RowNumber
    , a.name
    , a.dQty
    , a.Price
    , b.RowNumber
    , b.name
    , b.dQty
    , b.Price
    FROM
    MyTable a
    LEFT JOIN
    MyTable b ON a.RowNUmber + 1 = b.RowNumber and a.RowNumber%2 = 1

    John
    Post #1534311
    « Prev Topic | Next Topic »

    Add to briefcase

    Permissions Expand / Collapse