Re: Column aggregating for text columns

From: --CELKO-- <71062.1056_at_compuserve.com>
Date: 22 May 2002 12:04:10 -0700
Message-ID: <c0d87ec0.0205221104.75f2ab45@posting.google.com>

Please post DDL; what you showed us had no keys, so it was not really a table at all! Is this what you meant to post?

CREATE TABLE Foobar
(foo_id INTEGER NOT NULL,
 seq INTEGER NOT NULL,
 value CHAR(3) NOT NULL,
 PRIMARY KEY (foo_id, seq));

INSERT INTO Foobar VALUES (123, 1, 'abc');
INSERT INTO Foobar VALUES (123, 2, 'cde');
INSERT INTO Foobar VALUES (123, 3, 'xxx');

>> How do I convert this data in a table <<

Again this is not a table, since you have multiple columns that represent the same attriubute. This is a report and you should be using a report writer. You can "fake it" with this query:

SELECT id,

       MIN (CASE WHEN seq = 1 THEN value ELSE NULL END) AS v1,
       MIN (CASE WHEN seq = 2 THEN value ELSE NULL END) AS v2,
       MIN (CASE WHEN seq = 3 THEN value ELSE NULL END) AS v3

  FROM Foobar
 GROUP BY id;

You must have the sequence number within each foo_id because all relationships are shown by scalar values in the columns of rows in a table. You seem to be confused; have you taken a basic relational database course yet? Received on Wed May 22 2002 - 14:04:10 CDT