Combine multiple rows into single row

Combine multiple rows of data into single unique records
row  c1   c2   c3 c4
1        2   null  'a'   null
2        2   'b' null, null
3       3     nul, null, 'CC'

Result should be
  row   c1   c2   c3 c4
   1       2    'b'      'a'    'CC'
 
bsbainsAsked:
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.

BrandonGalderisiCommented:
I can see no clearly defined "RULE" that would join the records.  even c1 doesn't have the same value across all 3 columns.  What would you want to do if row 3 had a value for c2 or c3?
0
bsbainsAuthor Commented:
Then keep those records as well
0
BrandonGalderisiCommented:
But you haven't defined any rule that says why rows 1, 2 and 3 should be combined.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

bsbainsAuthor Commented:
I am combining the select queries together number of these and each select only selects conditioned rows and rest of the coulmns are null

Query1  condition
Union
query2  condition  

etc...
0
BrandonGalderisiCommented:
You have nothing to define why they should be combined though.  The problem PROBABLY needs tackled in the previous query, where you are currently doing your UNIONs.
0
bsbainsAuthor Commented:

select  raRAPID, ApprovalDate, EPR_ID, CPIN ,Site from
(SELECT raRAPID, a1.atData as ApprovalDate, null EPR_ID, null CPIN , null Site
FROM Table1 a1,
     Table2  Rap
where a1.atRAPID=Rap.raRAPID and      a1.atClass='SY' and a1.atCode='0000000033'
UNION
SELECT raRAPID, null ApprovalDate, a2.atData as EPR_ID,null CPIN , null Site  
FROM Table1  a2,
            Table3 am2,
      Table2   Rap
where Rap.raRAPID= a2.atRAPID  
and a2.atCode=am2.amCode and a2.atClass=am2.amClass and am2.amClass in ('EP') and am2.amName='EPRID'
UNION
SELECT raRAPID, null ApprovalDate, null EPR_ID, a3.atData as CPIN , null Site  
FROM Table1  a3,
     Table2   Rap
where a3.atRAPID=Rap.raRAPID and a3.atCode='0000000001'
UNION
SELECT raRAPID, null ApprovalDate, null EPR_ID, null CPIN ,a4.atData as Site
FROM Table1  a4,
     Table2  Rap
where a4.atRAPID=Rap.raRAPID and a4.atClass='LO' and a4.atCode='0000000044')as x

group by raRAPID, ApprovalDate, EPR_ID, CPIN ,Site
0
BrandonGalderisiCommented:
Is raRAPID in the table1 table?  Is it common between all tables?  You still aren't saying how each component should be related.
0
bsbainsAuthor Commented:
yes in table1 and table2

atData this fields has multiple type of data date, numbers, characters etc... that's main reason reusing that table through out a1...a2...a3...an
table 3 is realted through   atCode  and atClass which have multiple records  
0
BrandonGalderisiCommented:
You should write this with a single Table1 and multiple joins toe table2 and 3 where necessary.  That way your end result has ONE raRAPID from the source table1.
0

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
bsbainsAuthor Commented:
Thanks Brandon for looking into this!
0
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.