Grouping into a Concatenated String

[Jazzer]

I want to 'flatten' two tables into one by combining the '1-n' values from the 'child' records into a single concatenated string within the parent by using queries.

I.E. create a single NVarChar field to hold all of the values that are contained in the child entries for that parent.

E.G.
Parent 1 - Child 1 Value "Big"; Child 2 Value "Small"
Parent 2 - Child 1 Value "Red"; Child 2 Value "White"; Child 3 Value "Green"

To Become
Parent 1 Values "Big / Small"
Parent 2 Values "Red / White / Green"

Any suggestions using query rather than code?

Thanyou in anticipation - Jazzer

[iburyak]

Try this

select parent_id,
substring(case when exists(select * from #Child where parent_id = a.parent_id and value = 'Big') then '\Big' else '' end
+ case when exists(select * from #Child where parent_id = a.parent_id and value = 'Small') then '\Small' else '' end
+ case when exists(select * from #Child where parent_id = a.parent_id and value = 'Big') then '\Big' else '' end
+ case when exists(select * from #Child where parent_id = a.parent_id and value = 'Red') then '\Red' else '' end
+ case when exists(select * from #Child where parent_id = a.parent_id and value = 'White') then '\White' else '' end
+ case when exists(select * from #Child where parent_id = a.parent_id and value = 'Green') then '\Green' else '' end
,2,800)
from #Parent_table a

[almaz]

No, better would be to use script

select parent_id = 'Parent 1',  child = 'Child 1 Value "Big"; Child 2 Value "Small"'
union all
select parent_id = 'Parent 2', child = 'Child 1 Value "Red"; Child 2 Value "White"; Child 3 Value "Green"'

This is the fastest way to get useless data :).

In SQL Server 2005 you can create a user-defined aggregate that will sum up the child rows, but there would be a 8000 bytes limitation for aggregated value (so the maximum length of the resulting string would be nvarchar(3998)) . Another way in SQL Server 2005 is to create CLR stored procedure that will iterate through all child rows and produce combined rows, something like this one:

using System.Data;
using System.Data.SqlClient;
using System.Text;
using Microsoft.SqlServer.Server;

public class StoredProcedures
{
	private static void SendRow(SqlDataRecord record, string parentName, StringBuilder children)
	{
		record.SetString(0, parentName);
		record.SetString(1, children.ToString());
		SqlContext.Pipe.SendResultsRow(record);
		children.Length = 0;
	}

	[SqlProcedure]
	public static void usp_CombineRows()
	{
		using (SqlConnection connection = new SqlConnection("context connection = true"))
		{
			SqlCommand command =
				new SqlCommand(
					@"
				select ParentTable.ID, ParentTable.Name, ChildTable.Name
				from ParentTable 
					inner join ChildTable on ParentTable.ID = ChildTable.ParentID
				order by ParentTable.ID");

			SqlDataRecord outputRecord = new SqlDataRecord(
				new SqlMetaData("ParentName", SqlDbType.NVarChar, 250),
				new SqlMetaData("CombinedChildren", SqlDbType.NVarChar, SqlMetaData.Max));
			SqlContext.Pipe.SendResultsStart(outputRecord);

			int parentID;
			int previousParentID = int.MinValue;
			string parentName = null;
			StringBuilder children = new StringBuilder();

			connection.Open();
			using (SqlDataReader reader = command.ExecuteReader())
			{
				parentID = reader.GetInt32(0);
				
				if (parentID != previousParentID)
				{
					if (previousParentID != int.MinValue)
						SendRow(outputRecord, parentName, children);

					previousParentID = parentID;
					parentName = reader.GetString(1);
				}
				children.Append(reader.GetString(2));
			}

			if (previousParentID != int.MinValue)
				SendRow(outputRecord, parentName, children);

			SqlContext.Pipe.SendResultsEnd();
		}
	}
}

[Jazzer]

Thanks for your reply - It caused me to evaluate the possibility of case, ( and I learnt something new), but all this has done is convinced me I need to develop the solution as code and that a query will not provide the answer I need.

Thanks again - Jazzer

Try this


select parent_id,
substring(case when exists(select * from #Child where parent_id = a.parent_id and value = 'Big') then '\Big' else '' end
+ case when exists(select * from #Child where parent_id = a.parent_id and value = 'Small') then '\Small' else '' end
+ case when exists(select * from #Child where parent_id = a.parent_id and value = 'Big') then '\Big' else '' end
+ case when exists(select * from #Child where parent_id = a.parent_id and value = 'Red') then '\Red' else '' end
+ case when exists(select * from #Child where parent_id = a.parent_id and value = 'White') then '\White' else '' end
+ case when exists(select * from #Child where parent_id = a.parent_id and value = 'Green') then '\Green' else '' end
,2,800)
from #Parent_table a

[Jazzer]

Thanks for your reply - I really wanted to solve the problem using a query but can now see that this will not provide the answer I need.
I will get one of the developers to provide the solution in code according to a full specification I now need to write. (sorry but I haven't written code for more than 10 years now).

The problem comes from an old commercial database solution and believe me the relational structure provides loads of headaches as it has been maintained by different people over a long period of time, ( and it is owned by a 3rd party who manages the content - we just get extracts) - selection reduces the basic working set from 5,000,000 rows to 50,000 before you start creating multiple relational links driving the row counts up rapidly.

Flattening some of the data helps to reduce the full row processing counts to manageable proportions.

Thanks again - Jazzer

No, better would be to use script

select parent_id = 'Parent 1', child = 'Child 1 Value "Big"; Child 2 Value "Small"'
union all
select parent_id = 'Parent 2', child = 'Child 1 Value "Red"; Child 2 Value "White"; Child 3 Value "Green"'

This is the fastest way to get useless data :).

In SQL Server 2005 you can create a user-defined aggregate that will sum up the child rows, but there would be a 8000 bytes limitation for aggregated value (so the maximum length of the resulting string would be nvarchar(3998)) . Another way in SQL Server 2005 is to create CLR stored procedure that will iterate through all child rows and produce combined rows, something like this one:

using System.Data;
using System.Data.SqlClient;
using System.Text;
using Microsoft.SqlServer.Server;
 
public class StoredProcedures
{
	private static void SendRow(SqlDataRecord record, string parentName, StringBuilder children)
	{
		record.SetString(0, parentName);
		record.SetString(1, children.ToString());
		SqlContext.Pipe.SendResultsRow(record);
		children.Length = 0;
	}
 
	[SqlProcedure]
	public static void usp_CombineRows()
	{
		using (SqlConnection connection = new SqlConnection("context connection = true"))
		{
			SqlCommand command =
				new SqlCommand(
					@"
				select ParentTable.ID, ParentTable.Name, ChildTable.Name
				from ParentTable 
					inner join ChildTable on ParentTable.ID = ChildTable.ParentID
				order by ParentTable.ID");
 
			SqlDataRecord outputRecord = new SqlDataRecord(
				new SqlMetaData("ParentName", SqlDbType.NVarChar, 250),
				new SqlMetaData("CombinedChildren", SqlDbType.NVarChar, SqlMetaData.Max));
			SqlContext.Pipe.SendResultsStart(outputRecord);
 
			int parentID;
			int previousParentID = int.MinValue;
			string parentName = null;
			StringBuilder children = new StringBuilder();
 
			connection.Open();
			using (SqlDataReader reader = command.ExecuteReader())
			{
				parentID = reader.GetInt32(0);
 
				if (parentID != previousParentID)
				{
					if (previousParentID != int.MinValue)
						SendRow(outputRecord, parentName, children);
 
					previousParentID = parentID;
					parentName = reader.GetString(1);
				}
				children.Append(reader.GetString(2));
			}
 
			if (previousParentID != int.MinValue)
				SendRow(outputRecord, parentName, children);
 
			SqlContext.Pipe.SendResultsEnd();
		}
	}
}