IBM Mainframe Forum

by **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

by **Akatsukami** » Tue Oct 29, 2013 8:42 pm

Why cannot these data be reformatted with your sort product?

by **vegafacundodaniel** » Wed Oct 30, 2013 3:59 pm

Thanks Akatsukami, but I've not idea how...
Some clue, please

by **Akatsukami** » Wed Oct 30, 2013 4:14 pm

Look at this thread for some hints.

by **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.

Select all

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

IBM Mainframe Forum

Horizontal result of a select sentence

Horizontal result of a select sentence

Re: Horizontal result of a select sentence

Re: Horizontal result of a select sentence

Re: Horizontal result of a select sentence

Re: Horizontal result of a select sentence