Help with efficiency

[ronedavid]

I'm kind of new to sql and I'm wondering if there is a more efficient way to accomplish the following code:

DECLARE @csv VARCHAR(255)
DECLARE @ValueId int

--Doc Type
SELECT @ValueId = NULL
SELECT @ValueId = (SELECT valueid from ps_value where categoryid=@categoryid and guidelineid=@guidelineid and fieldname='DocType')
SELECT @csv = '~DT~'
select @csv = coalesce(@csv+',','') + fielddata
from ps_valuedata
where categoryid=@categoryid and guidelineid=@guidelineid and valueid=@ValueId

--PurposeTypeAlias
SELECT @ValueId = NULL
SELECT @ValueId = (SELECT valueid from ps_value where categoryid=@categoryid and guidelineid=@guidelineid and fieldname='PurposeTypeAlias')
SELECT @csv = @csv + '~PT~'
select @csv = coalesce(@csv+',','') + fielddata
from ps_valuedata
where categoryid=@categoryid and guidelineid=@guidelineid and valueid=@ValueId

--OccupancyTypeAlias
SELECT @ValueId = NULL
SELECT @ValueId = (SELECT valueid from ps_value where categoryid=@categoryid and guidelineid=@guidelineid and fieldname='OccupancyTypeAlias')
SELECT @csv = @csv + '~OT~'
select @csv = coalesce(@csv+',','') + fielddata
from ps_valuedata
where categoryid=@categoryid and guidelineid=@guidelineid and valueid=@ValueId

--PropertyTypeAlias
SELECT @ValueId = NULL
SELECT @ValueId = (SELECT valueid from ps_value where categoryid=@categoryid and guidelineid=@guidelineid and fieldname='PropertyTypeAlias')
SELECT @csv = @csv + '~PRT~'
select @csv = coalesce(@csv+',','') + fielddata
from ps_valuedata
where categoryid=@categoryid and guidelineid=@guidelineid and valueid=@ValueId

--LTV
SELECT @ValueId = NULL
SELECT @ValueId = (SELECT valueid from ps_value where categoryid=@categoryid and guidelineid=@guidelineid and fieldname='LTV')
SELECT @csv = @csv + '~LTV~'
select @csv = coalesce(@csv+',','') + fielddata
from ps_valuedata
where categoryid=@categoryid and guidelineid=@guidelineid and valueid=@ValueId

--CLTV
SELECT @ValueId = NULL
SELECT @ValueId = (SELECT valueid from ps_value where categoryid=@categoryid and guidelineid=@guidelineid and fieldname='CLTV')
SELECT @csv = @csv + '~CLTV~'
select @csv = coalesce(@csv+',','') + fielddata
from ps_valuedata
where categoryid=@categoryid and guidelineid=@guidelineid and valueid=@ValueId

--LoanAmount
SELECT @ValueId = NULL
SELECT @ValueId = (SELECT valueid from ps_value where categoryid=@categoryid and guidelineid=@guidelineid and fieldname='LoanAmount')
SELECT @csv = @csv + '~LA~'
select @csv = coalesce(@csv+',','') + fielddata
from ps_valuedata
where categoryid=@categoryid and guidelineid=@guidelineid and valueid=@ValueId

--CashOut
SELECT @ValueId = NULL
SELECT @ValueId = (SELECT valueid from ps_value where categoryid=@categoryid and guidelineid=@guidelineid and fieldname='CashOut')
SELECT @csv = @csv + '~CO~'
select @csv = coalesce(@csv+',','') + fielddata
from ps_valuedata
where categoryid=@categoryid and guidelineid=@guidelineid and valueid=@ValueId

--CashOutPct
SELECT @ValueId = NULL
SELECT @ValueId = (SELECT valueid from ps_value where categoryid=@categoryid and guidelineid=@guidelineid and fieldname='CashOutPct')
SELECT @csv = @csv + '~COP~'
select @csv = coalesce(@csv+',','') + fielddata
from ps_valuedata
where categoryid=@categoryid and guidelineid=@guidelineid and valueid=@ValueId

--CreditScore
SELECT @ValueId = NULL
SELECT @ValueId = (SELECT valueid from ps_value where categoryid=@categoryid and guidelineid=@guidelineid and fieldname='CreditScore')
SELECT @csv = @csv + '~CS~'
select @csv = coalesce(@csv+',','') + fielddata
from ps_valuedata
where categoryid=@categoryid and guidelineid=@guidelineid and valueid=@ValueId

--AUSType
SELECT @ValueId = NULL
SELECT @ValueId = (SELECT valueid from ps_value where categoryid=@categoryid and guidelineid=@guidelineid and fieldname='AUSType')
SELECT @csv = @csv + '~AUS~'
select @csv = coalesce(@csv+',','') + fielddata
from ps_valuedata
where categoryid=@categoryid and guidelineid=@guidelineid and valueid=@ValueId
RETURN @csv

I would greatly appreciate the help guys.

 

[donutman]

David,
Try doing the job in two parts: a view and an aggregate query. I think this maybe faster. The view is a highly restrictive inner join and it is run only once. Can you compare the speeds? Or are you concerned about the length of the written code and its ease of understanding? If you have to do the entire job in 1 stored procedure than you could create a temporary table from the view and query against that.
-Karl

TheView:
SELECT (case v.fieldname when 'DocType' then d.fielddata else '' end) as F1,
(case v.fieldname when 'PurposeTypeAlias' then d.fielddata else '' end) as F2,
(case v.fieldname when 'OccupancyTypeAlias' then d.fielddata else '' end) as F3, ...

FROM ps_value v inner join ps_valuedata d on v.valueid=d.valueid

WHERE categoryid=@categoryid and guidelineid=@guidelineid and v.fieldname in ('DocType', 'PurposeTypeAlias', 'OccupancyTypeAlias',...)

Now run an aggregate query on TheView as

Select @CSV='~DT~' + max(F1) + ',' + '~PT~' + max(F2) + ... from TheView

 

[donutman]

Oops! I forgot you can't include variables in a view. So you'll have to move the portion of the where clause for guidelineid=@guidelineid and categoryid=@categoryid to the query and include those two fields in the select portion of the view.
-Karl

 

[ronedavid]

Thanks for the help. I tried this and the speed is great but the data is not being formated correctly. Maybee this will give you a better understanding. The data is layed out in the following format:

Table A
-----------------------------------
CatId GlID VId Desc
1 897 1 DocType
1 897 2 PurposeType
1 897 3 PropertyType
1 897 4 LTV
1 898 1 DocType
1 898 2 DocType
1 898 3 CreditScore

Table B
-----------------------------------
CatId GlID Vid Desc
1 897 1 FULLDOC
1 897 1 Rate/Term
1 897 2 Purchase
1 897 2 Refi
1 897 2 C/O

The two table are joined by CatId,GlID,Vid

I need the function to return a string that contains for each record in Table A, I need the desc field concatenated for all the matchine records in Table B.

The string would look something like this:
'~DT~FULLDOC,Rate/Term~PT~Purache,Refi' and so forth. The DT and PT are just designators the tell me the position of the data for a new field within the string.

I hope this is more clear. I have been racking my head on this for a week now. I would appreciate any help.

Thanks

 

[donutman]

David,
The reason you're having trouble is that CatID and GlID are not a primary key in Table B. So your original query was not returning one row that you could set @CSV to. The solution has become more complex and depends on the full extent of multiple CatID/GlID entries you have in Table B and/or what choices you have in regard to rebuilding Table B. One kludgy approach would be to create multiple views of Table B so that you can seperate out the multiple rows of CatID/GlID. Then continue with the approach I gave you but use 3+ tables with inner joins. Then coalesce the fielddata. Good Luck, sorry I can't be of more help.
-Karl

 

[ronedavid]

Karl thank you very much for your help. I will see what I can do with this. Thanks again