sql join and delimiter text

Hi,

I have a table with two columns  table1 = (id,name) and other table2 = (id,sequence,results)

I make a simple query like this

SELECT T1.NAME,T2.SEQUENCE,T2.RESULTS FROM TABLE2 T2 LEFT JOIN TABLE1 T1 ON T2.ID = T1.ID

Open in new window


this works well, my problem is the following. In table 2 i have some rows that in the id i have something like 2:3 or 4:7:8 or 9:2:1:4 and so on

I need for that case that the T1.NAME give me the individual results separate by comma. like name2,name3 or name4,name7,name8 or name9,name2,name1,name4. Assume that for each id in T1 the name is name + correspondingID. Any idea what i can do? I can use any database
LVL 1
joyacv2Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
Every database product has a way to create a comma separated list.

With current versions of Oracle there is a LISTAGG function that can return values up to 4000 characters in total length:
http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions089.htm#SQLRF30030

I cannot say how to do it in different databases.
joyacv2Author Commented:
Hi,

Can you explain how to apply the liistagg?
slightwv (䄆 Netminder) Commented:
It is a SQL function.  There are examples in the doc link I posted.

I'm still not completely sure I understand what you need.

If you can provide sample data and expected results we can probably provide a working example.
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

awking00Information Technology SpecialistCommented:
To reiterate slightwv's request, sample data with table structures and the expected results is a desirable way to provide the experts a means of clarifying the problem and testing their proposed solutions.
joyacv2Author Commented:
-- phpMyAdmin SQL Dump
-- version 4.2.10
-- http://www.phpmyadmin.net
--
-- Host: localhost:8889
-- Generation Time: Oct 01, 2015 at 08:50 PM
-- Server version: 5.5.38
-- PHP Version: 5.6.2

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

--
-- Database: `demo`
--

-- --------------------------------------------------------

--
-- Table structure for table `TABLE1`
--

CREATE TABLE `TABLE1` (
  `ID` text NOT NULL,
  `NAME` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `TABLE1`
--

INSERT INTO `TABLE1` (`ID`, `NAME`) VALUES
('1', 'SFDKSLJDF'),
('2', 'SKLFJSDFLSKJDF'),
('3', 'KLJSDLFSD'),
('4', 'SDLFKJSDFSDSZSSSS'),
('5', 'ELKEJDSSLKJ'),
('6', 'LDKJFDFSDD');

-- --------------------------------------------------------

--
-- Table structure for table `TABLE2`
--

CREATE TABLE `TABLE2` (
  `ID` text NOT NULL,
  `SEQUENCE` text NOT NULL,
  `RESULTS` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `TABLE2`
--

INSERT INTO `TABLE2` (`ID`, `SEQUENCE`, `RESULTS`) VALUES
('1', '989', '755'),
('2', '9876', '655'),
('3', '0', '0'),
('1', 'LKJ', 'LKJHJK'),
('2', 'LKJHHHH', 'IUIII'),
('3:4', 'KLJKJ', 'IOKLLKJ'),
('1:2:5', 'EEWLKJ', 'KLJKLJLKJJK');
slightwv (䄆 Netminder) Commented:
Where are the expected results?
joyacv2Author Commented:
id                       name
1                      SFDKSLJDF                              
2                     SKLFJSDFLSKJDF
3                     KLJSDLFSD
1                      SFDKSLJDF
2                      SKLFJSDFLSKJDF
3:4                  KLJSDLFSD,SDLFKJSDFSDSZSSSS
1:2:5                SFDKSLJDF,SKLFJSDFLSKJDF, ELKEJDSSLKJ
Mark GeerlingsDatabase AdministratorCommented:
Data that is organized like this, where some rows have multiple values "something like 2:3 or 4:7:8 or 9:2:1:4 and so on in the id" column is certainly *NOT* organized the way that SQL databases work best.  SQL statements work best when the data is normalized.  Whenever you don't follow data normalization rules (putting multiple values in a single column is an example of this) writing SQL statements to work with the data becomes *MUCH* more complex.
slightwv (䄆 Netminder) Commented:
I completely agree with the above post:  You have a bad design which requires more work than necessary to get SQL to play nice with it.

That said, this is what I came up with for Oracle.  The sequence column is very important so I hope it is unique across the repeating id column or it will produce 'bad' results.

If you have two of these:
insert into table2 (id, sequence, results) values('1', '989', '755');
insert into table2 (id, sequence, results) values('1', '989', '755');

The result will be a single row with the two id='1' values concatenated together.

--drop table table1 purge;
create table table1 (
   id varchar2(5),
   name varchar2(30)
);

 
insert into table1 (id, name) values('1', 'SFDKSLJDF');
insert into table1 (id, name) values('2', 'SKLFJSDFLSKJDF');
insert into table1 (id, name) values('3', 'KLJSDLFSD');
insert into table1 (id, name) values('4', 'SDLFKJSDFSDSZSSSS');
insert into table1 (id, name) values('5', 'ELKEJDSSLKJ');
insert into table1 (id, name) values('6', 'LDKJFDFSDD');

--drop table table2 purge;
create table table2 (
   id varchar2(5),
   sequence varchar2(10),
   results varchar2(30)
);

insert into table2 (id, sequence, results) values('1', '989', '755');
insert into table2 (id, sequence, results) values('2', '9876', '655');
insert into table2 (id, sequence, results) values('3', '0', '0');
insert into table2 (id, sequence, results) values('1', 'LKJ', 'LKJHJK');
insert into table2 (id, sequence, results) values('2', 'LKJHHHH', 'IUIII');
insert into table2 (id, sequence, results) values('3:4', 'KLJKJ', 'IOKLLKJ');
insert into table2 (id, sequence, results) values('1:2:5', 'EEWLKJ', 'KLJKLJLKJJK'); 
commit;

with mylist as (
	select id, sequence, rtrim(regexp_substr(id,'([[:alnum:]]*)(:)?',1,column_value),':') single_id
	from table2,
 	table(
  		cast(
  			multiset(select level from dual connect by level <= ((length(id)-length(replace(id,':'))))+1)
  			as sys.odcivarchar2list
  		)
  	)
)
select m.id, listagg(name, ',') within group(order by t1.id) result
from table1 t1 join mylist m on t1.id=m.single_id
group by m.id, m.sequence
/

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
joyacv2Author Commented:
perfecttttt
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.