Help in query
-
Hi All,
Here is my problem:
Create table #Temp(id TinyInt, Color varchar(20))
Insert into #temp values (1, 'Red')
Insert into #temp values (2, 'Green')
Insert into #temp values (3, 'Blue')
Now I want the result as: Red, Green, Blue
How can I do this, Please help........
Thanks a lot in advance.
-
-- sum must be <8000
Declare @result varchar(8000)
Select @result=''
Select @result=@result+Color+','
From #Temp
if Len (@result)>0
Select @result=Substring(@result,1,Len(@result)-1)
Select @result
-
I'm not sure why do you need to do things like that, but here you goi... If you use permanent table Colors (instead of temporary) with the same structure and data, you can do it with UDF - like this (credits for the short form with ISNULL go to Sergiy):
CREATE FUNCTION dbo.GetAllColors ()
RETURNS VARCHAR(1000)
AS
BEGIN
DECLARE @List VARCHAR(1000)
SELECT @List = ISNULL(@List+ ', ', '') + c.Color
FROM Colors c
ORDER BY [id]
RETURN @List
END
And this is how you call it:
SELECT dbo.GetAllColors()
-
Right, on a temptable you can go about it this way... but even here you can make the code shorter :
DECLARE @result varchar(8000)
SELECT @result=ISNULL(@result+ ',','') + c.Color
FROM #temp c
ORDER BY [id]
SELECT @result
-
Thanks to all of you.
But can I do this without using Variable.
Thanks again.
-
No, you can't, as far as I know.
Generally, such transformations are often easier done on the client side, not on the server with SQL. You didn't mention what do you need it for, but if you don't want to use variable, consider this option.
-
This should work:
Select
Max(Case Color When 'Red' Then 'Red' End) as Color1,
Max(Case Color When 'Green' Then 'Green' End) as Color2,
Max(Case Color When 'Blue' Then 'Blue' End) as Color3
From #Temp
sam
-
Well, of course... if we are talking about such possibilities, you could also do it this way :
SELECT 'Red, Green, Blue'
Problem is that the question wasn't really clear as to what is needed - just the result is not enough, some description of requirements would be helpful. I understood it as "get all values from column Color into one comma-delimited string"... but maybe I wasn't right.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply