How can I merge 8 records in two records?
-
Hello everbody,
I have a view with data that I want to merge with a SQL statement.
1161870 NULL NULL NULL n.v.t. 1
1161870 NULL NULL NULL n.v.t. 1
1161870 NULL NULL 116 NULL 1
1161870 NULL NULL 305 NULL 1
1161870 NULL AMEV NULL NULL 1
1161870 NULL ING NULL NULL 1
1161870 5352 NULL NULL NULL 1
1161870 6074 NULL NULL NULL 1
I want to build a query that gives the following result:
1161870 5352 AMEV 116 n.v.t. 1
1161870 6074 ING 305 n.v.t. 1
Can anybody please help me?
-
It'll work in this case but I'm not sure it'll solve your problem :
Select min(col1) as C1, min(col2) as C2, min(col3) as C3, min(col4) AS C4, min(Col5) AS C5, min(Col6) AS C6 from db.YourView
UNION ALL
Select max(col1) as C1, max(col2) as C2, max(col3) as C3, max(col4) AS C4, max(Col5) AS C5, max(Col6) AS C6 from db.YourView
-
Thanks for your answer, but your right. It doesn't solve my
problem, because there are also ocurrences where there are
three or more records that I have to merge... (min and max
are then not sufficiënt). Do you know an other method?
Greetings,
Kenneth
-
Can you show me the query that produces those results?
Or can you repair the data?
-
The query is as follows:
select DISTINCT m.ponr PolisNr
, CASE WHEN veldc in (2, 6, 10, 14, 18, 22)
THEN vartkst
END PolisNrOud
, CASE WHEN veldc in (3, 7, 11, 15, 19, 23)
THEN vartkst
END Maatschappij
, CASE WHEN veldc in (4, 8, 12, 16, 20, 24)
THEN vartkst
END Bedrag
, CASE WHEN veldc in (5, 9, 13, 17, 21, 25)
THEN vartkst
END Overdrachtsdatum
, 1 IsNieuw
into #Clausule120
from phh030 m
, phh001 h
where m.cls = 120
and m.ponr = h.a1_001_PoNr
and h.a1_076_actc = 1
and h.a1s_00_soort = 'H'
and NOT vartkst IS NULL
and EXISTS
(select s.ponr
from phh030 s
where s.ponr = m.ponr
and s.cls = m.cls
and s.veldc = 63
 
order by m.ponr
-
Why are you splitting into 4 groups in (2, 6, 10, 14, 18, 22)?
-
Because they made one field 'vartkst' for different columns. I find out that
e.g. veldc 2, 6, 10, 14, 18, 22 is for the column that i'll call PolisNr. This is also
the case for the other columns (i.e. PolisNrOud, Maatschappij, Bedrag, etc.)
Thanks for helping me!
-
So you basically have fields like email1, email2, email3 and you're trying to normalize the data?
-
Assuming that "1161870" is an ID value,
SELECT
P.ponr,
P.vartkst AS PolisNrOud,
M.vartkst AS Maatschappij,
B.vartkst AS Bedrag,
O.vartkst AS Overdrachtsdatum
FROM
(SELECT ponr, vartkst FROM phh030 WHERE (veldc+2)%4 = 0) P
JOIN (SELECT ponr, vartkst FROM phh030 WHERE (veldc+3)%4 = 0) M
ON P.ponr = M.ponr
JOIN (SELECT ponr, vartkst FROM phh030 WHERE (veldc)%4 = 0) B
ON M.ponr = B.ponr
JOIN (SELECT ponr, vartkst FROM phh030 WHERE (veldc+1)%4 = 0) O
ON B.ponr = O.ponr
I use modulo rather than IN but i think this may do what you require
-
The problem is not the in clause, it's the result it produces.
-
Yes. My results are from a policysystem and I'm trying to merge the rows with
the same policynumbers (first column).
I wil show you a print screen put in excel:
I hope thiw will clarify my question!
-
You have to put the jpeg on a web server, or we won't be able to see it.
-
You mean like this:
-
We can't see that : src="C:\Documents and Settings\L8CKHO1\My Documents\My Pictures\exampleresults policy.jpg"
it must be http://www.blabla.com/policy.jpg
-
If you follow this link (click on origineel for better quality):
Can you see it now?
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply