Flip Columns and Rows SQL

Question

I am trying to flip columns and rows using SQL. I've looked up and trialled a few options from older stackoverflow questions, but haven't been able to successfully apply it to my table.

I have a table ("Count Shifts") such as the following:

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

I want to flip it to:

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

I've been trying to use the following:

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;

From this thread, but couldn't make it work.

Any advice is appreciated!!

EDIT: this is the error message I get from trying that query:

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

You'll better off performing this transposing at the app level. — The Impaler, Jul 25 '20 at 14:32

SABER · Answer 1 · 2020-07-26T18:50:37.370

0

The issue seems to be IDE specific , you can create the following function in your database and then try calling it from IDE console/UI.

/*function call : 
                    select * from fn_return_pivot() ;
*/
create or replace function fn_return_pivot() 
returns table(srno bigint , D text, N text , X text , F text, C text)
as 
$$
declare v_query text ;
begin 

v_query = (SELECT T.sql 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 );

return query execute v_query ;

end ;
$$
language plpgsql ;

You need to be careful while using this function cause it may generate error in following cases :

If No. of Shifts does not remain constant.
If any shift data is missing(i.e. there should be data for every shift).

edited Jul 26 '20 at 18:50

answered Jul 25 '20 at 14:51

SABER

373
6
17

Thanks for the idea! Unfortunately it hasn't worked. I posted the error log I get in my main body now – Barnee Jul 26 '20 at 15:03
@Barnee you have used `BY attrelid;) AS T LIMIT 50` at end , you have wrote `;` after group by clause. Remove it and it shall work. – SABER Jul 26 '20 at 16:02
```AS T LIMIT 50``` is a part of the error I guess, because it is not in my query. I have removed the ```;``` but still no luck :( – Barnee Jul 26 '20 at 16:12
Yes. [Link to gif here incase you can spot anything I don't](https://i.imgur.com/MxBq5vU.gif) – Barnee Jul 26 '20 at 16:21
the error seems to be IDE specific, try creating a function/view with it, which will return the table if no. of shifts are constant. And then calling it. – SABER Jul 26 '20 at 16:46
which IDE are you using? – SABER Jul 26 '20 at 17:33
It's Pyramid Analytics postgresql. Original table seems to show number of shifts is constant – Barnee Jul 26 '20 at 17:45
I have change the answer , try calling it from custom query tab `select * from fn_return_pivot()` . I would like to know if it works or not. – SABER Jul 26 '20 at 18:52
Calling ```select * from fn_return_pivot()``` yields: ```Error: Failed to execute query "select * from fn_return_pivot()" Failed to Parse Query SELECT * FROM (select * from fn_return_pivot()) AS T LIMIT 50 line 1:46 no viable alternative at input '(select*fromfn_return_pivot())' Component: Extended Error Display Method: View More Info```. Trying your amended code returns ```AS T LIMIT 50 line 4:0 no viable alternative at input '(create'``` – Barnee Jul 26 '20 at 19:15
That's all I can contribute, mate. Try adding the tag for `pyramid analytics` in your question so the people who are good with it can see this question. – SABER Jul 26 '20 at 19:36
Thanks for your help bud. I'll get in touch with my account manager at Pyramid tomorrow and see if they can help - seems like an issue on their side. Will update if I get a solution. Cheers! – Barnee Jul 26 '20 at 19:57

score 0 · Answer 2 · answered Jul 25 '20 at 15:30

You can do with plain sql - and some "clever" editing skills.

Add a series of integers. I create a two-row in-line table b, containing 0 and 1, and cross join it with itself 4 times, adding up the two integers, respectively multiplied by different powers of 2, creating the next in-line table i(i).

Then, I cross join the input with i, and filter to only get 1 to 4, as I only have 4 days, and not your 30 days, to un-pivot.

Finally, I re-pivot GROUP-ing by the integer I had added while un-pivoting. Here goes:

WITH
input(Shift,day01,day02,day03,day04) AS (
          SELECT 'D',133,123,16,124
UNION ALL SELECT 'N',12,3,15,4
UNION ALL SELECT 'X',2,4,5,6
UNION ALL SELECT 'F',25,32,32,64
UNION ALL SELECT 'C',1,3,0,1
)
,
-- start with verticalizing - un-pivoting ..
-- create 32 rows out of nothing
b(b) AS (
          SELECT 0
UNION ALL SELECT 1
)
,
i(i) AS (
  SELECT
    b16.b*(2^4)+b08.b*(2^3)+b04.b*(2^2)+b02.b*(2^1)+b01.b*(2^0) 
  FROM b AS b16 CROSS JOIN b AS b08 CROSS JOIN b AS b04 CROSS JOIN b AS b02 CROSS JOIN b AS b01
)
-- i(i) now contains 32 rows ..
,
unpivot AS (
  SELECT
    shift
  , i AS day_i
  , CASE i 
      WHEN 1 THEN day01
      WHEN 2 THEN day02
      WHEN 3 THEN day03
      WHEN 4 THEN day04  -- need to continue until 30 , therefore 2 digits for sorting and alignment ...
      ELSE CAST(NULL AS INT)
    END AS val
  FROM input CROSS JOIN i
  WHERE i BETWEEN 1 AND 4 -- I only have 4 in my example
)

-- TEST QUERY OF unpivot: ...

SELECT * FROM unpivot;
-- out  shift | day_i | val 
-- out -------+-------+-----
-- out  D     |     1 | 133
-- out  N     |     1 |  12
-- out  X     |     1 |   2
-- out  F     |     1 |  25
-- out  C     |     1 |   1
-- out  D     |     2 | 123
-- out  N     |     2 |   3
-- out  X     |     2 |   4
-- out  F     |     2 |  32
-- out  C     |     2 |   3
-- out  D     |     3 |  16
-- out  N     |     3 |  15
-- out  X     |     3 |   5
-- out  F     |     3 |  32
-- out  C     |     3 |   0
-- out  D     |     4 | 124
-- out  N     |     4 |   4
-- out  X     |     4 |   6
-- out  F     |     4 |  64
-- out  C     |     4 |   1

Now, re-pivot in final query ...

SELECT
  'Day'||RIGHT(CAST (100+day_i AS CHAR(3)),2) AS the_day 
   -- don't use "day" as it's a reserved word
   -- force leading zeroes by adding int to 100 
   -- and picking the last two digits as string
, MAX(CASE shift WHEN 'D' THEN val END) AS d
, MAX(CASE shift WHEN 'N' THEN val END) AS n
, MAX(CASE shift WHEN 'X' THEN val END) AS x
, MAX(CASE shift WHEN 'F' THEN val END) AS f
, MAX(CASE shift WHEN 'C' THEN val END) AS c
FROM unpivot
GROUP BY day_i
ORDER BY day_i
;

Final result:

-- out  the_day |  d  | n  | x | f  | c 
-- out ---------+-----+----+---+----+---
-- out  Day01   | 133 | 12 | 2 | 25 | 1
-- out  Day02   | 123 |  3 | 4 | 32 | 3
-- out  Day03   |  16 | 15 | 5 | 32 | 0
-- out  Day04   | 124 |  4 | 6 | 64 | 1
-- out (4 rows)
-- out 
-- out Time: First fetch (4 rows): 65.962 ms. All rows formatted: 66.002 ms

score 0 · Answer 3 · 2020-07-25T20:01:39.097

One way I can think of is this:

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;

The inner most query returns something like this:

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

The next level then aggregates that back into one row per day, the result of that is:

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}

And the outer most query, then extracts the value for each shift as a column and converts it back to a number.

You probably want to put that into a view to make your life easier.

Online example

Thanks a lot for writing that out! I can't seem to get it to work. I get the error ```Error: Failed to execute query AS T LIMIT 50 line 2:16 no viable alternative at input '(selectday,(shifts->' Component: Extended Error Display Method: View More Info``` — Barnee, Jul 26 '20 at 17:40
nor is there in the query I tested. Not sure where it comes from, but it seems to append it to every error message. — Barnee, Jul 26 '20 at 19:16

Flip Columns and Rows SQL

3 Answers3