T-SQL Teaser
-
I have the following Table
CustomerID, SomeString
1, 'SomeText A'
1, 'SomeText B'
1, 'SomeText C'
2, etc
I want a query that will transform it to come out like:
1, SomeText A SomeText B Sometext C
2, SomeText A SomeText B Sometext C
I would usually do soumething like:
Select a.SomeString + b.SomeString + c.SomeString
From MyTable a, MyTable b, MyTable c
Where a.CustomerID = b.CustomerID and a.CustomerID = c.CustomerID
Group By a.SomeString + b.SomeString + c.SomeString
Is there a better way of achieving the same outcome?
-
The best way is to do it on the client.
but you can perform this in a function.
-- =============================================
IF EXISTS (SELECT *
FROM sysobjects
WHERE name = N'fn_ConcatStrings')
DROP FUNCTION fn_ConcatStrings
GO
CREATE FUNCTION fn_ConcatStrings
(@ID int)
RETURNS varchar(500)
AS
BEGIN
declare @String varchar(500)
select @String = coalesce(@String,'') + SomeString + ','
from mytable
where [CustomerID] = @ID
if Right(@String,1) = ','
set @String = substring(@String,1,len(@String) -1)
return @String
END
GO
-- =============================================
-- Example to execute function
-- =============================================
SELECT customerid, dbo.fn_ConcatStrings(CustomerID)
From Mytable
Where CustomerID between 1 and 10
GO
-
Thanks for this good info -
Please explain what you mean when you say this is best done on the client? Do you mean this is best achieved by a programme calling on the database?
-
What I guess Ray means, is that SQL is not very well suited for formatting purposes - it's a data retrieving / manipulating setbased language, quite different from your 'normal' structured programming language. This kind of pivoting operation is just 'formatting', and it isn't quite what the database is supposed to do, thus it's 'better' to do it 'on the client'
Though this may seem to be contradicting with the arrival of SQL Server 2005, where you now have a PIVOT and UNPIVOT operator. These new operators may or may not solve this kind of problems in Transact SQL, it remains to be seen. Take them with a grain of salt, they won't solve all of the 'classic' pivot problems.
/Kenneth
-
Ray probably means that you should execute
select customerID, SomeString from mytable
and then put data on the desired form in your client program.
I was wondering if you have an additional column on your table that ensures uniqueness or maybe just uniqueness in each group? Is there always 3 occurencies of each customerID?
Added: Didn't see Kenneth's post before I posted this...
-
The table I have only has uniqueness by the group, and no there may be more or less than 3 occurrences, and I think this is where the problem lies.
So in this instance, when I try and run Ray's function, for 1 customerID I get 18 rows ( And the Somestring text is NULL)
-
OK, I figured out that the coalesce fails when the contents are null, so a isnull(...) seems to have fixed up the problem
-
Dimitri,
not sure how you mean that... but COALESCE is principally identical with ISNULL in that it replaces NULL value with some other. COALESCE allows several parameters while ISNULL only 2, but both should work the same in posted SQL. Also, you can achieve the same by adding a line with
SET @String = ''
right after the DECLARE @string... then you don't need COALESCE/ISNULL to resolve NULL value in an uninitialized variable. Another source of possible problems is, if the column you are concatenating ("SomeString" in the function) can contain NULLs. Then you should either use COALESCE(somestring, '') - or ISNULL(somestring,'') which gives the same result - or limit the query in WHERE clause so that it does not include rows with NULL value:
WHERE [CustomerID] = @ID AND SomeString IS NOT NULL
The inside part of the function would then be:
BEGIN
DECLARE @String varchar(500)
SET @String = ''
SELECT @String = @String + mytable.SomeString + ','
FROM mytable
WHERE mytable.CustomerID = @ID AND mytable.SomeString IS NOT NULL
IF Right(@String,1) = ','
SET @String = substring(@String,1,len(@String) -1)
RETURN @String
END
HTH, Vladan
-
Yes Vladan, the problem was that somestring contained Null values in some of the records
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply