SQL query to select one of multiple rows from a table

Question

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

This will vary heavily depending on the RDBMS that you are using. — Lamak
– Lamak, Commented Oct 15, 2013 at 15:11
You'd probably want to add an AND A.STATUS <> B.STATUS, otherwise you'll just be self-joining rows on themselves and end up with DetailsOfY-DetailsOfY — Marc B
– Marc B, Commented Oct 15, 2013 at 15:16
The table has a lot more rows for NAMEs other than A. Adding this <> condition is omitting all other rows and still give me 2 rows out of which one is exactly i want. — Ninja
– Ninja, Commented Oct 15, 2013 at 15:26

Community · Accepted Answer · 2017-05-23 12:05:14Z

1

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.

edited May 23, 2017 at 12:05

CommunityBot

11 silver badge

answered Oct 15, 2013 at 23:13

WarrenT

4,54021 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

mayabelle · Accepted Answer · 2013-10-15 15:19:49Z

0

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.

answered Oct 15, 2013 at 15:19

mayabelle

10.1k10 gold badges40 silver badges59 bronze badges

2 Comments

Ninja Over a year ago

I have AS400 at backend. Not sure if this would help. Thanks.

WarrenT Over a year ago

No, this will not work on DB2 for i. Our syntax, and our support for XML is different. (On SQL Server, wouldn't your query put a hyphen in front of every description, whether concatenated or not?)

Collectives™ on Stack Overflow

SQL query to select one of multiple rows from a table

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related