• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1694
  • Last Modified:

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'
 
0
bsbains
Asked:
bsbains
  • 5
  • 5
1 Solution
 
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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

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

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now