ingres sql join 2 rows into 1 row from 1 table using case

Question

Please accept my apologies if this has been asked in the past but I have been through the questions and can't find the answer.

I have a table with multiple entries as follows:

    key   | type   | Code  | Date

    1234  | S      | 10DY  | 01/10/2012
    1234  | E      | 10DY  | 31/10/2012
    12376 | S      | 10DY  | 11/10/2012
    12376 | E      | 10DY  | 21/10/2012

I would like to extract the data so that it appears as follows:

    key   |  Code  | S_Date     | E_Date

    1234  |  10DY  | 01/10/2012 | 31/10/2012
    12376 |  10DY  | 11/10/2012 | 21/10/2012

Currently I have this:

SELECT  key, code, CASE WHEN type = 'S' THEN Date END AS S_Date, 
CASE WHEN type = 'E' THEN Date END AS E_Date
FROM  Table1
WHERE code = '10DY'

user2577405 · Answer 1 · 2013-11-04T16:59:57.187

0

Assumed that S_Date < E_Date, something like this should do it:

SELECT `key`, `code`, MIN(`Date`) AS S_Date, MAX(`Date`) AS E_Date FROM Table1 WHERE code='10DY' GROUP BY `key`

edited Nov 04 '13 at 16:59

answered Nov 04 '13 at 16:41

user2577405

85
5

Thank you for your response. Unfortunately I get the error that columns used in the select clause must be used in the group clause which then gives the wrong result. – user2928556 Nov 04 '13 at 16:47
Well, i tried it and got an Error because of the Keyword "key". So I corrected the SQL String. It worked for me for the Dataset you provided. – user2577405 Nov 04 '13 at 17:01

quelgeek · Answer 2 · 2013-11-06T11:32:35.240

0

Based only on the names of the columns and making some leaps of intuition in the absence of any stated constraints on the table, the following should do what you need:

SELECT s1.key, s1.code, s1.date AS S_date, s2.date AS E_date
FROM startend s1 LEFT JOIN startend s2 ON s1.key = s2.key AND s1.type <> s2.type
WHERE s1.date < s2.date OR s2.date IS NULL;

This query handles the case where there is an 'S' row but not yet an 'E' row, but it does depend on there only ever being a single 'S' row and a single 'E' row for any value of "key".

edited Nov 06 '13 at 11:32

answered Nov 06 '13 at 11:21

quelgeek

1
1

Thanks, I'm still having a few problems trying to join it at source as I think Ingres deals with it slightly differently, so I created 2 datasets and joined them via a lookup in SSRS – user2928556 Nov 08 '13 at 15:43

score 0 · Answer 3 · answered Aug 04 '14 at 12:50

All you need is this SQL statement:

SELECT key, code, MIN(Date) AS S_Date, MAX(Date) AS E_Date 
FROM Table1
GROUP BY key, code

If you always have a pair of rows, one with type = 'S' and the other with type = 'E' Then an improved version is:

SELECT key, code, MIN(Date) AS S_Date, MAX(Date) AS E_Date 
FROM Table1
GROUP BY key, code
HAVING count(*) = 2

ingres sql join 2 rows into 1 row from 1 table using case

3 Answers3