help with query...

need some help with a query.

i have a table with columns
contractnum
locnum
unitnum

there could be some contractnums that have more than one locnum,unitnum's. for xample :
3000,6,21
3000,6,22
3000,1,10
4000,6,78
etc

for those contractnums that have more than one record i need to concatenate the locnum,uitnum values...
i currently have

select contractnum,locationnum + '-' + unitnum as Unit from custunits

to bind to a datagrid...but my boss wants to show all the "unit"'s in one row rather than multiple rows for a single contract...

so i need something like
3000,6-21,6-22,1-10 ...etc
4000,6-78
can someone give me a hand pls..

also, its not necessary for me to use a single query..if this cannot be accomplished in a single query..a stored proc is just fine too

thanksHow many rows are we talking about here? Will you be returning a relatively small result set at any given tiime?

If so, you scould write a UDF which returned the data in the format you require. The downside: it would be called in-line, in the SELECT statement. So a resultset of, say, 20000 records would suffer. But if you're only returning 50 records at a time, should be doable...

If that would work, let me know and I'll post a solution.|||i dont think i will have more than 1000-1500 records at any time...and not all of them have multiple locnum/unitnums...just some of them..
i'd still lilke to see your suggestion though...

thanks pjmcb|||Your UDF:

CREATE FUNCTION [dbo].[udf_Test] (@.ContractNum INT)
RETURNS VARCHAR(8000) AS
BEGIN
DECLARE @.vc VARCHAR(8000)
SELECT @.vc = ISNULL(@.vc + ',' + locnum + '-' + contractnum, locnum + '-' + contractnum)
FROM myTable
WHERE ContractNum = @.ContractNum
RETURN @.vc
END

Your query (goes inside a sproc, of course):

SELECT ContractNum, ContractNum + ',' + dbo.udf_Test(ContractNum)
FROM myTable

Or something along those lines. Obviously, I have done no null checking or conversion of ints to varchars or anyhting else you might actually need to do with your data. But I've used UDFs in a similar fashion before, with much success...|||hi pjmcb..

i dont see any loop in the UDF...infact i tried the code and it returned null ( after i made some changes to the select stmt...)

i think i need to clarify my question..
i have some records like

contractnumlocationnumunitnum
-------------
3000621
3000622
3000512
412345
4123678
456756

i need to return something like

contractnum unit(s)
--------
30006-21,6-22,5-12
41234-5,6-78
45675-6

etc

so i should prbly use some cursor to loop around contractnums that have more than one record and concatenate their (locationnum+unitnum)..s ? dont know if i can use cursors in UDFs...

your UDF did give me some idea...will keep trying from my side...while you might have a better suggestion..

thanks|||You don't need a cursor or loop. The SELECT statement:

@.vc = ISNULL(@.vc + ',' + locnum + '-' + contractnum, locnum + '-' + contractnum)

...will build your string for you by concatenating all of the results of the query into one field (trust me, it works).

As to why it returns NULL: like I said, it might need a bit of tweaking.|||The following:

DROP TABLE TestTable
GO
CREATE TABLE TestTable(
ContractNum VARCHAR(20),
LocNumVARCHAR(20),
UnitNumVARCHAR(20)
)
GO
INSERT INTO TestTable
VALUES ('3000','6','21')
GO
INSERT INTO TestTable
VALUES ('3000','6','22')
GO
INSERT INTO TestTable
VALUES ('3000','5','12')
GO
DECLARE @.vc VARCHAR(2000)
SELECT @.vc=ISNULL(@.vc+','+LocNum+'-'+UnitNum,LocNum+'-'+UnitNum)
FROM TestTable
WHERE ContractNum = '3000'
PRINT @.vc
GO

Prints the following output when run in QA:

6-21,6-22,5-12

Which is in line with what you want. The select statement above should be basically what you need within your UDF, with a RETURN @.vc replacing the PRINT @.vc. Otherwise, it should work (as long as you make sure that all of your datatypes match up.|||yep works perfect...initially when i tried it threw an error at the isnull since it requires 2 parameters so i took off the isnull ;) but now i understand it ...

SELECT distinct (ContractNum ), dbo.fnretunits(ContractNum) as unit
FROM custunits
 group by contractnum
having count(Contractnum) > 1

am using the above query now to get contractnums having multiple records...

thanks a lot