Tricky SQL Queries

Q1. I have a table with data

Column 1 Column 2
A 1
A 2
A 3
B 1
B 2
C 4
c 6
c 7

I need to get the output as (based on group by first column)
A 1 2 3
B 1 2
C 4 6 7

Ans:

Select
Id
, Max(CASE WHEN PosN = 1
THEN Col2 END) As Val1
, Max(CASE WHEN PosN = 2
THEN Col2 END) As Val2
, Max(CASE WHEN PosN = 3
THEN Col2 END) As Val3
, Max(CASE WHEN PosN = 4
THEN Col2 END) As Val4
, Max(CASE WHEN PosN = 5
THEN Col2 END) As Val5
, Max(CASE WHEN PosN = 6
THEN Col2 END) As Val6
, Max(CASE WHEN PosN = 7
THEN Col2 END) As Val7
, Max(CASE WHEN PosN = 8
THEN Col2 END) As Val8
, Max(CASE WHEN PosN = 9
THEN Col2 END) As Val9
, Max(CASE WHEN PosN = 10
THEN Col2 END) As Val10
, Max(CASE WHEN PosN > 10
THEN 'You Need More Columns!'
Else '' END) As CheckVal
From
(Select
Col1
, Col2
, Row_Number () Over (Partition By Col1
Order By Col2) as PosN
From tbl_with_data
) As D1
Group By 1
;

Q2: Reversing the string:

CREATE TABLE DATA005
(
EID INTEGER NOT NULL PRIMARY KEY
,NAME VARCHAR(30) NOT NULL
);

INSERT INTO DATA005 VALUES(1, 'JOEY BOY');
INSERT INTO DATA005 VALUES(2, 'CALVIN');
INSERT INTO DATA005 VALUES(3, 'HOBBES');
INSERT INTO DATA005 VALUES(4, '');

WITH RECURSIVE REVNAME(EID, NAME, NAMELEN, LVL, RNAME)
AS
(
SELECT EID, NAME, CHARACTER_LENGTH(NAME), 1(INTEGER), '' (VARCHAR(30))
FROM DATA005

UNION ALL

SELECT EID, NAME, NAMELEN, LVL+1, SUBSTRING(NAME FROM LVL FOR 1) || RNAME
FROM REVNAME
WHERE LVL <= NAMELEN
)
SELECT EID, NAME, RNAME
FROM REVNAME
WHERE NAMELEN+1 = LVL
;

EID NAME RNAME
1 JOEY BOY YOB YEOJ
2 CALVIN NIVLAC
3 HOBBES SEBBOH
4

Q3: Space Usage Query

select databasename
,cast(sum(maxperm)/1024/1024/1024 as decimal(7,2)) max_perm
,cast(sum(currentperm)/1024/1024/1024 as decimal(7,2)) current_perm
,cast(sum(maxspool)/1024/1024/1024 as decimal(7,2)) max_spool
,cast(sum(currentspool)/1024/1024/1024 as decimal(7,2)) current_spool
from dbc.diskspace
where databasename in
(select databasename from dbc.databases
 where dbkind = 'D'
and databasename in ('STG_DEV', 'STG_TST_TMP', 'WRK_STG_DEV')
)
group by databasename order by max_perm desc;