[kc_casey]

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.

[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

[kc_casey]

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.

[cvandemaele]

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

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)

[kc_casey]

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.