Compare values in same column in Oracle PL/SQL

5 pts.
Tags:
Oracle
PL/SQL
I have a set of data in table. For example, let my data be:
id         col1
-------------
1          a
2           b
3           c
4            d
5           a
6            b
7            e
8            f
9           a
10          a
I want the output like:
parent_id child_ids    col_val     count
------------------------------------------------------
1                 1,5,9,10      a            4
2                 2,6              b             2
3                 3                  c            1
4                 4                 d             1
7                 7                  e            1
8                 8                  f             1
1

Answer Wiki

Thanks. We'll let you know when a new response is added.

Hi,

As my Collogue has good hands in sql queries so he helped me sort this out 

USE SQLEmployees;

GO

SET NOCOUNT ON;

 IF OBJECT_ID(‘dbo.data_set’) IS NOT NULL DROP TABLE dbo.data_set;

create table dbo.data_set (id int primary key, col1 varchar(10));

 go

insert into dbo.data_set values (1,’a’), (2,’b’),(3,’c’),(4,’d’),(5,’a’),(6,’b’),(7,’e’),(8,’f’),(9,’a’),(10,’a’)

;

WITH BASE_DATA AS

(

    select 

        DS.id

       ,ROW_NUMBER() OVER

            (

                PARTITION BY DS.col1

                ORDER BY     DS.id ASC

            ) AS Parent_RID

       ,DS.col1 AS Col_val

       ,COUNT(DS.id) OVER

            (

                PARTITION BY DS.col1

            ) AS [count]

    from dbo.data_set DS

)

SELECT

    BD.id

   ,BD.Col_val

   ,STUFF( (SELECT CONCAT(CHAR(44),BS.id)

            FROM BASE_DATA BS

            WHERE BD.Col_val = BS.Col_val

            FOR XML PATH(”),TYPE).value(‘.[1]’,’VARCHAR(100)’),1,1,”) AS Child_ids

   ,BD.[count]

FROM BASE_DATA BD

WHERE BD.Parent_RID = 1;

 

Try this one and you will get your required output easily.

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: