August 13, 2004 at 11:17 am
Hi,
I´ve a little problem, I don´t know how to solve it
have this table from a previus developer
tk_id tk_number tk_battery
6500 null null
6501 12345 6500
6555 67897 6500
6588 56431 6500
I need to concatenate the result of this query into another result column
select tk_number
from myTable
where tk_battery = '6500'
result:
tk_number
---------------------
12345
67897
56431
I wanted to get
TKtotal
'12345 - 67897 - 56431'
Thnk you
August 15, 2004 at 7:34 am
For strings, the only way I know is through a cursor or temp table. Here's the cursor version
DECLARE @rowStr varchar(20),
@totalStr varchar(2000)
DECLARE mytable_curs CURSOR FOR
SELECT tk_number from mytable
where tk_Battery = 6500
OPEN mytable_curs
fetch next FROM mytable_curs INTO @rowStr
SELECT @totalStr = isNull(@rowStr, '')
WHILE @@fetch_status = 0
begin
fetch next FROM mytable_curs INTO @rowStr
if @@fetch_status = 0
SELECT @totalStr = @TotalStr + ' - ' + isNull(@rowStr, '')
end
CLOSE mytable_curs
DEALLOCATE mytable_curs
SELECT TKtotal = @TotalStr
August 15, 2004 at 10:25 pm
Alternatively you could append the string in the select statement...
DECLARE @tk_total Varchar(2000)
SET @tk_total = ''
SELECT @tk_total = @tk_total +
CASE Len(@tk_total) WHEN 0 THEN '' ELSE
CASE WHEN tk_number IS NULL THEN '' ELSE ' - ' END
END
+ isNull(CAST(tk_number as Varchar) ,'')
FROM myTable
WHERE tk_battery = 6500
SELECT 'TKTotal' = @tk_total
HTH
August 16, 2004 at 4:54 am
You could also try the COALESCE function. I've found it handy for CSV building.
This assumes that tk_number is a varchar, you may have to CAST(tk_number as varchar) otherwise.
DECLARE @List varchar(2000)
SELECT @List = COALESCE(@List + ' - ', '') + tk_number --assume tk_number is a varchar
FROM myTable
WHERE tk_battery = 6500
SELECT @List
August 16, 2004 at 6:26 am
Hello ebufanio,
there is a nice way to do this using UDF (user defined function):
/*create test environment*/
create table testing(tk_id int, tk_number int, tk_battery int)
insert into testing values (6500, null, null)
insert into testing values (6501, 12345, 6500)
insert into testing values (6555, 67897, 6500)
insert into testing values (6588, 56431, 6500)
/*FUNCTION starts here*/
CREATE FUNCTION dbo.udf_concatenate(@battery int)
RETURNS VARCHAR(1024)
AS BEGIN
DECLARE @string VARCHAR(1024)
SELECT @string = ''
/*concatenates the string*/
SELECT @string = @string + CONVERT(VARCHAR(20),t.tk_number) + ' - '
FROM testing t
WHERE tk_battery = @battery
/*strips away the delimiter at the end of string*/
IF @string <> '' SELECT @string = LEFT(@string, LEN(@string)-2)
RETURN @string
END
/*end of function*/
/*call function*/
SELECT dbo.udf_concatenate(6500) AS [TKtotal]
I don't know your tables and their structure, so probably you'll have to tune it a bit and change the data types (I used INT in all fields for simplicity). Hope this helps (and hope I didn't mistype anything in SQL when making final corrections to the post).
August 17, 2004 at 5:45 am
Thank you all. Each answer help me a lot.
Kind Regards
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply