时间戳

首页 > 解决方案 > 翻转列和行 SQL

sql - 翻转列和行 SQL

问题描述

我正在尝试使用 SQL 翻转列和行。我从旧的 stackoverflow 问题中查找并尝试了一些选项，但未能成功地将其应用到我的表中。

我有一个表（“计数班次”），如下所示：

Shift,day1,day2,day3,...,day30
D,133,123,16,...,124
N,12,3,15,...,4
X,2,4,5,...,6
F,25,32,32,...,64
C,1,3,0,...,1

我想将其翻转为：

Day,D,N,X,F,C
day1,133,12,2,25,1
day2,123,3,4,32,3
day3,16,15,5,32,0
...
day30,124,4,6,64,1

我一直在尝试使用以下内容：

SELECT 'SELECT *
FROM   crosstab(
       $ct$SELECT u.attnum, t.rn, u.val
        FROM  (SELECT row_number() OVER () AS rn, * FROM '
                              || attrelid::regclass || ') t
             , unnest(ARRAY[' || string_agg(quote_ident(attname)
                              || '::text', ',') || '])
                 WITH ORDINALITY u(val, attnum)
        ORDER  BY 1, 2$ct$
   ) t (attnum bigint, '
     || (SELECT string_agg('r'|| rn ||' text', ', ')
         FROM  (SELECT row_number() OVER () AS rn FROM "COUNT SHIFTS") t)
     || ')' AS sql
FROM   pg_attribute
WHERE  attrelid = 'COUNT SHIFTS'::regclass
AND    attnum > 0
AND    NOT attisdropped
GROUP  BY attrelid;

从这个线程，但无法使其工作。

任何建议表示赞赏！

编辑：这是我尝试该查询时得到的错误消息：

Error: Failed to execute query "SELECT 'SELECT *
FROM   crosstab(
       $ct$SELECT u.attnum, t.rn, u.val
        FROM  (SELECT row_number() OVER () AS rn, * FROM '
                              || attrelid::regclass || ') t
             , unnest(ARRAY[' || string_agg(quote_ident(attname)
                              || '::text', ',') || '])
                 WITH ORDINALITY u(val, attnum)
        ORDER  BY 1, 2$ct$
   ) t (attnum bigint, '
     || (SELECT string_agg('r'|| rn ||' text', ', ')
         FROM  (SELECT row_number() OVER () AS rn FROM "COUNT SHIFTS") t)
     || ')' AS sql
FROM   pg_attribute
WHERE  attrelid = '"COUNT SHIFTS"'::regclass
AND    attnum > 0
AND    NOT attisdropped
GROUP  BY attrelid;" Failed to Parse Query SELECT * FROM (SELECT 'SELECT *
FROM   crosstab(
       $ct$SELECT u.attnum, t.rn, u.val
        FROM  (SELECT row_number() OVER () AS rn, * FROM '
                              || attrelid::regclass || ') t
             , unnest(ARRAY[' || string_agg(quote_ident(attname)
                              || '::text', ',') || '])
                 WITH ORDINALITY u(val, attnum)
        ORDER  BY 1, 2$ct$
   ) t (attnum bigint, '
     || (SELECT string_agg('r'|| rn ||' text', ', ')
         FROM  (SELECT row_number() OVER () AS rn FROM "COUNT SHIFTS") t)
     || ')' AS sql
FROM   pg_attribute
WHERE  attrelid = '"COUNT SHIFTS"'::regclass
AND    attnum > 0
AND    NOT attisdropped
GROUP  BY attrelid;) AS T LIMIT 50
line 5:43 no viable alternative at input '(SELECT'SELECT *\nFROM   crosstab(\n       $ct$SELECT u.attnum, t.rn, u.val\n        FROM  (SELECT row_number() OVER () AS rn, * FROM 'attrelidregclass'
Component: Extended Error Display
Method: View More Info

标签： sqlpostgresql

解决方案

我能想到的一种方法是：

select day, 
       (shifts ->> 'D')::int as d,
       (shifts ->> 'N')::int as n,
       (shifts ->> 'X')::int as x,
       (shifts ->> 'F')::int as f,
       (shifts ->> 'C')::int as c
from (       
  select day, jsonb_object_agg(shift, value) shifts
  from (
    select to_jsonb(s) ->> 'shift' as shift, x.*
    from count_shifts s
      cross join jsonb_each(to_jsonb(s)) as x (day, value)
    where x.day <> 'shift'
  ) t1
  group by day
) t2 
order by day;

最里面的查询返回如下内容：

shift | key   | value
------+-------+------
D     | day01 | 133  
D     | day02 | 123  
D     | day03 | 16   
D     | day04 | 124  
N     | day01 | 12   
N     | day02 | 3    
N     | day03 | 15   
N     | day04 | 4    
...

然后下一个级别每天将其聚合回一行，其结果是：

day   | shifts                                      
------+---------------------------------------------
day04 | {"C": 1, "D": 124, "F": 64, "N": 4, "X": 6} 
day01 | {"C": 1, "D": 133, "F": 25, "N": 12, "X": 2}
day03 | {"C": 0, "D": 16, "F": 32, "N": 15, "X": 5} 
day02 | {"C": 3, "D": 123, "F": 32, "N": 3, "X": 4}

最外面的查询，然后将每个班次的值提取为一列并将其转换回一个数字。

您可能希望将其放入视图中，以使您的生活更轻松。

在线示例

推荐阅读