"CreativeMind" <af*********@gmail.comwrote in message
news:68**********************************@t18g2000 prt.googlegroups.com...
hi all , actually i am binding a datagrid with a dataview which has a
datatable showing output like
this..
propertyid viewname
1 fronside
1 lefttside
1 rightside..
2 frontside
2 leftside
3 rightside
3 leftside
i need the output like that..
propertyid viewname
1 fronside,leftside,rightside
2 fronside,leftside
3 leftside,rightside
------------
what can i do to concatenate column values??
One approach might be to make this an SQL problem, by writing a stored
procedure that creates a temporary table and concatenates the data.
-- create a temporary table to hold the results
CREATE TABLE #temp(propertyid INT, viewname VARCHAR(100))
-- copy unique property id values to temporary table
INSERT #temp SELECT DISTINCT propertyid, '' FROM myTable
-- set up a cursor to iterate through the source table
DECLARE @id INT, @name VARCHAR(50)
DECLARE c CURSOR FOR SELECT propertyid, viewname FROM myTable
OPEN c
-- get the first record
FETCH NEXT FROM c INTO @id, @name
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE #temp SET viewName=viewName +' ' + @name where propertyid = @id
FETCH NEXT FROM c INTO @id, @name
END
-- get the final results, trimming extra space from start
SELECT propertyid, LTRIM(viewname) as viewname FROM #temp
--
Robert Dunlop
----------------------
http://www.directxzone.org http://rdunlop.spaces.live.com
Microsoft DirectX MVP 1998-2006
