CODEWITHSUNDEEP
db2
J.Lee
J.Lee

Reputation: 23

How to pivot 1 column and 2 rows into 2 columns and 1 row in Db2 SQL

How to achieve 1 column 2 rows to 2 columns 1 row on DB2 please?

eg :

select value from <tablename> WHERE name='VAR' ORDER BY effectivedate DESC FETCH FIRST 2 ROWS ONLY;

which gives 

VAR
----
12
57

But I want to get

VAR1,VAR2
-----------
12  ,57

Thanks very much!

Upvotes: 1

Views: 878

Answers (3)

Paul Vernon
Paul Vernon

Reputation: 3901

Typically, the best way to "pivot" rows to columns is to aggregate over CASE statements.

For example

SELECT MAX(CASE WHEN RN = 1 THEN value END) AS VAR1
,      MAX(CASE WHEN RN = 2 THEN value END) AS VAR2
,      MAX(CASE WHEN RN = 3 THEN value END) AS VAR3
,      MAX(CASE WHEN RN = 4 THEN value END) AS VAR4
FROM (
    SELECT *, ROW_NUMBER() OVER(ORDER BY effectivedate DESC) AS RN
    FROM a_table T
    )

will return this

 VAR1 VAR2 VAR3 VAR4
 ---- ---- ---- ----
   12   57    1 NULL

using the table and data in my other answer which would return

Upvotes: 1

Paul Vernon
Paul Vernon

Reputation: 3901

There are many ways to do this. If you are on Db2 LUW 11.1 or above, this will work

SELECT * FROM TABLE(VALUES 
   ( ( select value from a_table WHERE name='VAR' ORDER BY effectivedate DESC FETCH FIRST 1 ROW ONLY)
   , ( select value from a_table WHERE name='VAR' ORDER BY effectivedate DESC OFFSET 1 ROW FETCH NEXT 1 ROW ONLY )
 )) AS t(VAR1, VAR2)

and with this table and data

create TABLE a_table( value int, name char(3), effectivedate date);
INSERT INTO a_table values (12,'VAR','2018-01-10'),(57,'VAR', '2018-01-09'),(1,'VAR','2018-01-08');

will return this result

VAR1 VAR2
---- ----
  12   57

Upvotes: 0

user7392562
user7392562

Reputation:

Use substring and aliases

SELECT 
    SUBSTR(VAR, 1,LOCATE(' ',VAR)-1) as VAR1
  , SUBSTR(VAR, LOCATE(' ',VAR)+1)   as VAR2
FROM YOURTABLE;

Basically breaking on space, if you have fixed length you can use without locate.

Upvotes: 0

Related Questions