column values concatenation

[CreativeMind]

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,leftsi de,rightside
2 fronside,leftsi de
3 leftside,rights ide

------------
what can i do to concatenate column values??

[Robert Dunlop]

Re: column values concatenation

"CreativeMi nd" <aftab.pucit@gm ail.comwrote in message
news:68c6a238-5525-460e-8398-91fb6c6f0250@t1 8g2000prt.googl egroups.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,leftsi de,rightside
2 fronside,leftsi de
3 leftside,rights ide
>
------------
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(propertyi d 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=viewNa me +' ' + @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