Need help with sql

Database
Enthusiast

Need help with sql

Hi Team,

 

I have data  as 

 

applicatoin_id  status  timestamp

CSE211143                       a            2016-10-11

CSE211143                        x            2016-12-11

CSE211143                       b            2016-09-12

CSEP75565                       a            2016-08-11

CSEP75565                        b              2016-09-12

 

I need ouput as

applicatoin_id              status    timestamp

CSE211143                  a x b    2016-10-11 2016-12-11 2016-09-12  

CSEP75565                   a b     2016-08-11 2016-09-12

    

 

I want  to make unique application_id as shown in the second output with combined status ( a x b ) in the same secquence as exists in the source ( I do not want 'a b x' nor 'x b a..'.but a x b )

The same with timestamp ..in the same sequence.

 

Need help here ..thanks all 

 

3 REPLIES
Senior Supporter

Re: Need help with sql

how do you determine the order of a x b?

 

CSE211143 a 2016-10-11

CSE211143 x 2016-12-11

CSE211143 b 2016-09-12

 

The order of dates would indicate b a x or x a b

Junior Supporter

Re: Need help with sql

This answer is picked from Dnoeth post

http://community.teradata.com/t5/Database/Convert-a-column-into-a-comma-separated-list/m-p/1042

 

SELECT application_id,
TRIM(TRAILING ' ' FROM (XMLAGG(id_status|| ' '
ORDER BY timestamp
) (VARCHAR(10000))))new_column,
TRIM(TRAILING ' ' FROM (XMLAGG(timestamp|| ' '
ORDER BY timestamp
) (VARCHAR(10000)))) new_column1
FROM test
GROUP BY 1

Enthusiast

Re: Need help with sql

--May I (in spite of Dnoeth reputation ;-) ) kindly suggest another approach.
--This is speudo code (you might need to tweek it)
CREATE TABLE DB.Application (
application_id CHAR(9) NOT NULL
status VARCHAR(200) NOT NULL
aTimestamp VARCHAR(200) NOT NULL)
UNIQUE PRIMARY INDEX ( application_id );

CREATE TABLE DB.ApplicationTemp AS
(select T.application_id
,T.Status
,CAST(aTimestamp AS CHAR (8) FORMAT ('dd-mm-yyyy')) (CHAR(8)) AS aTimestamp
,ROW_NUMBER() OVER (PARTITION BY application_id ORDER BY aTimestamp ASC) AS rn
from <YourTable> T) with data;

INSERT INTO DB.Application
WITH RECURSIVE rec_test(parent, status, aTimestamp, LVL) AS
(
SELECT T.application_id, status, aTimestamp, LVL
FROM DB.ApplicationTemp T
WHERE T.rn=1
UNION ALL
SELECT T.application_id, rec_test.status || ' ' || T.status, rec_test.aTimestamp || ' ' || T.aTimestamp, LVL +1
FROM DB.ApplicationTemp T
INNER JOIN rec_test ON (application_id = parent AND T.rn = rec_test.LVL +1)
)
SELECT RT.parent, RT.Status, RT.aTimestamp
FROM rec_test RT
QUALIFY ROW_NUMBER() OVER(PARTITION BY RT.parent ORDER BY RT.LVL DESC) = 1;

Tags (1)