Convert rows to column in PostgreSQL

Question

I want to convert rows to column in PostgreSQL.I want all variables against their respective id.but its not working.

Expected Output:

myvar   desc    fname   lname        sdate          edate         id     
title1  desc1   cina    jhon    1483920000000   1484524800000     14
title2  desc2   jhon    lname2  1483920000000   1483910000000     16
title3  desc3   diesel  zier    1483920000000   1484524800000     17



 SELECT * FROM crosstab(
 'SELECT  name, value, id FROM test ORDER  BY id') AS (
 "myVar" text, "desc" text, "fname" text, "lname" text,"sdate" text,"edate" text, "value" text ,"containerid" bigint);

Error: ERROR: invalid return type SQL state: 42601 Detail: SQL rowid datatype does not match return rowid datatype.

Please type up this graph. Do not paste pictures. Just type `TABLE test;` and put that output here. Even better, if you really care and want upvotes.. Paste the DDL. `CREATE TABLE AS SELECT` and you'll get joy. — Evan Carroll, Jan 14 '17 at 18:48

McNets · Answer 1 · 2017-01-14T20:27:58.597

8

Maybe this can helps.

ORDER BY 1,2 is required here.

select *
    from crosstab (
        'select id, name, value
        from tt1
        order by 1,2')
    AS (row_name int, col1 text, col2 text, col3 text, col4 text);

+----------+-------+--------+--------+--------+
| row_name | col1  |  col2  |  col3  |  col4  |
+----------+-------+--------+--------+--------+
|    14    | desc1 |  chen  |  john  | title1 |
+----------+-------+--------+--------+--------+
|    15    | desc2 | fname2 | lname2 | title2 |
+----------+-------+--------+--------+--------+
|    16    | desc4 | deiser |  ziel  | title3 |
+----------+-------+--------+--------+--------+

In fact, columns should be named as: col1, col2, col3, col4, ...

Check it here: http://rextester.com/MFWAW58518

edited Jan 14 '17 at 20:27

answered Jan 14 '17 at 18:39

McNets

10,352
3
32
61

1

@EvanCarroll write an answer, my knowledge about crosstab is rally limited. – McNets Jan 14 '17 at 18:51
So is mine. I always get what I want but it's not my strong area. You went above and beyond what I was willing to do by typing up any of this picture graph. I have like 0-tolerance for that. I draw the line at making DDL with text. Anyway, keep digging man. This is how we get better ourselves. It may very well have to do with ORDER BY 1,2. The docs mention it. I learned something. I just couldn't observe it with your Rexster. – Evan Carroll Jan 14 '17 at 18:54
Crosstab is definitively a weak area with PostgreSQL. – Evan Carroll Jan 14 '17 at 18:54
if fact, order by id, gives the correct row_order, but not for the name values. – McNets Jan 14 '17 at 18:56
If crosstab works in the same way than mssql PIVOT, is not possible to get the observed result. – McNets Jan 14 '17 at 19:01
ERROR: invalid return type DETAIL: SQL rowid datatype does not match return rowid datatype. ********** Error ********** but your query giving me same result as mine. ERROR: invalid return type SQL state: 42601 Detail: SQL rowid datatype does not match return rowid datatype. – nagi Jan 14 '17 at 19:03
i have use this query select * from crosstab ( 'select id, name, value from test order by 1') AS (row_name bigint, myvar varchar(255), fname varchar(255), lname varchar(255), "desc" varchar(255)); but its not giving me required results. – nagi Jan 14 '17 at 19:17
You cannot use more than 2 rows in a crosstab. One pivot depending on the other. For additional columns you should use crosstabN that is more complicated. – McNets Jan 14 '17 at 19:20
i created following code but its not working created http://rextester.com/live/CFSZX88331 – nagi Jan 14 '17 at 20:06
Great answer. Just wanted to add that (at least in Postgres 11) you can name the columns whatever you like. It doesn't have to be row_name, col1, col2 etc. – Chris Kobrzak Jan 31 '20 at 22:33

score 0 · Answer 2 · answered Feb 01 '23 at 18:49

I needed to do this recently & don't have crosstab available to me. Will need to be adapted for OPs data set, but here's what I did:

WITH
-- just a little setup to make the rest work
dates AS (
    SELECT CURRENT_DATE AS end_current
        , DATE(CURRENT_DATE - interval '1 year') AS end_prev
        , TO_DATE(date_part('year', current_date)||'0101','YYYYMMDD') AS start_current
        , TO_DATE(date_part('year', current_date)-1||'0101','YYYYMMDD') AS start_prev
        , DATE_PART('year', current_date) AS year_current
        , DATE_PART('year', DATE(CURRENT_DATE - interval '1 year')) AS year_prev
),


-- fetch data
new_users AS (
    SELECT count(*)
        , DATE_PART('year',created_at) as year
    FROM users, dates
    WHERE ((created_at >= start_current AND created_at <= end_current)
            OR (created_at >=start_prev AND created_at <= end_prev))
    GROUP BY year
),

-- turn rows into columns. 
-- We get two rows, one with a null 'curr' and one with a null 'prev'
new_users_pivot AS (
    SELECT 'People who signed up' as label
        , CASE WHEN year = year_current THEN count END AS curr
        , CASE WHEN year = year_prev THEN count END AS prev
    FROM new_users, dates

)

-- use grouping & aggregate (max) function to collapse rows & eliminate nulls
SELECT label, max(curr) as curr, max(prev) as prev
FROM new_users_pivot
GROUP BY label

Convert rows to column in PostgreSQL

2 Answers2