Results 1 to 9 of 9

Thread: Query question

  1. #1
    Join Date
    Jan 2002
    Location
    Berlin
    Posts
    72

    Unanswered: Query question

    Hi, for preparing a csv-export I want to do the following:

    I have a table with x records and the following structure

    car-id (varchar)
    id (num) -->incremental from 1 for each car-id
    equipment (varchar)
    price (decimal)

    Example:
    1234567 1 equipment1 100
    1234567 2 equipment2 200
    1234567 3 equipment3 300
    1234568 1 equipment8 250

    One car-id can has n equipments, so currently n rows.

    I a csv-file it shall look like:

    car-id;equipment;price
    1234567;equipment1,equipment2,equipment3;100,200,3 00
    1234568;equipment8;250

    Before writing it into a csv-file I have to add by joining with another table over the car-id some further fields.
    I have no idea how to implement ? Can anybody help ?


    dajm

  2. #2
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393

    Re: Query question

    I'm working on this query...

  3. #3
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393

    Re: Query question

    This is a classic pb
    it's comes often in different type of pb
    (list of children, list of quantity, list of prices)



    Create this function


    CREATE Function ListOfEquipments (@CarID Varchar(100))
    RETURNS Varchar(100)
    As
    Begin
    Declare @List as VarChar(2000)

    Set @List=''
    Select @List = @List + ',' + RTrim(Equipment) From Cars Where RTrim(CarID)=RTrim(@CarID)

    Return (Left(@List,Len(@List)-1))
    End




    Then you can do


    Select Carid, dbo.ListOfEquipments(Carid) From Cars



    do the same type of function for the Prices...

  4. #4
    Join Date
    Jan 2002
    Location
    Berlin
    Posts
    72

    Re: Query question

    Thx a lot for ur proposal. It is working nearly perfect.
    One more thing:

    It will return as many records as equipment is available for each car. A simple select distinct in the function call is not very speedy solution.
    Any ideas ?

    dajm

    Originally posted by Karolyn
    This is a classic pb
    it's comes often in different type of pb
    (list of children, list of quantity, list of prices)



    Create this function


    CREATE Function ListOfEquipments (@CarID Varchar(100))
    RETURNS Varchar(100)
    As
    Begin
    Declare @List as VarChar(2000)

    Set @List=''
    Select @List = @List + ',' + RTrim(Equipment) From Cars Where RTrim(CarID)=RTrim(@CarID)

    Return (Left(@List,Len(@List)-1))
    End




    Then you can do


    Select Carid, dbo.ListOfEquipments(Carid) From Cars



    do the same type of function for the Prices...

  5. #5
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393

    Re: Query question

    I don't see how you can get your result without the distinct...


    Select Distinct
    dbo.ListOfEquipments(CarId),
    dbo.ListOfPrices(CarId),
    CarId
    From Cars

  6. #6
    Join Date
    Jan 2002
    Location
    Berlin
    Posts
    72

    Re: Query question

    Any other function procedure possible ?
    My table has 15000 records and needs just for this more than 30 mins.
    This on a server with 4 GB RAM and 2 Xeon processors ... Can´t be real - or ?

    dajm


    Originally posted by Karolyn
    I don't see how you can get your result without the distinct...


    Select Distinct
    dbo.ListOfEquipments(CarId),
    dbo.ListOfPrices(CarId),
    CarId
    From Cars

  7. #7
    Join Date
    Jan 2002
    Location
    Berlin
    Posts
    72

    Re: Query question

    After 55 mins done. Two more problems:
    The field size is getting blasted (more than 255 chars) and the last char is getting cut off.
    How can I automatically create a further field like 'equipment2' to avoid overflow ?

    dajm

    Originally posted by Karolyn
    I don't see how you can get your result without the distinct...


    Select Distinct
    dbo.ListOfEquipments(CarId),
    dbo.ListOfPrices(CarId),
    CarId
    From Cars

  8. #8
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393

    Re: Query question

    you can do it with

    Min(Id) - Group By


    maybe it is faster

  9. #9
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393

    Re: Query question

    try this :

    Select
    dbo.ListOfEquipments(CarId),
    CarId
    From Cars
    Inner Join (Select Min(Id) Id From Cars Group By CarId) CarGroup
    on Cars.Id=CarGroup.Id

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •