SQl Query

[Abdul Rahman Hamidy]

Dear All, I am looking to query followings in Sql 2005 table as Table A ID Name 1 A 1 B 1 C 2 D 2 E 2 F I want the result as ID Name 1 A,B,C 2 D,E,F any ideas

Abdul Rahaman Hamidy Database Developer Kabul, Afghanistan

[Blue_Boy]

You have to use cursor. check this.[^]

---

I Love T-SQL "Don't torture yourself,let the life to do it for you." If my post helps you kindly save my time by voting my post. www.aktualiteti.com

[Pranay Rana]

Following one of the solution work for you but there must be better solution than this SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: <Author,,Name> -- Create date: <Create Date, ,> -- Description: <Description, ,> -- ============================================= CREATE FUNCTION tableaValue ( @id int ) RETURNS varchar(50) AS BEGIN declare @name varchar(50) declare @tempString varchar(50) DECLARE tablea_cursor CURSOR FOR select name from tablea where id = @id -- Variable value from the outer cursor OPEN tablea_cursor FETCH NEXT FROM tablea_cursor INTO @name WHILE @@FETCH_STATUS = 0 BEGIN SELECT @tempString= isnull(@tempString,'') + ',' + @name FETCH NEXT FROM tablea_cursor INTO @name END CLOSE tablea_cursor DEALLOCATE tablea_cursor -- Return the result of the function RETURN @tempString END GO select dbo.tableaValue(id),id from tablea group by id

[Abdul Rahman Hamidy]

Thanks. I have used for Xml Path and I have done it in SQl 2005. but i want to do it only with pure T-Sql which I need the same thing in Ms Access also. any ideas for Ms Access 2003/2007

Abdul Rahaman Hamidy Database Developer Kabul, Afghanistan

[J4amieC]

You dont even need the cursor to build a CSV, you can use COALESCE

DECLARE @nameCsv VARCHAR(MAX)
SET @nameCSV = ''

SELECT @nameCsv = COALESCE(name + ',','') + @nameCsv
FROM tablea

SELECT LEFT(@nameCsv,LEN(@nameCsv)-1) AS NameCsv

[Pranay Rana]

its good but it nned it id wise see the question he need csv value which group by id i hope this will on id basis thanks for reply

[J4amieC]

I have no idea what you just said :confused:

[AprNgp]

how abt this ?

select b.id, b.name from TableA b
where b.id in (select distinct a.id from TableA a)

Apurv “Never trust a computer you can’t throw out a window.” (Steve Wozniak) “There are only two industries that refer to their customers as ‘users’.” (Edward Tufte)

[Abdul Rahman Hamidy]

Thanks for reply. the query returns my table as it is, not the output which I am searching for

Abdul Rahaman Hamidy Database Developer Kabul, Afghanistan

[AprNgp]

Oh .. sry .. I think 'cursors' can only solve this ...

Apurv “Never trust a computer you can’t throw out a window.” (Steve Wozniak) “There are only two industries that refer to their customers as ‘users’.” (Edward Tufte)