Need help ordering records

[2eXtreme]

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!

[Paul Phillips]

Hiya

Details
-----------------------------------
ID DetailName DetailValue
1 FirstName John
1 LastName Smith
2 FirstName Jack
2 LastName Jones
3 FirstName Mary
3 LastName Ford
-----------------------------------

select
   DISTINCT Details.ID, 
   (Select DetailValue FROM Details D2 Where D2.ID = Details.ID AND DetailName = 'FirstName') as FirstName,
   (Select DetailValue FROM Details D2 Where D2.ID = Details.ID AND DetailName = 'LastName') as LastName
from
   Details

That should give you what you want.

Regards,

Paul

[gvalip]

Hey I did the following using Sybase database -

create table test_cust
(
    custid int,
    agentid int
)

grant all on test_cust to public 

create table test_details
(
    custid int,
    detailname varchar(15),
    detailvaue varchar(15)
)

grant all on test_details to public

I then inserted records you mentioned above except for agent id which I made numeric, and executed following query to get the result you wanted -

select a.custid, b.detailvaue, a.detailvaue
from test_details a, test_details b
where a.custid = b.custid
and a.detailname = 'FirstName'
and b.detailname = 'LastName'

Let me know if it resolve your problem.

[gvalip]

I just realized that you wanted customer id from first table, this should help in this case -

select x.custid, b.detailvaue, a.detailvaue
from test_details a, test_details b, test_cust x
where a.custid = b.custid
and a.custid=x.custid
and a.detailname = 'FirstName'
and b.detailname = 'LastName'

[2eXtreme]

Hey guys,

Thanks very much for all your replies, your solutions worked! :D

I was hoping someone could help me further, one query I run is to retrieve car rentals. Lets say, my current retrival of car details is as follows:

Colour......Make...........Model
-----------------------------------
Black........Ford............ModelA
Black........Ford............ModelA
Black........Ford............ModelA
Blue..........Audi............ModelB
Blue..........Audi............ModelB
Red ..........Audi............ModelC

Is there any way I can add an extra column to display the number of instances of that car? The reason for duplicate entries is because different people have rented that car...So I'd want something to look just like this:

Colour......Make...........Model......#Rented
------------------------------------------------
Black........Ford............ModelA.....3
Black........Ford............ModelA.....3
Black........Ford............ModelA.....3
Blue..........Audi............ModelB.....2
Blue..........Audi............ModelB.....2
Red ..........Audi............ModelC....1

The number relates to the number entries of that row in the table...does that make sense?

The query I'm using is:

select bpspA.BPSP_VALUE as 'Colour', bpspB.BPSP_VALUE as 'Make', bpspC.BPSP_VALUE as 'Model' from MONTH cf join WEEK cy on cy.CY_MONTH_ID = cf.CF_MONTH_ID join CUST tcy on  tcy.TC_CUST_ID = cy.CUST_ID join RENT rn on rn.RN_RENT_ID = tcy.TS_RENT_ID join CARDET bpspA on bpspA.BPSP_RENT_ID = rn.RN_RENT_ID join CARDET bpspB on bpspB.BPSP_RENT_ID = bpspA.BPSP_RENT_ID join CARDET bpspC on bpspC.BPSP_RENT_ID = bpspB.BPSP_RENT_ID where cy.CY_USER_08 = 'Maine' and cy.CY_CYCLE = '001' and bpspA.BPSP_NAME = 'Colour' and bpspB.BPSP_NAME = 'Make' and bpspC.BPSP_NAME = 'Model'

Thanks for any help!

[tesuji]

Hi 2eXtreme
Try:

select Colour, Make, Model count(*) from cars group by Colour, Make, Model

krs,
tesu

[2eXtreme]

Hey,

Thanks for your reply, but I get the following error:

The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

[2eXtreme]

So I googled around a little and found out that I needed to cast my 3 details fields to varchars, e.g. cast(bpspA.BPSP_VALUE as varchar(255)) both in the select statement and in the group by clause.

So now, I want to pull in the person that rented the car on each occassion, but when I try to pull in data from another table, i get:

Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Whats the workaround for this?

Thanks again!

[tesuji]

correct error message because all attributes of select clause not being part of an aggregate function must also be listed in group by clause, e.g.
if you have

select x , sum (z) from ... group by x

and you add just another attribute

select x, y, sum(z) from... group by x

would cause an error

you need to write

select x, y, sum(z) from... group by x, y

yet, the following is correct

select x, sum(z), avg(y) from... group by x

because y and z are part of aggregate functions.

krs,
tesu

p.s. It would really be easier to give you better advice, if you post your create-table commands AND the select statement you have got an error.

[2eXtreme]

Hey thanks for your help, I just placed all my attributes into the select and the group by, so it's all good (if not quite lengthy!)

lol

Cheers for your help!

[2eXtreme]

hey guys, every day I run a query, that retrieves the past 28 days of results.

so my table looks like:

date..........results
01/01/08...45
01/02/08...23
01/03/08...4
01/04/08...6
01/05/08...66
01/06/08...5
01/08/08...4
etc

Notice how there isn't an entry for 01/07/08? Well, if there are no results for a particular day, my query doesn't return anything for that date, so the date is dropped from the results. How can I make it so, if there are no results for a date, the query includes the date, and enters a '0' beside it? e.g.:

01/01/08...45
01/02/08...23
01/03/08...4
01/04/08...6
01/05/08...66
01/06/08...5
01/07/08...0
01/08/08...4

I'm sorry but I don't have my query at hand at the minute, but say it does something like:

select numresults from results where date > now()-28 and date < now() -1

Has anyone encountered this type of problem before?

[2eXtreme]

Can anyone help me?

Thanks