468,249 Members | 1,511 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,249 developers. It's quick & easy.

column values concatenation

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??
Oct 20 '08 #1
1 1657
"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
Oct 21 '08 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by graphicsxp | last post: by
34 posts views Thread by Larry Hastings | last post: by
6 posts views Thread by candide_sh | last post: by
reply views Thread by NPC403 | last post: by
reply views Thread by zattat | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.