Reputation: 19
I have a table listing student grade,
AG T1 T2 T3
L0011001 A B A
L0011002 A B B
L0011003 A A C
L0011004 A A C
I want output for AG like this:
L0011001 2A 1B
L0011002 1A 2B
L0011003 2A 1C
L0011004 2A 1C
How to get this?
Upvotes: 0
Views: 70
Reputation: 1
Just see if below query works for you.
`SELECT AG,ALL_VAL,
(LENGTH(ALL_VAL) - LENGTH(REPLACE(ALL_VAL, 'A', ''))) || 'A' || ' '||
(LENGTH(ALL_VAL) - LENGTH(REPLACE(ALL_VAL, 'B', ''))) || 'B' || ' '||
(LENGTH(ALL_VAL) - LENGTH(REPLACE(ALL_VAL, 'C', ''))) || 'C' as OUTPUT
FROM (
SELECT AG,T1||T2||T3 AS ALL_VAL FROM t_s
) RSLT`
I got the below output using above query.
- L0011001-->ABA--> 2A 1B 0C
- L0011002-->ABB--> 1A 2B 0C
- L0011003-->AAC--> 2A 0B 1C
- L0011004-->AAC--> 2A 0B 1C
Upvotes: 0
Reputation: 91376
You might like to consider a union query and a crosstab, the output is not exactly as you wish, but it does make sense.
TRANSFORM Count(qry.ag) AS countofag
SELECT qry.ag
FROM (SELECT tbl.ag,
tbl.t1
FROM tbl
UNION ALL
SELECT tbl.ag,
tbl.t2
FROM tbl
UNION ALL
SELECT tbl.ag,
tbl.t3
FROM tbl) AS qry
GROUP BY qry.ag
PIVOT qry.t1;
Upvotes: 0
Reputation: 1271231
Your data is in a really, really bad format. You should have one row per "T" and per student.
However, sometimes we are stuck with bad data formats. You can do what you want with iif()
:
select ag,
(iif(t1 = 'A', 1, 0) + iif(t2 = 'A', 1, 0) + iif(t2 = 'A', 1, 0)) as A_s,
(iif(t1 = 'B', 1, 0) + iif(t2 = 'B', 1, 0) + iif(t2 = 'B', 1, 0)) as B_s,
(iif(t1 = 'C', 1, 0) + iif(t2 = 'C', 1, 0) + iif(t2 = 'C', 1, 0)) as C_s
from t;
This doesn't do exactly what you want. It puts the values into separate columns -- a format that makes more sense to me.
For your specific format:
select ag,
(iif(A_s > 0, A_s & "A ") &
iif(B_s > 0, B_s & "B ") &
iif(C_s > 0, C_s & "C ")
)
from (select ag,
(iif(t1 = 'A', 1, 0) + iif(t2 = 'A', 1, 0) + iif(t2 = 'A', 1, 0)) as A_s,
(iif(t1 = 'B', 1, 0) + iif(t2 = 'B', 1, 0) + iif(t2 = 'B', 1, 0)) as B_s,
(iif(t1 = 'C', 1, 0) + iif(t2 = 'C', 1, 0) + iif(t2 = 'C', 1, 0)) as C_s
from t
) as x
Upvotes: 3