I have a table t1 as below:
create table t1 (
person_id int,
item_name varchar(30),
item_value varchar(100)
);
There are five records in this table:
person_id | item_name | item_value
1 'NAME' 'john'
1 'GENDER' 'M'
1 'DOB' '1970/02/01'
1 'M_PHONE' '1234567890'
1 'ADDRESS' 'Some Addresses unknown'
Now I want to use crosstab function to extract NAME, GENDER data, so I write a SQL as:
select * from crosstab(
'select person_id, item_name, item_value from t1
where person_id=1 and item_name in ('NAME', 'GENDER') ')
as virtual_table (person_id int, NAME varchar, GENDER varchar)
My problem is, as you see the SQL in crosstab() contains condition of item_name, which will cause the quotation marks to be incorrect.
How do I solve the problem?
To avoid any confusion about how to escape single quotes and generally simplify the syntax, use dollar-quoting for the query string:
SELECT *
FROM crosstab($$
SELECT person_id, item_name, item_value
FROM t1
WHERE person_id = 1
AND item_name IN ('NAME', 'GENDER')
$$) AS virtual_table (person_id int, name varchar, gender varchar)
And you should add ORDER BY to your query string. I quote the manual for the tablefunc module:
In practice the SQL query should always specify ORDER BY 1,2 to ensure
that the input rows are properly ordered, that is, values with the
same row_name are brought together and correctly ordered within the
row. Notice that crosstab itself does not pay any attention to the
second column of the query result; it's just there to be ordered by,
to control the order in which the third-column values appear across the page.
More details:
- PostgreSQL Crosstab Query
- Insert text with single quotes in PostgreSQL
Double your single quotes to escape them:
select * from crosstab(
'select person_id, item_name, item_value from t1
where person_id=1 and item_name in (''NAME'', ''GENDER'') ')
as virtual_table (person_id int, NAME varchar, GENDER varchar)
