Search

Ramchandra's Weblog

Just ……everything about DB2 LUW

Category

SQL

DB2 ROWS TO COLUMN

CREATE A TABLE AS BELOW

create table PIVOT(
    ID INTEGER,
    NAME  CHAR(10),
    COLCOUNT  INTEGER
)

INSERT THE VALUES AS BELOW 

SELECT * FROM PIVOT

/*
ID    NAME    COLCOUNT
1    COL101        1
1    COL102        2
1    COL103        3
2    COL201        1
2    COL202        2
2    COL203        3
*/

QUERY
SELECT
    ID,
    max(case when COLCOUNT = 1 THEN NAME end) AS COL1,
    max(case when COLCOUNT = 2 THEN NAME end) AS COL2,
    max(case when COLCOUNT = 3 THEN NAME end) AS COL3
FROM PIVOT
GROUP BY ID

/*
ID    COL1    COL2    COL3
1    COL101        COL102        COL103   
2    COL201        COL202        COL203   
*/

Advertisements

Mastering Top-N and OLAP Queries in DB2

Mastering Top-N and OLAP Queries in DB2

QUERY TO FIND Nth MAXIMUM

table :->CREATE TABLE COMPANY  ( ID INTEGER,NAME CHAR(50))……
replace N with the number you want

1.SELECT
    ID,    NAME
FROM
    COMPANY C1
WHERE
    ( N = (    SELECT
                COUNT ( DISTINCT ( C2.ID ) )
            FROM
                COMPANY C2
            WHERE
                C2.ID >=C1.ID
    )
    )

2. select T.ID,T.NAME from (
select ID,NAME,DENSE_RANK ()OVER (ORDER BY ID desc)  RK
FROM COMPANY) as T
where T.RK=N

Create a free website or blog at WordPress.com.

Up ↑