I rarely post about development, and most recently i was posting about KOTLIN
I just wanted to post something, about SQL
Let us say we have a table TBL holding a key varchar column and a value varchar column:
CREATE TABLE TBL (ky varchar2(10), val varchar2(10));
we may have inserted our data:
ky: X val :11
ky: X val :22
ky: Y val: 35
ky: X val: 33
Let us do a SELECT * FROM TBL:
You can clearly notice we have a redundancy on the KY column and we can transpose them:
Doing it so:
SELECT
*
FROM
(
SELECT
ROW_NUMBER() OVER (PARTITION BY T1.KY ORDER BY T1.KY) AS TAGNUM
, T1.KY
, T1.VAL as VAL1
, T2.VAL as VAL2
, T3.VAL as VAL3
FROM
TBL T1
LEFT JOIN TBL T2 ON T2.KY = T1.KY AND T2.ROWID NOT IN (T1.ROWID)
LEFT JOIN TBL T3 ON T3.KY = T1.KY AND T3.ROWID NOT IN (T1.ROWID, T2.ROWID)
)
WHERE
TAGNUM = 1
Note that you may extend your left joins until you reach your expected "transpose" window using the same analogy.
