Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2003
    Posts
    8

    Unanswered: Beginner prob: generating string

    Help!

    I'm new to T-SQL and trying to code a solve for the following problem, without a good idea of how I can make T-SQL jump through hoops. Here is the problem.

    I need to populate a single string with values from a series of possible fields.

    eg. a vehicle can be 4 wheel drive (4WD) and have air conditioning (Aircon).

    So for each stock record I want to test each of a range of values (all or none can be true) in a column for a match. Based on that, add to the output string (that would look like "4wd ac ab abs").

    So far I have :

    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_NULLS ON
    GO


    CREATE proc [dbo].[qry_EXPERIMENT_02-12-2003]

    --provides information for the 'notes' field of the results screen.
    --default value: NULL
    @notesString nvarchar (30) = NULL

    as

    SELECT
    tblStockExtra.StockID,


    (
    SELECT
    CASE WHEN tblExtra.ExtraHeader = '4WD' THEN '4WD ' END,
    CASE WHEN tblExtra.ExtraHeader = 'Aachk' THEN 'aa ' END,
    CASE WHEN tblExtra.ExtraHeader = 'Aircon' OR tblExtra.ExtraHeader = 'DualAircon' OR tblExtra.ExtraHeader = 'ClimateAirCon' THEN 'ac ' END,
    CASE WHEN tblExtra.ExtraHeader = 'ABS' THEN 'abs ' END,
    CASE WHEN tblExtra.ExtraHeader = 'AlloyWheel' OR tblExtra.ExtraHeader = 'FactoryAlloyWheel ' THEN 'aw ' END,
    CASE WHEN tblExtra.ExtraHeader = 'SRSAirBag' OR tblExtra.ExtraHeader = 'DualSRSAirBag' THEN 'ab ' END,
    CASE WHEN tblExtra.ExtraHeader = 'CD' OR tblExtra.ExtraHeader = 'CDShuttle' THEN 'cd ' END

    FROM tblExtra
    INNER JOIN tblStockExtra ON tblExtra.ExtraID = tblStockExtra.ExtraID
    INNER JOIN tblStock ON tblStockExtra.StockID = tblStock.StockID
    ) AS notes

    FROM tblStockExtra, tblExtra

    GROUP BY tblStockExtra.StockID, tblExtra.Extraheader

    ORDER BY tblStockExtra.StockID

    This doesn't work, as I assume a subquery can only return 1 value. I'm hoping someone knowledgable can see what I'm attempting to do and enlighten me!

    Do I need to be using Dynamic SQL?

    Many thanks for your thoughts and time,

    KC.

  2. #2
    Join Date
    Oct 2003
    Location
    Switzerland
    Posts
    140
    You are right, this will not work. It would be easier for us if you post :

    a) table descriptions of tblStock, tblExtra, tblStockExtra
    b) the foreign keys between those tables (that way we can have an idea about the relationships between them)
    c) some sample data
    d) an example of how you would like to output to be formatted

  3. #3
    Join Date
    Sep 2003
    Posts
    8
    Originally posted by cvandemaele
    You are right, this will not work. It would be easier for us if you post :

    a) table descriptions of tblStock, tblExtra, tblStockExtra
    b) the foreign keys between those tables (that way we can have an idea about the relationships between them)
    c) some sample data
    d) an example of how you would like to output to be formatted
    A) Many to many realtionship between tblStock and tblExtra with tblStockExtra as a bridging table
    B) Referential integrity not enforced (Don't ask! Its not mine )
    C)



    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblExtra]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
    drop table [dbo].[tblExtra]
    GO

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblStock]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
    drop table [dbo].[tblStock]
    GO

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblStockExtra]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
    drop table [dbo].[tblStockExtra]
    GO

    CREATE TABLE [dbo].[tblExtra] (
    [ExtraID] [int] NOT NULL ,
    [ExtraDesc] [nvarchar] (255) NULL ,
    [ExtraAbbrv] [nvarchar] (255) NULL ,
    [ExtraHeader] [nvarchar] (50) NULL ,
    [Group] [int] NULL
    ) ON [PRIMARY]
    GO

    CREATE TABLE [dbo].[tblStock] (
    [StockID] [int] NOT NULL ,
    ) ON [PRIMARY]
    GO

    CREATE TABLE [dbo].[tblStockExtra] (
    [StockExtraID] [int] IDENTITY (1, 1) NOT NULL ,
    [StockID] [int] NULL ,
    [ExtraID] [int] NULL
    ) ON [PRIMARY]
    SET IDENTITY_INSERT [dbo].[tblStockExtra] ON
    GO

    ALTER TABLE [dbo].[tblStockExtra] WITH NOCHECK ADD
    CONSTRAINT [PK_tblStockExtra] PRIMARY KEY NONCLUSTERED
    (
    [StockExtraID]
    ) ON [PRIMARY]
    GO

    INSERT INTO tblStock
    (StockID)
    VALUES (64);
    INSERT
    INTO tblStock(StockID)
    VALUES (65);
    INSERT
    INTO tblStock(StockID)
    VALUES (467);
    INSERT
    INTO tblStock(StockID)
    VALUES (487);
    INSERT
    INTO tblStock(StockID)
    VALUES (944);

    --4wd
    INSERT INTO tblExtra
    (ExtraID, ExtraDesc, ExtraAbbrv, ExtraHeader, [Group])
    VALUES (41,'4 wheel drive', '4WD', '4WD', 14)

    --aachk
    INSERT INTO tblExtra
    (ExtraID, ExtraDesc, ExtraAbbrv, ExtraHeader, [Group])
    VALUES (52, 'AA check', 'AA', 'Aachk', 17)

    --aircon
    INSERT INTO tblExtra
    (ExtraID, ExtraDesc, ExtraAbbrv, ExtraHeader, [Group])
    VALUES (8, 'air-conditioning', 'AC', 'AirCon', 2)
    INSERT INTO tblExtra
    (ExtraID, ExtraDesc, ExtraAbbrv, ExtraHeader, [Group])
    VALUES (9, 'climate air-conditioning', 'AAC', 'ClimateAirCon', 2)
    INSERT INTO tblExtra
    (ExtraID, ExtraDesc, ExtraAbbrv, ExtraHeader, [Group])
    VALUES (10, 'dual air condtioning', 'DAC', 'DualAirCon', 2)

    --abs
    INSERT INTO tblExtra
    (ExtraID, ExtraDesc, ExtraAbbrv, ExtraHeader, [Group])
    VALUES (39, 'antilock braking system', 'ABS', 'ABS', 12)

    --alloys
    INSERT INTO tblExtra
    (ExtraID, ExtraDesc, ExtraAbbrv, ExtraHeader, [Group])
    VALUES (27, 'alloy wheels', 'AW', 'AlloyWheel', 9)
    INSERT INTO tblExtra
    (ExtraID, ExtraDesc, ExtraAbbrv, ExtraHeader, [Group])
    VALUES (28, 'factory alloy wheels', 'FAW', 'FactoryAlloyWheel', 9)

    --airbags
    INSERT INTO tblExtra
    (ExtraID, ExtraDesc, ExtraAbbrv, ExtraHeader, [Group])
    VALUES (25, 'SRS airbag', 'AB', 'SRSAirBag', 8)
    INSERT INTO tblExtra
    (ExtraID, ExtraDesc, ExtraAbbrv, ExtraHeader, [Group])
    VALUES (26, 'dual SRS airbag', 'DAB', 'DualSRSAirBag', 8)

    --CD
    INSERT INTO tblExtra
    (ExtraID, ExtraDesc, ExtraAbbrv, ExtraHeader, [Group])
    VALUES (13, 'CD', 'CD', 'CD', 3)
    INSERT INTO tblExtra
    (ExtraID, ExtraDesc, ExtraAbbrv, ExtraHeader, [Group])
    VALUES (14, 'CD shuttle', 'CDS', 'CDShuttle', 3)

    INSERT INTO tblStockExtra
    (StockExtraID, StockID, ExtraID)
    VALUES (1112,64,8)
    INSERT INTO tblStockExtra
    (StockExtraID, StockID, ExtraID)
    VALUES (1113,64,12)
    INSERT INTO tblStockExtra
    (StockExtraID, StockID, ExtraID)
    VALUES (1115,64,23)
    INSERT INTO tblStockExtra
    (StockExtraID, StockID, ExtraID)
    VALUES (1116,64,15)
    INSERT INTO tblStockExtra
    (StockExtraID, StockID, ExtraID)
    VALUES (1117,64,60)
    INSERT INTO tblStockExtra
    (StockExtraID, StockID, ExtraID)
    VALUES (1118,64,7)
    INSERT INTO tblStockExtra
    (StockExtraID, StockID, ExtraID)
    VALUES (1118,64,59)
    INSERT INTO tblStockExtra
    (StockExtraID, StockID, ExtraID)
    VALUES (1119,65,8)
    INSERT INTO tblStockExtra
    (StockExtraID, StockID, ExtraID)
    VALUES (1120,65,39)
    INSERT INTO tblStockExtra
    (StockExtraID, StockID, ExtraID)
    VALUES (1121,65,14)
    INSERT INTO tblStockExtra
    (StockExtraID, StockID, ExtraID)
    VALUES (1122,65,23)
    INSERT INTO tblStockExtra
    (StockExtraID, StockID, ExtraID)
    VALUES (1123,65,26)
    INSERT INTO tblStockExtra
    (StockExtraID, StockID, ExtraID)
    VALUES (1124,65,16)
    INSERT INTO tblStockExtra
    (StockExtraID, StockID, ExtraID)
    VALUES (1125,65,15)
    INSERT INTO tblStockExtra
    (StockExtraID, StockID, ExtraID)
    VALUES (1126,65,43)
    INSERT INTO tblStockExtra
    (StockExtraID, StockID, ExtraID)
    VALUES (1127,65,7)
    INSERT INTO tblStockExtra
    (StockExtraID, StockID, ExtraID)
    VALUES (1128,65,12)
    INSERT INTO tblStockExtra
    (StockExtraID, StockID, ExtraID)
    VALUES (4357,467,7)
    INSERT INTO tblStockExtra
    (StockExtraID, StockID, ExtraID)
    VALUES (4358,467,8)
    INSERT INTO tblStockExtra
    (StockExtraID, StockID, ExtraID)
    VALUES (4359,467,11)
    INSERT INTO tblStockExtra
    (StockExtraID, StockID, ExtraID)
    VALUES (4465,487,7)
    INSERT INTO tblStockExtra
    (StockExtraID, StockID, ExtraID)
    VALUES (4466,487,8)
    INSERT INTO tblStockExtra
    (StockExtraID, StockID, ExtraID)
    VALUES (4467,487,11)
    INSERT INTO tblStockExtra
    (StockExtraID, StockID, ExtraID)
    VALUES (4468,487,67)
    INSERT INTO tblStockExtra
    (StockExtraID, StockID, ExtraID)
    VALUES (9188,944,7)
    INSERT INTO tblStockExtra
    (StockExtraID, StockID, ExtraID)
    VALUES (9189,944,8)
    INSERT INTO tblStockExtra
    (StockExtraID, StockID, ExtraID)
    VALUES (9190,944,12)
    INSERT INTO tblStockExtra
    (StockExtraID, StockID, ExtraID)
    VALUES (9191,944,16)
    INSERT INTO tblStockExtra
    (StockExtraID, StockID, ExtraID)
    VALUES (9192,944,15)
    INSERT INTO tblStockExtra
    (StockExtraID, StockID, ExtraID)
    VALUES (9193,944,20)
    INSERT INTO tblStockExtra
    (StockExtraID, StockID, ExtraID)
    VALUES (9194,944,23)
    INSERT INTO tblStockExtra
    (StockExtraID, StockID, ExtraID)
    VALUES (9195,944,28)
    INSERT INTO tblStockExtra
    (StockExtraID, StockID, ExtraID)
    VALUES (9196,944,64)
    INSERT INTO tblStockExtra
    (StockExtraID, StockID, ExtraID)
    VALUES (9197,944,41)


    D) I want the output as: "Extra1 Extra2 Extra3"

    Thanks for your help!

    KC.

  4. #4
    Join Date
    Oct 2003
    Location
    Switzerland
    Posts
    140
    As the list of extras linked to an item in StockId is unlimited, the list of extras is returned as a large string, seperated by a single character.

    First of all, create the following function

    Code:
    create function dbo.fGetOptionList (@CarId int, @Separator char(1))
    returns nvarchar(510)
    as 
    begin
    
      -- cursor that will browse throught the list of extra for the given car
    declare cOptionList 
    cursor for 
    select 
    options.extraabbrv as OptionAbbrv
    from tblstock as cars
    inner join tblstockextra as cars_options
     on cars.stockid = cars_options.stockid
    inner join tblextra as options
     on options.extraid = cars_options.extraid
    where Cars.stockid = @CarId
    
    declare @OptionAbbrv nvarchar(510)
    declare @AllOptions  nvarchar(510)
    
    open cOptionList
    fetch next from cOptionList into @OptionAbbrv
    while @@fetch_status = 0 
       begin
       set @AllOptions = rtrim(isnull(@AllOptions,'')) + rtrim(isnull(@OptionAbbrv,'')) + @Separator
       fetch next from cOptionList into @OptionAbbrv
       end
    close cOptionList
    deallocate cOptionList
    
    return @AllOptions
    
    end
    Afterwards, just :
    SELECT
    stockid,
    dbo.fgetoptionlist(stockid, ';') as OptionList
    FROM tblstock

    It will return :
    stockid OptionList
    ----------- ---------------
    64 AC;
    65 AC;ABS;CDS;DAB;
    467 AC;
    487 AC;
    944 AC;FAW;4WD;

    (5 row(s) affected)

  5. #5
    Join Date
    Sep 2003
    Posts
    8
    Wow!

    Thanks for your time and energy! I truly wasn't expecting the solution on a plate! Please accept my sincerest thanks, you've saved me a great deal of effort!

    I feel disappointed I can't reward you with a commendation as in other forums, but you've made my day!



    KC.

Posting Permissions

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