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
tmajor99Asked:
Who is Participating?
 
Pawan KumarConnect With a Mentor Database ExpertCommented:
Please try this for SQL Server. Full Tested solution.

Do not use Order BY Before XML Path. The ordering is changing the output you need.

CREATE TABLE Combiner
(
	 ID VARCHAR(5)                
	,Validity VARCHAR(20)
)
GO

INSERT INTO Combiner VALUES
('12A','Product'),
('12A','Family'),
('12A','FO'),
('31C','Product'),
('87A','FO'),
('87A','Product')
GO

Open in new window


Solution - Do not use Order BY Before XML Path.

SELECT DISTINCT b.Id 
			, STUFF 
				((
					SELECT '; ' + a.validity 
					FROM Combiner a
					WHERE ( a.ID = b.ID )					
					FOR XML PATH('')
				) ,1,2,'') 
				AS validity
FROM Combiner b

Open in new window


Output

/*------------------------
OUTPUT
------------------------*/
Id    validity
----- ----------------------------------
12A   Product; Family; FO
31C   Product
87A   FO; Product

(3 row(s) affected)

Open in new window

0
 
Dirk StraussSenior Full Stack DeveloperCommented:
Have a look at this thread: GROUP BY to combine/concat a column.
0
 
Michael CrippsMicrosoft Access & Excel  Advanced /SQL Syntax ExpertCommented:
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.
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
PortletPaulfreelancerCommented:
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

0
 
PortletPaulfreelancerCommented:
why not ordered? please provide a reason i you making such a statement.
0
 
Pawan KumarDatabase ExpertCommented:
If Order BY is added you will get

12A   Family; FO; Product

But requirement is this

12A   Product; Family; FO
0
 
PortletPaulfreelancerCommented:
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.
0
 
Pawan KumarDatabase ExpertCommented:
Yes the ideal way is to use ORDER BY ( ASC/DESC ) but this is a different case.
0
 
tmajor99Author Commented:
Really nice!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.