How to Combine Multiple Rows Data into single Record or String based on a common field.

Hellow Folks.

Here is the Original Data in my single SQL 2005 Table:

Department: Sells:

1 Meat

1 Rice

1 Orange

2 Orange

2 Apple

3 Pears

The Data I would like read separated by Semi-colon:

Department: Sells:

1 Meat;Rice;Orange

2 Orange;Apple

3 Pears

I would like to read my data via SP or VStudio 2005 Page . Any help will be appreciated. Thanks..

Hi,

you can use the following Function in SQL server:

USE NORTHWIND
GO
CREATE FUNCTION ProductList (@.CategoryIDINT)
RETURNSVARCHAR(1000)
AS
BEGIN
DECLARE @.ProductsVARCHAR(1000)
SELECT@.Products =COALESCE(@.Products +', ','') + ProductName
FROM Products
WHERE CategoryID = @.CategoryID
ORDER BY ProductNameASC
RETURN @.Products
END
GO
SELECTDISTINCT CategoryID, dbo.ProductList (CategoryID)AS ProductList
FROM Products
GO

and this is based on your table: 

USE NORTHWINDCS
GO
CREATE FUNCTION ProductList (@.CategoryIDINT)
RETURNSVARCHAR(1000)
AS
BEGIN
DECLARE @.ProductsVARCHAR(1000)
SELECT@.Products =COALESCE(@.Products +', ','') + sells
FROM table1
WHERE Department = @.CategoryID
ORDER BY sellsASC
RETURN @.Products
END
GO
SELECTDISTINCT Department, dbo.ProductList (department)AS ProductList
FROM table1
GO
 

thanks

|||

SharpGuy, your solution works. Thanks and have a great Thanksgiving...

How to collapsible a column when the report was designed in table mode?

Hi folks, I'm trying to collapse a column on a report that's designed in table mode. I was trying to mimick what happens in a matrix where you have a column that has a '+' in it that makes toggles the visibility of a column to its immediate right (The reason I'm not using Matrix mode is I continually get "out of memory" errors on the report I'm generating.).

When I select the column and mark visible to 'false' in the properties, it of course asks me for a TextBox. My problem is that I am unable to find a scenario where the textbox is "in the proper group". Is this something that's supported, and if so, I'd appreciate some pointers to lead me in the right direction.

Thanks.bump.. anyone have any clues?|||

I was able to get this to work fine in RS 2005. Where is the textbox that you are setting as the ToggleItem for the table column? I selected the textbox in the table header cell immediately to the left of the column that I wanted to toggle. What is the exact error you are seeing?

-Chris

|||Chris, thanks for your reply. My problem is I was selecting the whole column and trying to toggle the visibility on that. You can't do that; you've got to set the visibility on each portion individually (column header, group, detail). Thanks again.|||

Hi Aquineas..

I needed to do the same and managed to get it to work...

Create a text box outside of the table in the mainreport - with suitable text... Show / Hide Detail

and name the text box = ToggleColumns

Now highlight the column in the table and select the ToggleItem under Visibility and type ToggleColumns.

It works for me - even though the ToogleColumns isn't available in the list.

Cheers

Michael

|||I am having the same problem as Aquineas, I took Michael's suggestion but it doesn't seem to work for me. Having done what Micheal has suggested, I hides the column period and there is no way of getting it back on run time. I think I follow the instruction carefully. I am still looking for more suggestion.

How to collapsible a column when the report was designed in table mode?

Hi folks, I'm trying to collapse a column on a report that's designed in table mode. I was trying to mimick what happens in a matrix where you have a column that has a '+' in it that makes toggles the visibility of a column to its immediate right (The reason I'm not using Matrix mode is I continually get "out of memory" errors on the report I'm generating.).

When I select the column and mark visible to 'false' in the properties, it of course asks me for a TextBox. My problem is that I am unable to find a scenario where the textbox is "in the proper group". Is this something that's supported, and if so, I'd appreciate some pointers to lead me in the right direction.

Thanks.bump.. anyone have any clues?|||

I was able to get this to work fine in RS 2005. Where is the textbox that you are setting as the ToggleItem for the table column? I selected the textbox in the table header cell immediately to the left of the column that I wanted to toggle. What is the exact error you are seeing?

-Chris

|||Chris, thanks for your reply. My problem is I was selecting the whole column and trying to toggle the visibility on that. You can't do that; you've got to set the visibility on each portion individually (column header, group, detail). Thanks again.|||

Hi Aquineas..

I needed to do the same and managed to get it to work...

Create a text box outside of the table in the mainreport - with suitable text... Show / Hide Detail

and name the text box = ToggleColumns

Now highlight the column in the table and select the ToggleItem under Visibility and type ToggleColumns.

It works for me - even though the ToogleColumns isn't available in the list.

Cheers

Michael

|||I am having the same problem as Aquineas, I took Michael's suggestion but it doesn't seem to work for me. Having done what Micheal has suggested, I hides the column period and there is no way of getting it back on run time. I think I follow the instruction carefully. I am still looking for more suggestion.