Solved

Select to distinguish a value?

Posted on 2011-02-13
4
387 Views
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?
0
Comment
Question by:swjtx99
  • 2
4 Comments
 
LVL 11

Expert Comment

by:JoeNuvo
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
GROUP BY Model

Open in new window


or

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

0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 500 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
0
 

Author Comment

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

Thanks for the help!
0
 

Author Closing Comment

by:swjtx99
ID: 34893958
Many thanks,
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

697 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