column values concatenation

  • Thread starter Thread starter CreativeMind
  • Start date Start date
C

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,leftside,rightside
2 fronside,leftside
3 leftside,rightside
 
CreativeMind said:
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
Click to expand...

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
 
Back
Top