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
4 2584 iburyak 1,017
Recognized Expert Top Contributor
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 168
Recognized Expert New Member
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();
-
}
-
}
-
}
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
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();
-
}
-
}
-
}
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Mike King |
last post by:
I don't know how to group the following data in the way I want it. I want
the output of the transformation to be "5678". Does anyone know what I am
doing worry?
<?xml version="1.0"?>
<data>
<unit sn="5">
<test-a>
<result id="0">5</result>
</test-a>
|
by: Dr. StrangeLove |
last post by:
Greetings,
Let say we want to split column 'list' in table lists
into separate rows using the comma as the delimiter.
Table lists
id list
1 aa,bbb,c
2 e,f,gggg,hh
3 ii,kk
4 m
|
by: Laser Lu |
last post by:
Hello, everybody,
do you know how to use this Grouping Construct?
(?> )
I've found its reference on MSDN, but still can not understand it totally.
The following is its description:
Nonbacktracking subexpression (also known as a "greedy" subexpression). The
subexpression is fully matched once, and then does not participate piecemeal
|
by: Kurt |
last post by:
I'm using the fConcatChild function posted at
http://www.mvps.org/access/modules/mdl0004.htm to return a field from
the Many table of a 1:M relationship into a concatenated string. The
function puts everything in a comma separated format. In my case, it
returns a list of authors:
Borman, W., Oppler, S., White, L.,
I'd like to change the...
|
by: maffonso |
last post by:
Hi guys,
My table has a memo field. At the end of the month a run
a report and I would like to concatenate the memo fields in a unique
field by
dept. Im thinking doing this through totals button , but in the
combobox
there are only max,min,sum,.. There is no concatenate function.
Im avoinding doing that using VBA.
Somebody help me please.
| |
by: eric.goforth |
last post by:
Hello,
I'm working with a classic asp page that calls another classic asp
page. The html in my calling page looks like:
<form method="post"
action="/includes/mypage2.asp?subtype=MyType&ecd=1&eid=97702">
....Do some stuff
|
by: JackpipE |
last post by:
I have 2 tables
TABLE A
| id | name |
| 1 | John |
| 2 | David |
| 3 | Adam |
TABLE B
| id | colors |
| 1 | blue |
|
by: Bart op de grote markt |
last post by:
Hello,
I have a very simple problem which I will illustrate with an example:
I have the following records in my table:
A 1 C
A 2 C
A 3 C
B 8 K
B 9 K
|
by: bilibytes |
last post by:
Hi,
I am having a problem with a concatenated string.
I start my string outside of a for() and then, concatenate the string generated with the loop to it.
the string out of the loop looks like this:
$q = "INSERT INTO (asdf1,asdf2,asdf3) VALUES"
then when i add something like this:
$q .="('".$asdf1."', '".$asdf2."', '".$asdf3."')"
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it. ...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...
| |