在PostgreSQL中将行转换为列

zzlelutf  于 2023-02-04  发布在  PostgreSQL
关注(0)|答案(2)|浏览(198)

我想在PostgreSQL中将行转换为列。我希望所有变量对各自的ID。但它不工作。

    • 预期产出:**
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);

错误:错误:返回类型SQL状态无效:42601详细信息:SQL行ID数据类型与返回行ID数据类型不匹配。

oogrdqng

oogrdqng1#

也许这个能帮上忙。
此处需要ORDER BY 1,2

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 |
+----------+-------+--------+--------+--------+

实际上,列应命名为:第一列第二列第三列第四列...
点击这里查看:http://rextester.com/MFWAW58518

3htmauhk

3htmauhk2#

我最近需要这样做&没有crosstab可用。将需要适应OP数据集,但这里是我所做的:

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

相关问题