Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Partial Distinct and converting rows to columns

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Rajesh Haridoss

New User


Joined: 22 Feb 2014
Posts: 16
Location: India

PostPosted: Sat Jul 26, 2014 5:22 pm    Post subject: Partial Distinct and converting rows to columns
Reply with quote

Hi Everyone,
Good morning. I have a requirement like I need to remove duplicates for all columns except the last one which is always distinct. The last column values should come in a single row (but can be in multiple columns/can be concatenated into a single column) in the resultant set. The distinct values possible for the last columns are fixed which are S1,S2 & S3.

Please find below my input and the expected output.

Input:
--------
Col1 Col2 Col3 Col4
----- ----- ----- ------
M1 a b S1
M1 a b S2
M1 a b S3
M2 c d S1
M2 c d S2
M3 e f S1

I need the output in any of the two ways mentioned below

Output Way 1 :
-----------------

Col1 Col2 Col3 Col4 Col5 Col6
---- ----- ------ ----- ----- ------
M1 a b S1 S2 S3
M2 c d S1 S2
M3 e f S1

Output Way 2:
-------------------
Col1 Col2 Col3 Col4
---- ----- ------ -------------
M1 a b S1,S2,S3
M2 c d S1,S2
M3 e f S1

Thanks in advance for your help.
Back to top
View user's profile Send private message

gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Sun Jul 27, 2014 1:32 pm    Post subject:
Reply with quote

try exploring XMLAGG function.
Back to top
View user's profile Send private message
Rajesh Haridoss

New User


Joined: 22 Feb 2014
Posts: 16
Location: India

PostPosted: Sun Jul 27, 2014 7:41 pm    Post subject: Reply to: Partial Distinct and converting rows to columns
Reply with quote

Hi Bharat, thanks for your reply . XMLAGG function returns the result in XML format and XML data type. But I need resultset like I mentioned above.
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1610
Location: NY,USA

PostPosted: Sun Jul 27, 2014 9:03 pm    Post subject:
Reply with quote

Did you try with group by on first 3 columns and something (char( case expression only on col4)).
Back to top
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Sun Jul 27, 2014 10:23 pm    Post subject:
Reply with quote

you can type cast the result...
Back to top
View user's profile Send private message
Rajesh Haridoss

New User


Joined: 22 Feb 2014
Posts: 16
Location: India

PostPosted: Sun Jul 27, 2014 11:22 pm    Post subject: Reply to: Partial Distinct and converting rows to columns
Reply with quote

Rohit , I could not get the point. Could you please give me an example so that I can understand better.
Back to top
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Mon Jul 28, 2014 4:57 pm    Post subject: Reply to: Partial Distinct and converting rows to columns
Reply with quote

Method1:
Code:

WITH TBL1(col1,col2,col3,col4)
AS
( SELECT 'M1','a','b','S1' FROM SYSIBM.SYSDUMMY1 UNION ALL
  SELECT 'M1','a','b','S2' FROM SYSIBM.SYSDUMMY1 UNION ALL
  SELECT 'M1','a','b','S3' FROM SYSIBM.SYSDUMMY1 UNION ALL
  SELECT 'M2','c','d','S1' FROM SYSIBM.SYSDUMMY1 UNION ALL
  SELECT 'M2','c','d','S2' FROM SYSIBM.SYSDUMMY1 UNION ALL
  SELECT 'M3','e','f','S1' FROM SYSIBM.SYSDUMMY1
),
t1(rowNum,col1,col2,col3, col4) AS
    ( SELECT rownumber() over(partition by col1,col2,col3 order by col4),
    col1,col2,col3,col4
    FROM tbl1 ),
    t2(col1,col2,col3, list, cnt) AS
    ( SELECT col1,col2,col3, VARCHAR(col4, 50), 1
    FROM t1
    WHERE rowNum = 1
    UNION ALL
    SELECT t2.col1,t2.col2,t2.col3, t2.list || ', ' || t1.col4,
    t2.cnt + 1
    FROM t2, t1
    WHERE t2.col1 = t1.col1 AND
    t2.col2 = t1.col2 AND
    t2.col3 = t1.col3 AND
    t2.cnt + 1 = t1.rowNum )
SELECT col1,col2,col3, list
FROM t2
WHERE ( col1,col2,col3, cnt ) IN (
    SELECT col1,col2,col3,
    MAX(rowNum)
    FROM t1
    GROUP BY col1,col2,col3 )
ORDER BY 1


Method 2:
Code:

WITH TBL1(col1,col2,col3,col4)
AS
( SELECT 'M1','a','b','S1' FROM SYSIBM.SYSDUMMY1 UNION ALL
  SELECT 'M1','a','b','S2' FROM SYSIBM.SYSDUMMY1 UNION ALL
  SELECT 'M1','a','b','S3' FROM SYSIBM.SYSDUMMY1 UNION ALL
  SELECT 'M2','c','d','S1' FROM SYSIBM.SYSDUMMY1 UNION ALL
  SELECT 'M2','c','d','S2' FROM SYSIBM.SYSDUMMY1 UNION ALL
  SELECT 'M3','e','f','S1' FROM SYSIBM.SYSDUMMY1
)
select col1,col2,col3,
        replace(
        replace(
        replace(
           CAST(xmlserialize(XMLAGG(XMLELEMENT(NAME "x", col4) ) as CLOB(1000)) AS VARCHAR(1000))
            , '</x><x>', ',')
            , '<x>', '')
            , '</x>', '') as ids
    from TBl1
    group by col1,col2,col3;
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1610
Location: NY,USA

PostPosted: Tue Jul 29, 2014 2:20 am    Post subject:
Reply with quote

Please test below,

Code:
Input:
--------
Col1 Col2 Col3 Col4
----- ----- ----- ------
M1   a      b     S1
M1   a      b     S2
M1   a      b     S3
M2   c      d     S1
M2   c      d     S2
M3   e      f     S1

Code:
select main.col1,main.col2,main.col3,
       case when main.S1 = 1 then 'S1' else ' ' end as col4,
       case when main.S2 = 2 then 'S2' else ' ' end as col5,
       case when main.S3 = 3 then 'S3' else ' ' end as col6
from
(select col1,col2,col3,
        sum(case when col4 = 'S1' then 1 else 0 end) as S1,
        sum(case when col4 = 'S2' then 2 else 0 end) as S2,
        sum(case when col4 = 'S3' then 3 else 0 end) as S3       
from table a
group by col1,col2,col3) main
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1610
Location: NY,USA

PostPosted: Tue Jul 29, 2014 2:39 am    Post subject:
Reply with quote

Another way,
Code:
select col1,col2,col3,
  replace(max(case when col4 = 'S1' then 'Y' else ' '  end),'Y','S1') as col4,
  replace(max(case when col4 = 'S2' then 'Y' else ' '  end),'Y','S2') as col5
  replace(max(case when col4 = 'S3' then 'Y' else ' '  end),'Y','S3') as col6,     
from table a
group by col1,col2,col3
Back to top
View user's profile Send private message
Rajesh Haridoss

New User


Joined: 22 Feb 2014
Posts: 16
Location: India

PostPosted: Wed Jul 30, 2014 11:10 am    Post subject: Reply to: Partial Distinct and converting rows to columns
Reply with quote

All your methods work well. Bharat's XMLAGG method and Rohit's replace(max()) are very easy to use and are efficient too.
Comparatively XMLAGG method retrieves the result faster than replace(max()) method.
I appreciate your help. Thank you very much both of you icon_smile.gif .
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1610
Location: NY,USA

PostPosted: Thu Jul 31, 2014 2:28 am    Post subject:
Reply with quote

You are Welcome..
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
No new posts Row-Numbers of distinct rows? Auryn DB2 1 Thu Oct 20, 2016 4:38 pm
No new posts Comparing Decimal and CHAR columns rakesh17684 DB2 7 Thu Oct 20, 2016 2:33 am
No new posts Converting multiple VB files to FB fi... Viswanath Reddy JCL & VSAM 6 Mon Aug 08, 2016 11:49 pm
No new posts Converting NULL column into NOT NULL ... Raghu navaikulam DB2 5 Sat Aug 06, 2016 3:45 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us