asked on

SQL Select - Grouping Rows

I need help with a SQL Select Script that will remove duplicates and combine values. I have a table with two columns. Right now each ID is duplicated by the validity. I just need need to combine all validity values and separate by a ";".

ID Validity
------ -----------
12A Product
12A Family
12A FO
31C Product
87A FO
87A Product

Expected Output:

ID Validity
------ -----------
12A Product; Family;FO
31C Product
87A FO; Product

Dirk Strauss

Have a look at this thread: GROUP BY to combine/concat a column.

Michael Cripps

What database system are you using ? Every system has its' own flavor of SQL. I can cook something for you quick, but I am not sure we are using the same system.

PortletPaul

tmajor99

I notice you have asked questions on Oracle and SQL Server in the past, and the way those 2 products do what you want are radically different. PLEASE always indicate what database you want the query for.

ORACLE

select id, listagg(validity,';') within group (order by validity) as validity
from table1
group by id
order by id

Open in new window

MS SQL Server

select
    id, ca.validity
from table1 t1
cross apply (
        SELECT
        STUFF((
              SELECT
                    ';' + validity
              FROM table1 t2
              WHERE t1.id = t2.id
              ORDER BY validity
              FOR XML PATH ('')
              )
             , 1, 1, '')
       ) ca (validity)
group by 
      id, ca.validity

Open in new window

MySQL

select id, group_concat(validity ORDER BY validity SEPARATOR ';') as validity
from table1
group by id
order by id

Open in new window

ASKER CERTIFIED SOLUTION

Pawan Kumar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PortletPaul

why not ordered? please provide a reason i you making such a statement.

Pawan Kumar

If Order BY is added you will get

12A Family; FO; Product

But requirement is this

12A Product; Family; FO

PortletPaul

then, it would have been more correct to say: "if you wish to have an un-ordered list do not use order by"

(it reads like an instruction that should always be obeyed, and of course that isn't true)

all 3 of my examples assume the opposite, that tmajor99 would wish to have an ordered list.

Pawan Kumar

Yes the ideal way is to use ORDER BY ( ASC/DESC ) but this is a different case.

tmajor99

ASKER

Really nice!