I have tried using nvarchar data type but no use.

and yes, there are single quotes and commas in the "comments" column.Now what should i do?

Thank you.

I just gave an example ,but this is what it is doing exactly:

the current stored procedure is inserting the rows depending on the input parameters and returning only one row...

Thank you

This is what i am looking for .

Excellent .Thank you very much.

I have attached the note pad with expected results.

sorry for the confusion.

Hi,

I am sorry for this but i need a different output now.actually i want to create the view for the code you have given me but looks like...

The code is giving exact results i want .

Yes ,i checked the addition you are right my results are inaccurate.

Thanks Lynn.

I did test these scripts and they are working in sql server 2005.

create table OPPortunity

(

oppId int,

abc varchar(20)

)

create table book

(

Oppid int,

booked int,

year int

)

create table sales

(

oppid int,

shippped int,

year int

)

--inserting into opportunity table

insert...

of course i will change it to 2007 to 2010 ,but how will i get the desired column.if i include s.year i will miss 2008 and 2009 ,or if i...

can anyone please answer my previous reply ,its urgent.

What if i have data in sales table like this:

table c insertion:

insert into sales values

(2,200,2007)

(3,300,2010)

(5,300,2010)

if i only include B.year then i will miss 2007 shipped data in Sales table right.

Now, i need the out put in a different way which i will show you after i insert some more data into Book and sales tables.

insert into opportunity values

(1,'xxx')

(2,'ghj')

(3,'ghj')

(4,'ghj')

(5,'ghj')

table...

yes lynn,it is giving me like this:

opp id booked shipped

1 100 null

2 null 200

3 300 300

4 null null

5 null null

and when i do sum of booked and shipped it...

I got it ,left outer join is acting like an inner join here so i have changed my query to this:

select

sum(coalesce(B.shipped,0))

,sum(coalesce(B.booked,0))

from

tOpportunity O

left outer join tSalesS

on S.OppID = O.OppID

and S.year =2010

left...