Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE t (
    myarray text[]
);

INSERT INTO t VALUES
(ARRAY['A', 'B', 'C', 'D']),
(ARRAY['A', 'B', 'C', 'D', 'E']),
(ARRAY['A', 'B', 'D', 'C']),
(ARRAY['A', 'D', 'C', 'B']);

SELECT * FROM t;
4 rows affected



myarray


{A,B,C,D}

{A,B,C,D,E}

{A,B,D,C}

{A,D,C,B}


SELECT
    a.*,
    COUNT(*)
FROM t,
    unnest(myarray) WITH ORDINALITY as a(value, index)
GROUP BY a.value, a.index
ORDER BY a.value, a.index


value
index
count


A
1
4

B
2
3

B
4
1

C
3
3

C
4
1

D
2
1

D
3
1

D
4
2

E
5
1


SELECT
    *
FROM (
    SELECT
        generate_series(1, max(cardinality(myarray))) idx
    FROM t
) gs
CROSS JOIN (
    SELECT DISTINCT
        unnest(myarray) val
    FROM t
) values


idx
val


1
D

2
D

3
D

4
D

5
D

1
C

2
C

3
C

4
C

5
C

1
B

2
B

3
B

4
B

5
B

1
A

2
A

3
A

4
A

5
A

1
E

2
E

3
E

4
E

5
E


SELECT
    *
FROM (
    SELECT
        generate_series(1, max(cardinality(myarray))) idx
    FROM t
) gs
CROSS JOIN (
    SELECT DISTINCT
        unnest(myarray) val
    FROM t
) values
LEFT JOIN (
    SELECT
        *
    FROM t,
        unnest(myarray) WITH ORDINALITY as a(val, index)
) s ON s.index = gs.idx AND s.val = values.val
ORDER BY values.val, gs.idx


idx
val
myarray
val
index


1
A
{A,D,C,B}
A
1

1
A
{A,B,C,D}
A
1

1
A
{A,B,D,C}
A
1

1
A
{A,B,C,D,E}
A
1

2
A
null
null
null

3
A
null
null
null

4
A
null
null
null

5
A
null
null
null

1
B
null
null
null

2
B
{A,B,C,D}
B
2

2
B
{A,B,D,C}
B
2

2
B
{A,B,C,D,E}
B
2

3
B
null
null
null

4
B
{A,D,C,B}
B
4

5
B
null
null
null

1
C
null
null
null

2
C
null
null
null

3
C
{A,D,C,B}
C
3

3
C
{A,B,C,D}
C
3

3
C
{A,B,C,D,E}
C
3

4
C
{A,B,D,C}
C
4

5
C
null
null
null

1
D
null
null
null

2
D
{A,D,C,B}
D
2

3
D
{A,B,D,C}
D
3

4
D
{A,B,C,D}
D
4

4
D
{A,B,C,D,E}
D
4

5
D
null
null
null

1
E
null
null
null

2
E
null
null
null

3
E
null
null
null

4
E
null
null
null

5
E
{A,B,C,D,E}
E
5


SELECT
    values.val, gs.idx,
    COUNT(*) FILTER (WHERE s.index IS NOT NULL)
FROM (
    SELECT
        generate_series(1, max(cardinality(myarray))) idx
    FROM t
) gs
CROSS JOIN (
    SELECT DISTINCT
        unnest(myarray) val
    FROM t
) values
LEFT JOIN (
    SELECT
        *
    FROM t,
        unnest(myarray) WITH ORDINALITY as a(val, index)
) s ON s.index = gs.idx AND s.val = values.val
GROUP BY values.val, gs.idx
ORDER BY values.val, gs.idx


val
idx
count


A
1
4

A
2
0

A
3
0

A
4
0

A
5
0

B
1
0

B
2
3

B
3
0

B
4
1

B
5
0

C
1
0

C
2
0

C
3
3

C
4
1

C
5
0

D
1
0

D
2
1

D
3
1

D
4
2

D
5
0

E
1
0

E
2
0

E
3
0

E
4
0

E
5
1

idx	val	myarray	val	index
1	A	{A,D,C,B}	A	1
1	A	{A,B,C,D}	A	1
1	A	{A,B,D,C}	A	1
1	A	{A,B,C,D,E}	A	1
2	A	null	null	null
3	A	null	null	null
4	A	null	null	null
5	A	null	null	null
1	B	null	null	null
2	B	{A,B,C,D}	B	2
2	B	{A,B,D,C}	B	2
2	B	{A,B,C,D,E}	B	2
3	B	null	null	null
4	B	{A,D,C,B}	B	4
5	B	null	null	null
1	C	null	null	null
2	C	null	null	null
3	C	{A,D,C,B}	C	3
3	C	{A,B,C,D}	C	3
3	C	{A,B,C,D,E}	C	3
4	C	{A,B,D,C}	C	4
5	C	null	null	null
1	D	null	null	null
2	D	{A,D,C,B}	D	2
3	D	{A,B,D,C}	D	3
4	D	{A,B,C,D}	D	4
4	D	{A,B,C,D,E}	D	4
5	D	null	null	null
1	E	null	null	null
2	E	null	null	null
3	E	null	null	null
4	E	null	null	null
5	E	{A,B,C,D,E}	E	5

val	idx	count
A	1	4
A	2	0
A	3	0
A	4	0
A	5	0
B	1	0
B	2	3
B	3	0
B	4	1
B	5	0
C	1	0
C	2	0
C	3	3
C	4	1
C	5	0
D	1	0
D	2	1
D	3	1
D	4	2
D	5	0
E	1	0
E	2	0
E	3	0
E	4	0
E	5	1

val	idx	count
A	1	4
A	2	0
A	3	0
A	4	0
A	5	0
B	1	0
B	2	3
B	3	0
B	4	1
B	5	0
C	1	0
C	2	0
C	3	3
C	4	1
C	5	0
D	1	0
D	2	1
D	3	1
D	4	2
D	5	0
E	1	0
E	2	0
E	3	0
E	4	0
E	5	1

val	idx	count
A	1	4
A	2	0
A	3	0
A	4	0
A	5	0
B	1	0
B	2	3
B	3	0
B	4	1
B	5	0
C	1	0
C	2	0
C	3	3
C	4	1
C	5	0
D	1	0
D	2	1
D	3	1
D	4	2
D	5	0
E	1	0
E	2	0
E	3	0
E	4	0
E	5	1