Horizontal result of a select sentence



IBM's flagship relational database management system

Horizontal result of a select sentence

Postby vegafacundodaniel » Tue Oct 29, 2013 4:05 pm

Hello,

A question please.

I'd need to create a select sentence that shows me the result horizontally.

for example:
I have theses records in a table

Key Start Date End Date Number
1 01/10/2013 01/11/2013 5
1 02/11/2013 31/12/2013 3
1 01/01/2014 31/12/2016 1
2 01/10/2013 01/11/2013 5
2 01/01/2014 31/12/2016 1
3 01/01/2014 31/12/2016 1
4 01/10/2013 01/11/2013 5
4 02/11/2013 31/12/2013 3
4 01/01/2014 31/12/2016 1
4 01/01/2017 31/12/2018 1


I'd would like to show as result something like this:
1 01/10/2013 01/11/2013 5 02/11/2013 31/12/2013 3 01/01/2014 31/12/2016 1
2 01/10/2013 01/11/2013 5 01/01/2014 31/12/2016 1
3 01/01/2014 31/12/2016 1
4 01/10/2013 01/11/2013 5 02/11/2013 31/12/2013 3 01/01/2014 31/12/2016 1 01/01/2017 31/12/2018 1

Could anyone help me, please ?

Thanks in advance
vegafacundodaniel
 
Posts: 61
Joined: Tue Jul 20, 2010 4:27 pm
Has thanked: 1 time
Been thanked: 0 time

Re: Horizontal result of a select sentence

Postby Akatsukami » Tue Oct 29, 2013 8:42 pm

Why cannot these data be reformatted with your sort product?
"You have sat too long for any good you have been doing lately ... Depart, I say; and let us have done with you. In the name of God, go!" -- what I say to a junior programmer at least once a day
User avatar
Akatsukami
Global moderator
 
Posts: 1058
Joined: Sat Oct 16, 2010 2:31 am
Location: Bloomington, IL
Has thanked: 6 times
Been thanked: 51 times

Re: Horizontal result of a select sentence

Postby vegafacundodaniel » Wed Oct 30, 2013 3:59 pm

Thanks Akatsukami, but I've not idea how...
Some clue, please
vegafacundodaniel
 
Posts: 61
Joined: Tue Jul 20, 2010 4:27 pm
Has thanked: 1 time
Been thanked: 0 time

Re: Horizontal result of a select sentence

Postby Akatsukami » Wed Oct 30, 2013 4:14 pm

Look at this thread for some hints.
"You have sat too long for any good you have been doing lately ... Depart, I say; and let us have done with you. In the name of God, go!" -- what I say to a junior programmer at least once a day
User avatar
Akatsukami
Global moderator
 
Posts: 1058
Joined: Sat Oct 16, 2010 2:31 am
Location: Bloomington, IL
Has thanked: 6 times
Been thanked: 51 times

Re: Horizontal result of a select sentence

Postby alexm » Fri Dec 20, 2013 3:14 pm

Using XML scalar function, you might get your horizontal result.

The following query gives the idea; you would need to fine-tune it to fit your environment.
WITH T1 (KEY ,START_DATE ,END_DATE ,NUM)                               
AS (SELECT 1 ,'01/10/2013' ,'01/11/2013' ,5 FROM SYSIBM.SYSDUMMY1 UNION
    SELECT 1 ,'02/11/2013' ,'31/12/2013' ,3 FROM SYSIBM.SYSDUMMY1 UNION
    SELECT 1 ,'01/01/2014' ,'31/12/2016' ,1 FROM SYSIBM.SYSDUMMY1 UNION
    SELECT 2 ,'01/10/2013' ,'01/11/2013' ,5 FROM SYSIBM.SYSDUMMY1 UNION
    SELECT 2 ,'01/01/2014' ,'31/12/2016' ,1 FROM SYSIBM.SYSDUMMY1 UNION
    SELECT 3 ,'01/01/2014' ,'31/12/2016' ,1 FROM SYSIBM.SYSDUMMY1 UNION
    SELECT 4 ,'01/10/2013' ,'01/11/2013' ,5 FROM SYSIBM.SYSDUMMY1 UNION
    SELECT 4 ,'02/11/2013' ,'31/12/2013' ,3 FROM SYSIBM.SYSDUMMY1 UNION
    SELECT 4 ,'01/01/2014' ,'31/12/2016' ,1 FROM SYSIBM.SYSDUMMY1 UNION
    SELECT 4 ,'01/01/2017' ,'31/12/2018' ,1 FROM SYSIBM.SYSDUMMY1     
   )                                                                   
SELECT KEY                                                             
      ,STRIP(                                                         
        CAST(                                                         
         XMLSERIALIZE(                                                 
          XMLAGG(                                                     
           XMLTEXT(                                                   
                    START_DATE CONCAT ' '                             
             CONCAT END_DATE   CONCAT ' '                             
             CONCAT LTRIM(NUM) CONCAT ' '                             
           ) ORDER BY START_DATE                                       
          )                                                           
         AS CLOB(100) EXCLUDING XMLDECLARATION)                       
        AS VARCHAR(100))                                               
       ) AS DATA                                                       
FROM T1                                                               
GROUP BY KEY                                                           
FOR READ ONLY
User avatar
alexm
 
Posts: 35
Joined: Wed Oct 13, 2010 6:40 pm
Has thanked: 0 time
Been thanked: 0 time


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post