Hey guys,
I'm working with a database at the minute (not my own), and the two tables are as follows:
----------------------
Customer
----------------------
CustomerID Agent
1 55a
2 97t
3 87f
4 44g
5 63l
6 21s
----------------------
-----------------------------------
Details
-----------------------------------
ID DetailName DetailValue
1 FirstName John
1 LastName Smith
2 FirstName Jack
2 LastName Jones
3 FirstName Mary
3 LastName Ford
-----------------------------------
I want to Run a query that returns the following:
---------------------------------------------------------------
CustomerID LastName FirstName
---------------------------------------------------------------
1 Smith John
2 Jones Jack
3 Ford Mary
--------------------------------------------------------------
Currently, if I run the follwoing:
select ct.CustomerID
case
when dt.DetailName = 'FirstName' or dt.Detail = 'LastName' then dt.DetailValue
end as 'Details'
from Details dt
join Customer ct
on ct.CustomerID = dt.ID
where ct.CustomerID = '1'
and dt.Detail in ('FirstName','LastName')
I get:
---------------------------
CustomerID Details
---------------------------
1 John
2 Jack
3 Mary
1 Smith
2 Jones
3 Ford
---------------------------
If I modify it to:
select ct.CustomerID
case
when dt.DetailName = 'FirstName' then dt.DetailValue
end as 'First Name',
case
when dt.DetailName = 'LastName' then dt.DetailValue
end as 'Last Name'
from Details dt
join Customer ct
on ct.CustomerID = dt.ID
where ct.CustomerID = '1'
and dt.Detail in ('FirstName','LastName')
I get:
-------------------------------------------------------------
CustomerID FirstName LastName
-------------------------------------------------------------
1 John
2 Jack
3 Mary
1 Smith
2 Jones
3 Ford
-------------------------------------------------------------
Can anyone tell me the query I need to run to get a table like:
-------------------------------------------------------------
CustomerID LastName FirstName
-------------------------------------------------------------
1 Smith John
2 Jones Jack
3 Ford Mary
-------------------------------------------------------------
Thanks very much for any help!