Formatting a SQL query using a function
I have a SELECT query that returns multiple rows for one record due to a group in a field that has many different results, eg.
ID Name x
1 test a
1 test b
1 test c
How do I create a function that takes the current record ID in a select statement and returns all x values in one field?
+2
source to share
4 answers
There is no good set of ways to do this. You will need to use a cursor or some other form of iterating over the result set to concatenate it into one field.
You didn't mention the database server, if your sql server 2005 or higher pivot function can help you, I am not very fluent in using it, but here is the link
+1
source to share
Create a function like this:
CREATE FUNCTION GetValues (@id INT) RETURNS NVARCHAR(1000) AS
BEGIN
DECLARE mycursor
CURSOR READ_ONLY
FOR SELECT x
FROM MyTable
WHERE id = @id
DECLARE @x NVARCHAR(100)
DECLARE @return_value NVARCHAR(1000)
SET @return_value = ''
OPEN mycursor
FETCH NEXT FROM mycursor INTO @x
WHILE @@fetch_status = 0
BEGIN
SET @return_value = @return_value + @x
FETCH NEXT FROM mycursor INTO @x
IF (@@fetch_status = 0) SET @return_value = @return_value + ', '
END
CLOSE mycursor
DEALLOCATE mycursor
RETURN @return_value
END;
Then your request:
SELECT id, GetValues(id) as x
FROM MyTable
0
source to share
Look. Using XML path, should work in 2000/2005/2008, may be deprecated in 2010
set nocount on;
declare @t table (id int, name varchar(20), x char(1))
insert into @t (id, name, x)
select 1,'test1', 'a' union
select 1,'test1', 'b' union
select 1,'test1', 'c' union
select 2,'test2', 'a' union
select 2,'test2', 'c' union
select 3,'test3', 'b' union
select 3,'test3', 'c'
SELECT p1.id, p1.name,
stuff((SELECT ', ' + x
FROM @t p2
WHERE p2.id = p1.id
ORDER BY name, x
FOR XML PATH('') ), 1,2, '') AS p3
FROM @t p1
GROUP BY
id, name
It returns
1 test1 a, b, c
2 test2 a, c
3 test3 b, c
0
source to share
In SQL Server create function
CREATE FUNCTION [dbo].[GetX]
(
@id INT
)
RETURNS NVARCHAR(1024)
AS
BEGIN
DECLARE @AllInX NVARCHAR(1024)
SELECT @AllInX = ISNULL(@AllInX + ', ', '') + LTRIM(RTRIM(X))
FROM NamesAndX
WHERE ID = @id
RETURN @AllInX
END
And your request will be
SELECT ID
, Name
, dbo.GetX(ID)
FROM Names
0
source to share