Select Based on Column Value in Postgres

Question

I want to get values of TIMESTAMP and STRING_VALUE based on selected ID. Suppose My Selected ID is 4259,4226 and 4259 Then It should select TIMESTAMP and STRING_VALUE for selected ID using CASE Statement. I have tried Below query but returning Into Error

CREATE TABLE "DRL_FTO3_DI1_A0"
(
  "VARIABLE" integer,
  "CALCULATION" integer,
  "TIMESTAMP_S" integer,
  "TIMESTAMP_MS" integer,
  "VALUE" double precision,
  "STATUS" integer,
  "GUID" character(36),
  "STRVALUE" character varying(50)
)

INSERT INTO "DRL_FTO3_DI1_A0"(
            "VARIABLE", "CALCULATION", "TIMESTAMP_S", "TIMESTAMP_MS", "VALUE", 
            "STATUS", "GUID", "STRVALUE")
    VALUES (4226, 0, 1451120925, 329,0 , 1078067200, '', 'BATCH 1'),
           (4306, 0, 1451120925, 329,0 , 1078067200, '', 'BATCH 2'),
           (4311, 0, 1451120925, 329,0 , 1078067200, '', '2')

Now Suppose Out of three Variable(4226,4306,4311) I want to select 4226 and 4311

SELECT ((TIMESTAMP WITHOUT Time Zone 'epoch' + "TIMESTAMP_S" * INTERVAL '1 second') AT TIME ZONE 'UTC')::TIMESTAMP WITHOUT Time Zone,
SUM(CASE WHEN "VARIABLE" = 4226 Then "STRVALUE" END) as 'A',
SUM(CASE WHEN "VARIABLE" = 4311 Then "STRVALUE" END) as 'B'
FROM "DRL_FTO3_DI1_A0"
GROUP BY "TIMESTAMP_S"
ORDER BY "TIMESTAMP_S";


 TIMESTAMP_S                         A            B
 2015-12-26 14:38:45                BATCH_1       2

I don't understand what you're trying to do. Perhaps example data (and error message) would help? — thebjorn, Dec 26 '15 at 10:49
Will the `crosstab()` function do what you want? http://stackoverflow.com/questions/3002499/postgresql-crosstab-query/11751905#11751905 — thebjorn, Dec 26 '15 at 11:27
Can you please tell me how can i write the query for crosstab in my case — Parth Desai, Dec 26 '15 at 11:30
This is what i have tried using crosstab but resulting into error SELECT * FROM crosstab( 'SELECT "VARIABLE","TIMESTAMP_S","STRVALUE" FROM "DRL_FTO3_DI1_A0" ORDER BY 2') AS ct ("TIMESTAMP_S" Integer, "A" text, "B" text); — Parth Desai, Dec 26 '15 at 11:46
ERROR: return and sql tuple descriptions are incompatible ********** Error ********** ERROR: return and sql tuple descriptions are incompatible SQL state: 42601 This is the error i am getting — Parth Desai, Dec 26 '15 at 11:47

score 1 · Answer 1 · answered Dec 26 '15 at 12:14

This Is the Query using crosstab and It Works

SELECT *
    FROM  crosstab (
        $$SELECT "VARIABLE", "TIMESTAMP_S", "STRVALUE"
         FROM   "DRL_FTO3_DI1_A0"
         WHERE  "VARIABLE" = ANY (array[4306,4226])
         ORDER  BY 1,2$$
    ) 
        AS
    t (            
        "TIMESTAMP_S" integer,                
        "A" character varying,
        "B" character varying
    );

Select Based on Column Value in Postgres

1 Answers1