CODEWITHSUNDEEP
sqljoindb2ibm-midrangedb2-400
Ninja
Ninja

Reputation: 183

SQL query to select one of multiple rows from a table

I have a table which contains more than one row for a particular value . Here is the table structure:

NAME,NUMBER,STATUS,DESC,START_DATE,END_DATE
A,3,X,DetailsOfX,13-10-15,13-10-15
A,2,Y,DetailsOfY,13-10-15,13-10-15
A,2,Z,DetailsOfZ,13-10-15,13-10-15
A,1,X,DetailsOfX,12-10-15,12-10-15

The output i need is i.e.

A,3,X,DetailsOfX,13-10-15,13-10-15
A,2,Y,DetailsOfY-DetailsofZ,13-10-15,13-10-15
A,1,X,DetailsOfX,12-10-15,12-10-15

So basically i want to select one of two or more rows from a table with data from columns from both the rows (in bold above). The query below i tried using JOIN returns 4 rows. 

SELECT A.NAME,A.NUMBER,B.STATUS,A.DESC||"-"||B.DESC,A.START_DATE,A.END_DATE
FROM TABLE A
JOIN (SELECT NUMBER,STATUS,DESC,START_DATE,END_DATE FROM TABLE WHERE NAME='A') B
ON A.NAME=B.NAME AND
A.NUMBER=B.NUMBER

Can somebody help me with the query that would work.

Thanks

Upvotes: 0

Views: 4142

Answers (2)

WarrenT
WarrenT

Reputation: 4532

If you are using IBM i 7.1 (formerly known as OS/400), you should be able do this with two tricks: hierarchical queries, and XML functions.

See my tutorial under Q: SQL concatenate strings which explains how to do this on DB2 for i in order to merge the descriptions.

GROUP BY any fields by which you would want rows combined into one, but all other columns must be the result of an aggregate function. So for example, if you want one row per name, number, but have various values for Status, StartDate, EndDate, then you will need to say something like min(Status), min(StartDate), max(EndDate). Is the minimum status code actually the one you want to report?

If your OS is at version 6.1, you may still be able to use a conventional recursive query (or under v5r4), but you might need an addtional CTE (or two?) to concatenate the descriptions.

Upvotes: 1

mayabelle
mayabelle

Reputation: 10014

You need to use GROUP BY and FOR XML PATH:

SELECT 
  X.NAME, X.NUMBER, X.STATUS,
  STUFF((
    SELECT '-' + [Desc] AS Desc 
    FROM YourTable Y
    WHERE Y.ID = X.ID
    FOR XML PATH(''),TYPE),1,1,'') AS DescValues,
  StartDate,
  EndDate
FROM YourTable X
GROUP BY Name, Number, Status, StartDate, EndDate

This is assuming you want separate rows for any differences in name, number, status, start date, or end date.

Also, this is assuming SQL Server.

Upvotes: 0

Related Questions