Problems with the SQL query function
I am designing a website, and I’m having a problem with the SQL query function. My table looks like this:
Table Structure
Ref_ID Mem_ID
1001 1002
1001 1003
1001 1004
1002 1005
1003 1006
1003 1007
1003 1008
When I try the command “select * from table where ref_id='1001',” it returns 1002, 1003 and 1004. However, I want all the co-related data together. What can I do?
Software/Hardware used:
Software/Hardware used:
ASKED:
August 21, 2008 4:16 PM
UPDATED:
August 22, 2008 12:43 AM
Answer Wiki:
You have to write a function which strings all the values together.
<pre>CREATE FUNCTION ReturnMemIDs (@Ref_Id INT)
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @Output NVARCHAR(MAX)
SET @Output = ''
DECLARE @Mem_Id INT
DECLARE cur CURSOR FOR SELECT Mem_ID FROM YourTable WHERE Ref_Id = @Ref_Id
OPEN cur
FETCH NEXT FROM cur INTO @Mem_Id
WHILE @@FETCH_CURSOR = 0
BEGIN
SET @Output = @Output + CAST(@Mem_Id AS NVARCHAR(10)) + ', '
FETCH NEXT FROM cur INTO @Mem_Id
END
CLOSE cur
DEALLOCATE cur
SET @Output = SUBSTRING(@Output, 0, LEN(@Output)-2)
RETURN @Output
END
GO</pre>
This will take all the values and put them into a single value. You can then call the function.
<pre>SELECT dbo.ReturnMemIDs(1001)</pre>
Or
<pre>SELECT DISTINCT dbo.ReturnMemIDs(RefId)
FROM YourTable</pre>
To see all answers submitted to the Answer Wiki: View Answer History.
Check out my SQL Server blog “SQL Server with Mr Denny” for more SQL Server information.