Page 1 of 1

Horizontal result of a select sentence

PostPosted: Tue Oct 29, 2013 4:05 pm
by vegafacundodaniel
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

Re: Horizontal result of a select sentence

PostPosted: Tue Oct 29, 2013 8:42 pm
by Akatsukami
Why cannot these data be reformatted with your sort product?

Re: Horizontal result of a select sentence

PostPosted: Wed Oct 30, 2013 3:59 pm
by vegafacundodaniel
Thanks Akatsukami, but I've not idea how...
Some clue, please

Re: Horizontal result of a select sentence

PostPosted: Wed Oct 30, 2013 4:14 pm
by Akatsukami
Look at this thread for some hints.

Re: Horizontal result of a select sentence

PostPosted: Fri Dec 20, 2013 3:14 pm
by alexm
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