1

The original table:

=======================================================================================
|   A   |     B    |       C        |     D     |     E     |      F      |     G     |
=======================================================================================
| 65432  | AFD452E |   sometext1    |   IA1     |   10,6    |    8,5      |   22,0    |
| 65432  | AFD452E |   sometext1    |   IA2     |           |    11,4     |   16,5    |
| 65432  | AFD452E |   sometext1    |   IA3     |           |    3,5      |    5,3    |
| 65989  | 74652E  |   sometext2    |   IA1     |   3,3     |             |     8     |
| 65989  | 74652E  |   sometext2    |   IA3     |   23      |     1,1     |    6,9    |
| 27890  | P8965A  |   sometext3    |   IA3     |   12,7    |     13,8    |   33,2    |
| 34432  | P673452 |   sometext6    |   IA6     |           |             |           |
| 55770  | 27799A  |   sometext7    |   IA7     |           |             |           |
=======================================================================================

The resulting table should be:

=======================================================================================
|   A   |     B    |       C        |     NEWA1     |     NEWA2     |      NEWA3      |
=======================================================================================
| 65432  | AFD452E |   sometext1    | 10,6-8,5-22,0-| -11,4-16,5-   |                 |
| 65989  | 74652E  |   sometext2    | 3,3--8-       |               |  23-1,1-6,9-    |
| 27890  | P8965A  |   sometext3    |               |               | 12,7-13,8-33,2- |
=======================================================================================

This is what I got so far :

SELECT A, B, C,
max(iif([D] like "IA1" .. rest of code)) as newa1,
max(iif([D] like "IA2" .. rest of code)) as newa2,
max(iif([D] like "IA3" .. rest of code)) as newa3
from table
group by A, B,C;

This is the output of that query :

=======================================================================================
|   A   |     B    |       C        |     NEWA1     |     NEWA2     |      NEWA3      |
=======================================================================================
| 65432  | AFD452E |   sometext1    | 10,6-8,5-22,0-| -11,4-16,5-   |                 |
| 65989  | 74652E  |   sometext2    | 3,3--8-       |               |  23-1,1-6,9-    |
| 27890  | P8965A  |   sometext3    |               |               | 12,7-13,8-33,2- |
| 34432  | P673452 |   sometext6    |               |               |                 |
| 55770  | 27799A  |   sometext7    |               |               |                 |
=======================================================================================
  • When your description was a picture, the NEWxx values in your expected output looked slightly differently. E.g. the second row's NEWA1 was to be 3,3-8 but in the new version it became 3,3--8- – same as your actual output, in fact. Could you please clarify/edit what they should really be? Note also an empty NEWA3 in the text version where it previously was 3,5-5,3. I'm guessing that's a mistake, because it matched the source before but now it doesn't. (Your actual output likely has a mistake too: it shows the same value in NEWA2 and NEWA3 for 65432 | AFD452E | sometext1.) – Andriy M Jul 23 '15 at 5:45
  • Hi, Actually you are right, I have just aranged the output. But the need is clearly define. Someone gave me this query as an answer which works great but it also brought me rows from the original table I did not display earlier where column D has values other than IA1 and IA2 and IA3 like : | 34432 | P673452 | sometext3 | IA6 | | | | | 55770 | 27799A | sometext4 | IA7 | | | | This the query : select A, B, C, max(iif([D] like "1A1" .. rest of code)) as newa1, e ... from table group by A, B. c; How to avoid rows when col D is not IA1 and IA2 and IA3 ? – user71111 Jul 23 '15 at 7:19
  • Basically what I want to add is that the resulting columns NEWA1 and NEWA2 and NEWA3 should not be all : empty, or null or zero lenght as the same time. – user71111 Jul 23 '15 at 7:42
  • Sorry, I don't get it. You are grouping by A, B, C and you do not seem to be selecting D in your SELECT clause – how then can you get IA6, IA7 etc. in the output? That exact GROUP BY and MAXes around your IIFs were what I was going to suggest too, by the way. (I just wanted to clarify that thing I asked 2 hours ago before posting my answer.) So I really don't understand your problem at this point. You may have done something wrong but I can't figure out what it could be. Maybe you should update your question now that you seem to have found a solution but are struggling to implement it. – Andriy M Jul 23 '15 at 7:45
  • I re-edit the question now to make myself clear! Thx – user71111 Jul 23 '15 at 9:13
1

Looks like you just need to add a WHERE filter to your query:

SELECT
  A, B, C, ...
FROM
  YourTable
WHERE
  D IN ('IA1', 'IA2', 'IA3')
GROUP BY
  A, B, C
;

That way those A, B, C groups that do not have 'IA1', 'IA2' or 'IA3' in column D will be excluded from the output.

  • All my congrat to all of you the question has been fully answered with Andriy las post. Thank you very much to all! :) – user71111 Jul 23 '15 at 12:10
  • One last thing :) Is it possible to add a new column in the result let's say SumTotal which sould be the SUM of any occurences in column G for the same value in column A no matter if column D is IN ('IA1', 'IA2', 'IA3'). I have tried by adding this before the where clause, but it just giving me back just column G not the SUM, I guess because of the constraint of the Where clause: SELECT A, B, C, ..., Sum(G) as SumTotal FROM YourTable WHERE D IN ('IA1', 'IA2', 'IA3') GROUP BY A, B, C ; Thank you! – user71111 Jul 23 '15 at 13:25
  • @user71111: If you still want to suppress the rows where all NEWxx values are empty, then remove the WHERE clause and add this HAVING clause instead (after GROUP BY, of course, according to the syntax): HAVING max(iif([D] like "IA1" .. rest of code)) IS NOT NULL OR max(iif([D] like "IA2" .. rest of code)) IS NOT NULL OR max(iif([D] like "IA3" .. rest of code)) IS NOT NULL. (Not sure, maybe you'll need to enclose separate predicates in parentheses.) Yes, it'll be much more messy now. But if it's fine to have those empty data lines, then you can just remove the WHERE without adding anything. – Andriy M Jul 23 '15 at 13:41
  • Really, Thank you very much! It's working like a charm now with the having as you suggesting! Great help! Thx :) – user71111 Jul 23 '15 at 14:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.