-
Jan 15th, 2020, 10:00 AM
#1
[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.
ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·
-
Jan 15th, 2020, 10:10 AM
#2
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
-
Jan 15th, 2020, 12:17 PM
#3
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.
ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·
-
Jan 15th, 2020, 01:02 PM
#4
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
-
Jan 15th, 2020, 02:17 PM
#5
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.
ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·
-
Jan 16th, 2020, 03:21 AM
#6
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.
ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·
-
Jan 16th, 2020, 11:17 AM
#7
Re: Two Rows in one
Then your way is the best method for that (using the CTE)
Sometimes the Programmer
Sometimes the DBA
Mazz1
-
Jan 17th, 2020, 08:36 AM
#8
ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|