Format an SQL Query using a function

Question

I have a SELECT query that returns multiple lines for the same record due to a group by on a field that has many different results e.g.

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 the select statement and returns all the values of x in one field?

Answer 1

do you have a sample list ?

Answer 2

There is no good set based way to do this. You would need to use a cursor or some other form of iteration over the result set to concatenate it into a single field.

You didn't mention you database server if its sql server 2005 or greater the pivot function may help you, I'm not very fluent in using it but here is a link

http://msdn.microsoft.com/en-us/library/ms177410.aspx

Answer 3

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;

Your query would then be:

SELECT id, GetValues(id) as x
FROM MyTable

Answer 4

See this. Uses 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

Answer 5

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 query will be

SELECT ID
 , Name
 , dbo.GetX(ID)
FROM Names