Select to distinguish a value?

Posted on 2011-02-13
Medium Priority
Last Modified: 2012-05-11
I want to one line that is something like ths:

Model    Received_date   Shipped_Date
Ford       12 Jan 10            15 Jan 10

The problem is that both dates are "events" in the same table so I get

Model    Event_TYPE     Event_Date
Ford       Received          12 Jan 10
Ford       Shipped            15 Jan 10

Is there a way to select
Event_TYPE (Received)
Event_Date (Received)

then select

Event_type (Shipped)
Event_Date (Shipped)

Does this make sense?
Question by:swjtx99
  • 2
LVL 11

Expert Comment

ID: 34885545
you can try this

SELECT Model, MIN(CASE WHEN Event_TYPE = 'Received' THEN Event_Date END) AS Received_Date,
MIN(CASE WHEN Event_TYPE = 'Shipped' THEN Event_Date END) AS Shipped_Date
FROM TableName

Open in new window


SELECT T.Model, T.Event_Date AS Received_Date, S.Event_Date AS Shipped_Date
FROM TableName T
INNER JOIN (SELECT Model, Event_Date FROM TableName WHERE Event_TYPE = 'Shipped') S
ON T.Model = S.Model
WHERE T.Event_TYPE = 'Received'

Open in new window

LVL 50

Accepted Solution

Lowfatspread earned 2000 total points
ID: 34885941
sql 2005 onwards...

select model,received,shipped
 from yourtable as x
 pivot (max(event_date) for event_type in ('Received','Shipped')) as pvt
 order by 1

pre sql 2005

select model
     ,  max(case event_type when 'received' then event_date end) as Received
       ,  max(case event_type when 'shipped' then event_date end) as Shipped
from yourtable
group by model
order by 1

Author Comment

ID: 34893957
sorry for the delay in answering. Got knocked off.

Thanks for the help!

Author Closing Comment

ID: 34893958
Many thanks,

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how the fundamental information of how to create a table.

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question