how to SQL Script

[youquijano]

hi,

i have this table

TEST_CODE | MASTNO
2ELEM | 1
2V/LS | 1
3ELEM | 1
4ELEM | 1
5ELEM |2
6ELEM |2
7ELEM |2
8ELEM | 2
ACCUL | 2
ACTV. | 3
ADDT | 3
AFB | 4
AG/RA | 4
AIDS | 4
AIDS2 | 4

is there a way to have a result set from sql to have a

select <dddd> from table

<dddd> = "2ELEM,2V/LS,3ELEM,4ELEM"

so on and so fort

[szlamany]

What is your backend - MS SQL - mySQL - what version?

[mendhak]

What does that mean?

<dddd> = "2ELEM,2V/LS,3ELEM,4ELEM"

Are you looking for WHERE fieldname IN (value1,value2,value3........)?

[youquijano]

What does that mean?

<dddd> = "2ELEM,2V/LS,3ELEM,4ELEM"

Are you looking for WHERE fieldname IN (value1,value2,value3........)?

what i mean here is that the result set will concat all the test_code with same mastno...

sql server 2000

[szlamany]

We have a table with several phone numbers for each master id - this function returns them in a single string

Use Funds
Go
DROP Function GetMasterPhone_F
Go
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
CREATE FUNCTION dbo.GetMasterPhone_F (@MasId int)
RETURNS varchar(100) AS  
BEGIN
	Declare @P_Work varchar(100)

	Declare @PTbl Table (CallSeq int, PhoneType varchar(2), Phone varchar(20))

	Insert into @PTbl Select CallSeq, PhoneType, dbo.GetPhone_F(Phone,'',0) From MasterPhone_T Where MasId=@MasId

	Declare @CS int, @PT varchar(2), @PH varchar(20)

	Select Top 1 @CS=CallSeq, @PT=PhoneType, @PH=Phone From @PTbl Order by CallSeq, PhoneType Desc

	While @@RowCount<>0
	Begin
		Set @P_Work=IsNull(@P_Work+', ','')+@PT+': '+@PH
		Delete From @PTbl Where @CS=CallSeq and @PT=PhoneType and @PH=Phone 
		Select Top 1 @CS=CallSeq, @PT=PhoneType, @PH=Phone From @PTbl Order by CallSeq, PhoneType Desc
	End

	RETURN @P_Work
END
GO
GRANT EXECUTE ON dbo.GetMasterPhone_F TO FundsUser
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

It is used like:

Select MasId,dbo.GetMasterPhone_F(MasId) From Master_T

[youquijano]

well in my vb code, i created a for loop to concat those test_code checking if their mastno is the same then doing an updated statement, is this much faster?

[szlamany]

Well...

The server can handle 2 or 3 or even 5 or 6 phone numbers with no effort.

And putting that in a user-defined function in the DB allows it to be used in queries all over the place - without you embedding it in client level or business layer logic.

Speed should not be of concern to you.

The issue should be where you desire to put this level of logic in your architecure.

MS SQL 2005 supports TABLE VALUE FUNCTIONS - basically a VIEW that uses SPROC like or UDF like T-SQL syntax like I just showed. We use that to evaluate the balance and payment methods for tax bills in towns and cities. Return a dozen or more columns of this type of data in what appears like a VIEW joined to the SELECT. On 10's of thousands of bills that JOIN to this TVF (calling logic much uglier then what I showed above) it is instantaneous.

Although there are those who would say that MS SQL Scalar functions should not do table I/O that is not the direction that MS is pushing us developers.

[youquijano]

Although there are those who would say that MS SQL Scalar functions should not do table I/O that is not the direction that MS is pushing us developers.

so it is just fine to use MS SQL for I/O operations as well...

[szlamany]

I think they only need to be understood for what their strong points are and where they are weak.

For instance - imagine using my MASTER table and GETPHONE function for a second - that you want to find everyone in the MASTER table with an area code of "(212)" - which is NYC.

The standard SQL way to do this using SET BASED LOGIC would be something like:

Select * From Master
   Where MasId in (Select MasId From MasterPhone
                         Where Phone like '%(212)%')

or

Select * From Master
   Where MasId in (Select MasId From MasterPhone
                         Where Left(Phone,5)='(212)')

You could do this many other ways - JOINS and INNER JOINS and what not.

But all of them are using a more conventional SET BASED method - one that the query optimizer can best create an execution plan for. Hopefully that plan will be to find the couple of hundred id's with (212) and then just get those from the MASTER table.

Now you come along and think to yourself - why not just use that function to see if they have a (212) area code?

Select * From Master
    Where dbo.GetPhone(MasId) like '%(212)%'

This is not a proper query. The GETPHONE function doesn't expose it's guts to the query engine the same way. SQL doesn't know what's in that function (as it should not) so it's going to call it for every row in the master table one painstakingly time after the other. Having to do all that "hidden" I/O - putting all those phones into a string - just to see if one of them had (212). It's not the I/O that's slow - it's what the execution plan that it yields.

Now this might not actually be how it executes but you get the idea.

Putting the SCALAR FUNCTION in a SELECT list is not expensive. The business requirement to get the phones put into a list separated by commas isn't going away. Either way it's getting done.

But using it in a WHERE clause - when better options exist - must be understood and considered.