Results 1 to 8 of 8

Thread: [RESOLVED] Two Rows in one

  1. #1

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    Resolved [RESOLVED] Two Rows in one

    Hello.
    I'm trying to bring data from 2 rows of the same table to one row.
    Table Rows are:
    Code:
    HEUSERDEFTEXT01	HEBKEPYOVAL	HEBKEPYOVATVAL	HEBTOTALVAL	HEDOCCODE	HEDCSRTYPE	HEISPRINTED
    40	        0.0000000	0.0000000	10.0000000	ΠΕΙΣΠ-0000000011	3	    0
    40	        8.0600000	1.9400000	10.0000000	ΑΠΥ-0000000010	        0	    1
    What I'm trying is to bring some data from first row and some from second, combined into a single Row.

    What I'm doing is the below. I want to know if this is an acceptable solution.

    Code:
    ;WITH
        FAMILY_CTE as
        (
           select H1.HEUSERDEFTEXT01,H1.HEBKEPYOVAL,H1.HEBKEPYOVATVAL,H1.HEBTOTALVAL,H1.HEDOCCODE,H1.HEDCSRTYPE,H1.HEISPRINTED
    
      FROM HEDOCENTRIES H1 inner join HEDOCENTRIES H2 on H1.HEUSERDEFTEXT01 = H2.HEUSERDEFTEXT01
    
      where H2.HEUSERDEFTEXT01 =40 and H2.HEISPRINTED = 1
      group by H1.HEUSERDEFTEXT01,H1.HEBKEPYOVAL,H1.HEBKEPYOVATVAL,H1.HEBTOTALVAL,H1.HEDOCCODE,H1.HEDCSRTYPE,H1.HEISPRINTED
        )
        SELECT 
               a.HEUSERDEFTEXT01, a.HEBTOTALVAL,a.HEBKEPYOVAL, a.HEBKEPYOVATVAL
               ,b.HEDOCCODE,b.HEDCSRTYPE,b.HEISPRINTED
              
        FROM FAMILY_CTE a
        JOIN FAMILY_CTE b on a.HEUSERDEFTEXT01 = b.HEUSERDEFTEXT01 and a.HEDCSRTYPE != b.HEDCSRTYPE
    	and b.HEDCSRTYPE = 3 and b.HEISPRINTED = 0
    Thant brings correct data I guess:

    Code:
    HEUSERDEFTEXT01	HEBTOTALVAL	HEBKEPYOVAL	HEBKEPYOVATVAL	HEDOCCODE	HEDCSRTYPE	HEISPRINTED
    40	        10.0000000	8.0600000	1.9400000	ΠΕΙΣΠ-0000000011	3	0
    Thanks.
    Last edited by sapator; Jan 15th, 2020 at 10:04 AM.
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  2. #2
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,495

    Re: Two Rows in one

    You can also combine all the data into one column if needed (well by that I mean the data you want in one column). Is this SQL Server?
    If so you can use a stuff command with a query with for XML (if before 2017)

    like this:
    Code:
    SELECT 
       SS.SEC_NAME,
       STUFF((SELECT '; ' + US.USR_NAME 
              FROM USRS US
              WHERE US.SEC_ID = SS.SEC_ID
              ORDER BY USR_NAME
              FOR XML PATH('')), 1, 1, '') [SECTORS/USERS]
    FROM SALES_SECTORS SS
    GROUP BY SS.SEC_ID, SS.SEC_NAME
    ORDER BY 1
    Last edited by GaryMazzone; Jan 15th, 2020 at 10:13 AM.
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  3. #3

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    Re: Two Rows in one

    Thank I'll give it a go tomorrow.
    However I must point out that whenever we used FOR XML we saw performance decreasing rapidly on large table.
    Currently I only have a few hundred rows so I can't compare but the table is expected to have 50.000 rows per year.
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  4. #4
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,495

    Re: Two Rows in one

    I've used it on some table with over 60,000,000 and been OK. just very sparingly
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  5. #5

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    Re: Two Rows in one

    Maybe it depends on the data.
    I can't recall all the places we used it but I'm sure that I have an sp that expands cinema rows off screens and the execution plan shows these icon next to the For XML itteration
    With that said, the current query only expands 2 xtra rows so I guess it won't matter. I'll take a look.

    Thanks.
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  6. #6

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    Re: Two Rows in one

    Ahm, maybe I'm a bit rusty on For XML thing but it only puts the data in one column, like so: 40<HEISPRINTED>0</HEISPRINTED><HEBKEPYOVATVAL>0.0000000</HEBKEPYOVATVAL> etc

    I want them in different columns.
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  7. #7
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,495

    Re: Two Rows in one

    Then your way is the best method for that (using the CTE)
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  8. #8

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    Re: Two Rows in one

    Alright good to know.
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width