0

I have a transactional table for each day (can have 1-to-many transaction per day). Now I want to transform it in such a way that I get a single record per customer with a series of 0 (if there isn't a record in the transactional table for that day) and a 1 (if there is a record for that day).

How can this be achieved in SQL (ANSI SQL or Postgres)?

Eg:

Transform this transactions table: 

Cust   Date  
123    2/3/2015  
123    4/3/2015  
456    1/3/2015

into the following table:

Cust   1/3/2015   2/3/2015   3/3/2015   4/3/2015  
123       0          1          0          1  
456       1          0          0          0

Given the suggestion to use CROSSTAB I've come up with this SQL:

SELECT *
FROM CROSSTAB12 (
   'SELECT CALLING_ISDN AS A_PARTY, DATE(CALL_STA_TIME), 1 AS VALUE
   FROM CDRVMS_ADMIN.AW_CDRICC_SAMPLE
   ORDER BY 1,2');

which produces the following error message (I'm working on a Netezza machine):

ERROR (42000) found "(" (at char 26) expecting a keyword

I'm using this documentation: http://www.postgresql.org/docs/current/interactive/tablefunc.html

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
a4xrbj1
  • 445
  • 3
  • 21
  • Thanks for the formatting changes to a_horse_with_no_name. I'm also open to suggestions to change the title of this question, I'm not a native English speaker and it's probably not explaining well what I want to get answered. Sorry for that! – a4xrbj1 Apr 02 '15 at 07:07
  • You can do this using a cross tab: http://www.postgresql.org/docs/current/interactive/tablefunc.html – Frank Heikens Apr 02 '15 at 07:09
  • Nice example of a pivot table / cross tab can be found here: http://stackoverflow.com/questions/28337765/postgresql-9-3-dynamic-pivot-table/29011847#29011847 – Frank Heikens Apr 02 '15 at 07:11

0 Answers0