Need Help for Parent Child Table Join Output

[chint...@gmail.com]

Hi frnds I want to know whether following thing is possible through
query

Parent Table
----------------
Pid | PField1 | PField2 | PField3 | PField4
1 A Aa Aaa Aaaa
2 B Bb Bbb Bbbb

Child Table
----------------
CPid | CField1 | CField2 | CField3
1 X Xx Xxx
1 X1 X1x X1xx
1 X2 X2x X2xx
2 Y Yy Yyy
2 Y1 Y1y Y1yy
2 Y2 Y2y Y2yy

when we create a join between this 2 table the output will be as such
Output
---------
Pid | PField1 | PField2 | PField3 | PField4 | CPid | CField1 | CField2
| CField3
1 A Aa Aaa Aaaa 1
X Xx Xxx
1 A Aa Aaa Aaaa 1
X1 X1x X1xx
1 A Aa Aaa Aaaa 1
X2 X2x X2xx
2 B Bb Bbb Bbbb 2
Y Yy Yyy
2 B Bb Bbb Bbbb 2
Y1 Y1y Y1yy
2 B Bb Bbb Bbbb 2
Y2 Y2y Y2yy

I want the output through query as below
desired output
---------
Pid | PField1 | PField2 | PField3 | PField4 | CPid | CField1 | CField2
| CField3
1 A Aa Aaa Aaaa 1
X Xx Xxx

1 X1 X1x X1xx

1 X2 X2x X2xx
2 B Bb Bbb Bbbb 2
Y Yy Yyy

2 Y1 Y1y Y1yy

2 Y2 Y2y Y2yy

Is it possible to write query to get such output..

[SetonSoftware]

On Sep 8, 11:05 am, "chintu4...@gmail.com" <chintu4...@gmail.com>
wrote:

The text wrapping made this difficult to read but it seems you want to
suppress repeating information, likely because you are displaying it
in a report. is this correct?

If so, this would best be handled in the reporting tool.

Thanks

Carl

[chint...@gmail.com]

Hi SetonSoftware yes you are right I want to do this for report but I
was not able to figure out in Report so I though to go other way round
and fetch record in this manner.I am using Crystal Report 2008 Basic.

Is it possible for such query or not plz let me know?

[SetonSoftware]

On Sep 9, 2:36 am, "chintu4...@gmail.com" <chintu4...@gmail.com>

> > Carl- Hide quoted text -
>
> - Show quoted text -

Crystal Reports will allow you to suppress repeating information and
personally I feel that is the way you should go. There was a thread on
this a little while back that may help you:

http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/b3f04ea9142f124c/8e1a01f4fcf1ab04?hl=en&lnk=gst&q=report+format#8e1a01f4fcf1ab04

You can suppress this information in your stored procedure but its a
pain to do and I believe not worth the effort. A report writer is best
used as a formatting tool and this definitely falls under the
formatting category.

Hope this helps

Carl

[Erland Sommarskog]

chint...@gmail.com (chint...@gmail.com) writes:
> Hi SetonSoftware yes you are right I want to do this for report but I
> was not able to figure out in Report so I though to go other way round
> and fetch record in this manner.I am using Crystal Report 2008 Basic.
>
> Is it possible for such query or not plz let me know?

Possible? Yes. Should you do it? No. This is a basic feature for a
reporting tool. It is a very tricky thing to do in a stored procedure.
You might do it for an admin procedure that is to be run from a query
window.

I would like to say that I have never worked with Crystal, but that is
not really true. But almost. There is one thing I know though: they
have forums where you can ask questions.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx