getting similar fields in same output row for grouping totals

I have a table similar to the below:

Field1 Field2
EXE1 Age
EXE2 Child Under 10
EXE3 Not a citizen
WEB1 Cares for a child under 10
WEB2 Be a US Citizen

and I am getting the totals for the records grouped by Field1 - not a
problem. So, output is something like:

Field2 Total %
Age 10 .4
Child under 10 15 .5
Not a citizen 15 .5
Cares for < 10 20 .6
Be a US citizen 25 .7

However, IF there is an EXE# basically the same as a WEB#, then I need
to put them together in my report output, like so:

Field EXEtotal WEBtotal Total %
Age 10 0 10 .4
Child <10 15 20 35 1.0
Citizen 15 25 40 1.1

So, I'm getting the data I need returned, but don't know how to sort it
in my report to give me the new output I need.

I think I need to do this in my report output rather than my SQL
because I need to pull the individual information. Is there a way to
say "if Field 1 = 'WEB2' and Field 1 = 'EXE3', list in same row, then
total? I just don't know how to get them in the same row...

Thanks!

I would create a new column in the table to keep track of the parent group, ie.. your Web1 and Exe1 would be in the same Parent group.

Let me know if you can't make sense of it.

sql

Getting FKey violations in Replication

I have parent and child tables both in publisher and subscriber and I have
identical foreign keys in publisher and subscriber. However, sometimes
replication will report foreign key violation for the data getting
replicated.
To troubleshoot, I dropped and recreated the foreign keys with ‘Not For
Replication’ option which made the error go away. I ran some SQLs to see
where the data was inconsistent in the reported parent/child tables but
didn’t find any thing. To be sure, I dropped and recreated the foreign keys
and no error was reported.
My question is, why did I get the error in replication when I had the FKeys
without “Not For Replication” option. I would prefer to have FKeys check for
data integrity for replicated data on the subscriber side as well.
-A
Where did you do your validation? On the Publisher or Subscriber or both?
Also check to see if you are replicating cascading updates and deletes -
this could trigger this error.
What you should do next time you get this error is to look at the conflict
in the conflict viewer and determine which row is causing the problem.
In general you want to maintain DRI on both sides. Using the NFR option on
constraints will have the effect of not enforcing the constraint when the
DML is caused by a replication process. Merge replication can replicate
child records before parent records and this can cause the error you are
seeing.
Another option is to increase the download and upload generations per batch
setting, and if you get the error, re-run the agent..
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Adam" <Adam@.discussions.microsoft.com> wrote in message
news:364E4E6B-A323-43CE-A728-394846D36C05@.microsoft.com...
>I have parent and child tables both in publisher and subscriber and I have
> identical foreign keys in publisher and subscriber. However, sometimes
> replication will report foreign key violation for the data getting
> replicated.
> To troubleshoot, I dropped and recreated the foreign keys with 'Not For
> Replication' option which made the error go away. I ran some SQLs to see
> where the data was inconsistent in the reported parent/child tables but
> didn't find any thing. To be sure, I dropped and recreated the foreign
> keys
> and no error was reported.
> My question is, why did I get the error in replication when I had the
> FKeys
> without "Not For Replication" option. I would prefer to have FKeys check
> for
> data integrity for replicated data on the subscriber side as well.
> -A
>
>