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    |               |               |                 |
=======================================================================================
Improve this question
5
  • 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
    Commented Jul 23, 2015 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
    Commented Jul 23, 2015 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
    Commented Jul 23, 2015 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
    Commented Jul 23, 2015 at 7:45
  • I re-edit the question now to make myself clear! Thx
    – user71111
    Commented Jul 23, 2015 at 9:13

1 Answer 1

Reset to default
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.

Improve this answer
4
  • All my congrat to all of you the question has been fully answered with Andriy las post. Thank you very much to all! :)
    – user71111
    Commented Jul 23, 2015 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
    Commented Jul 23, 2015 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
    Commented Jul 23, 2015 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
    Commented Jul 23, 2015 at 14:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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