Concatenating in select statement?
-
Hi all,
I have the following table:
Table1([ID] int,[Name] varchar(255))
This table simply stores a unique ID for each row and a name. The name CAN be duplicated for different records, so for example, ID's 1 and 2 can both have the name 'Test'.
Now, what I want is to create a query that will return a table with two columns: Table2([Name] varchar(255), [ID_List] varchar(255))
I want this table to display all distinct [Name]s in one column and a list of IDs in the next column which have that name. So for example, from the same data discussed above for Table1, Table2 will have: ('Test', '1,2').
So basically I want to have the ID's concatenated into a single string stored in the second column.
Anyone have a clue how to do this?
Thanks
Paul
-
There must be dosen clues just in this forum for the last week.
Would you bother to browse forum first?
_____________
Code for TallyGenerator -
If I could find what you're referring to, then I wouldn't post my message. Obviously I did not find it.
Thanks for the lecture anyway.
-
do you want that in single query?
This cabe easily be achived by using cursors..
Thanks
Brij
-
Declare @Name Varchar(255)
Declare @ID_LIST Varchar(255)
Declare @Temp Table
(
Name varchar(255),
ID_LIST Varchar(255)
)
Declare cur1 scroll cursor for Select name from table1 group by Name
Open cur1
Fetch cur1 into @Name while @@Fetch_Status =0
Begin
Set @ID_LIST=null
Select @ID_LIST = COALESCE(@ID_LIST + ', ', '') + CAST(ID as varchar) FROM Table1 where name =@Name
Insert into @Temp Values (@Name, @ID_LIST)
Fetch cur1 into @Name
End
Close cur1
Deallocate cur1
Select * from @temp
Brij
-
We should probably look into creating a sticky post with a FAQ. This question, in particular, seems to come up two or three times a week.
CREATE FUNCTION ConcatID (@vName varchar(50))
RETURNS varchar(50)
AS
BEGIN
DECLARE @myString varchar(50)
SET @myString = ''
SELECT @myString = @myString + CAST(Table1.ID AS Varchar(10)) + ','
FROM Table1
WHERE Table1.[Name] = @vName
RETURN @myString
END
SELECT [Name], ConcatName([Name]) AS IDList
FROM Table1
GROUP BY [Name]
-
There must be dosen clues just in this forum for the last week. Yep and even longer than that
But finding them isn't easy
Marshall beat me to this, but my solution anyway
CREATE FUNCTION dbo.udf_test (@Name varchar(255))
RETURNS varchar(255)
AS
BEGIN
DECLARE @result varchar(255)
SELECT @result = COALESCE(@result + ',' + CAST([ID] as varchar), CAST([ID] as varchar))
FROM [Table1]
WHERE [Name] = @Name
ORDER BY [ID]
RETURN @result
END
SELECT [Name], dbo.udf_test([Name])
FROM (SELECT [Name] FROM [Table1] GROUP BY [Name]) a
ORDER BY [Name] ASC
Far away is close at hand in the images of elsewhere.
Anon. -
SELECT [Name], dbo.udf_test([Name])
FROM (SELECT [Name] FROM [Table1] GROUP BY [Name]) a
ORDER BY [Name] ASC
David,
Why did you do an Alias here? You didn't have a reference to it in the select. Is this necessary, or just good general syntax?
-
Why did you do an Alias here? SQL Server requires sub queries to be aliased, you will get an error otherwise
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply