best way to combine columns

Hi all,

I have a table with multiple rows with the same ID.

a) How do I combine all columns into one row with the same ID?

b) Is this better to do the combine in the store procedure/trigger or a sub that involked by some sort of datarepeater, datagrid controls?

Since I am trying to brush up my sql. I appreciate any examples on top of conceptual solution.

Thanks

I take that my question is not so clear so here is the example of what I wanted to do:

I have a sql 2000 table like this:

ID col1 col2

7777 itemx 12/02/07 00:00:10

7777 itemy 12/02/07 10:00:00

7777 itemz 12/02/07 12:10:60

8888 itemA 12/02/07 01:01:00

888 itemB 12/02/07 02:00:00

..........................................

I like to combine all rows with the same ID together like the followings:

7777 itemx itemy itemz 12/02/07

888 itemA itemB...................

The question has 3 parts:

1) what is the best way to do this? using trigger/stored procedure or just a vb sub with somesort of datarepeater, datalist controls

2) can you show me some examples of each way if any?

3) Can crystal report do something like this?

I am open to create another table or just plain writting them out on page.

Thanks

|||

Some one mentioned to me that 'trigger' could cause some confusion in reading the question. So , please do skip the trigger part.

|||

Depending on how many records will be pulled out, you could do it at the report level or at the DB level. You could write a function that takes the ID as parameter and returns a concatenated string for items and use the function in the SELECT.

SELECT Id, dbo.fnGetItems(ID), col2...

FROM ...

and create the function with a SELECT as

SELECT @.val = ISNULL(@.val,'') + ' ' + Convert(Varchar, col2) FROM YourTable WHERE ID = @.Id

and return the @.val.

|||

Thanks ndinakar. Anyone has any other solutions/ideas or clearer/complete solution? thanks

|||

Hi tvfoto,

This depends on if this table has a primary key.

If yes, I would suggest you read everything into a DataSet. Process the data combination

within the DataSet and update it back to the server, because in the .NET code, you will

have better flexibility for the combination logic.

If the table doesn't have a primary key, as ndinakar suggested, you can use some stored

procedure to achieve this.

HTH. If this does not answer you question, please feel free to mark it as Not Answered and post your reply. Thanks!

|||

THis table does not have a primary key. The following code works, however I dont know how to seperate them out as individual columns so I can give them a proper heading. Any suggestion? Thanks

//my user function.

create functionGetItems(@.mId int)

returns varchar(1000)As

Begin

declare @.values varchar(1000)

Set @.values=''

Select @.values=@.values+','+ myItemColumnName from myTable whereID=@.mId

return @.values

End

Go

//my aspx code

<

body><formid="form1"runat="server"><div><asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:myTableConnectionString %>"SelectCommand="SELECT DISTINCT [ID],dbo.GetItems(ID) as [xyzValues] FROM [myTable] "></asp:SqlDataSource>

<

asp:RepeaterID="Repeater1"runat="server"DataSourceID="SqlDataSource1">

<HeaderTemplate><tablestyle="background:#ebebeb"><tr><tdcolspan="5"> </td></tr><trstyle="text-align:left; background:yellow"><th>ID</th><th> </th><th>item1,item2,item3,item4, item5...</h>///////header for different items here...not a good way

</tr>

</HeaderTemplate><ItemTemplate><tr><tdstyle="width:25px"><%#DataBinder.Eval(Container.DataItem,"ID")%></td>

<td> </td><td><%#DataBinder.Eval(Container.DataItem,"xzyValues")%></td>.................................

</div></form></body>