CODEWITHSUNDEEP
postgresqlcrosstab
nagi
nagi

Reputation: 381

Convert rows to column in PostgreSQL

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

enter image description here

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.

Upvotes: 6

Views: 44536

Answers (2)

G. Ann - SonarSource Team
G. Ann - SonarSource Team

Reputation: 22854

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

Upvotes: 1

McNets
McNets

Reputation: 10827

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

Upvotes: 8

Related Questions