Concatinate & Delimiter

try this...I dont know if your date is actually a string or you want it formated like that.. so fix that part accordingly

Declare
@Product_Id varchar(100),
@expiration_information Varchar(500)

Select      @expiration_information='', @Product_ID='001'

Select      @expiration_information=Convert(varchar(12),expiration_date,110)+';'+IsNull(expiration_desc,'Null')+'|'
From      expiration
Where      product_id =@Product_ID

Insert      Summary

Select   Product_ID,
      Product_Name,
      @expiration_information
From      products
Where      product_id =@Product_ID

well...i need to work for all product_ids...there are about 20,000

thanks
samir

1) U got to write a Sp
2) Loop thru Products table
3) For each row in Product fetch child and concatenate
4) Insert into a temptable
5) Fetch result from temptable

Samir, its up to you ... you can either put following in a proc or just run this script


Create Table #Product ( Id int Identity(1,1), Product_ID varchar(100))
Create #Summary (Product_ID varchar(100), Product_Name Varchar(500),expiration_information Varchar(8000))

Declare
@ID int,
@Product_Id varchar(100),
@expiration_information Varchar(8000)

Insert #Product (Product_ID)
Select distinct Product_ID from products

Select   @ID=1,@expiration_information=''
Select   @Product_ID=Product_ID from #Product where ID=@ID

While @ID<=(Select Max(ID) from #Product)
Begin
Select     @expiration_information=Convert(varchar(12),expiration_date,110)+';'+IsNull(expiration_desc,'Null')+'|'
From      expiration
Where     product_id =@Product_ID

Insert    #Summary
Select   Product_ID,
            Product_Name,
            @expiration_information
From     products
Where   product_id =@Product_ID

Select   @ID=@ID+1,@expiration_information=''
Select   @Product_ID=Product_ID from #Product where ID=@ID
End

Select * from #Summary


rw3admin

wouldn't an update command with a set function be easier...i rather go down that path...

you can obviously work with this query to update insert or select and you didnt tell me what table you are updating
you said this is the structure of products table

product_id            product_name
------------            ----------------
001                     Nexus Hair Gel

and this is the structure of expiration table

expiration
product_id           expiration_date                   expiration_desc
------------           -----------------                   ------------------
001                    10-01-2006                        expires_dsc1
001                    11-01-2006                        expires_dsc2
001                    12-01-2006                        null
001                    01-01-2007                        expires_dsc3

so where should I update?

sorry for the confusion....ignore the example...i was trying to replace the real life example with a fake and now realized this is not top secret...

so here is what i am doing now....not working to well...cause it doesn't loop if you have 2 sets of value that need to be inserted.  

i have data (1776 rows) in this table named 'exclusivity' and data (20,000) in this table named 'products'.  Both tables have a columns labeled 'nda_number' and 'product_number' and are used to join the table.

what i am looking to do (1) insert matching values from exclusivity table into the products table into a column 'exclusivity information'.

so i wrote this statement...but there is one major flaw...if there are two rows in exclusivity it only inserts the first one......

UPDATE
      products
      SET
            exclusivity_information = products.exclusivity_information + ' | ' + exclusivity.exclusivity_date + ' : ' + exclusivity.exclusivity_code
      FROM
            exclusivity, products
      WHERE
            products.nda_number = exclusivity.nda_number
            and products.product_number = exclusivity.product_number

here is the result.  as you can see it is wrong cause the value should be (| APR 15,2006 : M-28 | OCT 15,2006 : PED)

017577      001      M-28      APR 15,2006      ALZA      DITROPAN        | APR 15,2006 : M-28
017577      001      PED      OCT 15,2006      ALZA      DITROPAN        | APR 15,2006 : M-28

I guess I would need to loop through to find all values...like a temp variable???

samir

i have been looking on krugle..and see many cursor examples...

hold Samir I am busy in something, Cursors are bad, you can update through this loop I can either give you an example in next hour or so or you can figure it out on your own

quickly tell me...is this true
exclusivity table have distinct product numbers
products table have repeated product numbers each with its own exclusivity_information

and when you run your update statement
>>
UPDATE
     products
     SET
exclusivity_information = products.exclusivity_information + ' | ' + exclusivity.exclusivity_date + ' : ' + ....... <<

and you do a select on products table you get following....

017577     001     M-28     APR 15,2006     ALZA     DITROPAN       | APR 15,2006 : M-28
017577     001     PED     OCT 15,2006     ALZA     DITROPAN       | APR 15,2006 : M-28

but you want

017577     001     M-28     APR 15,2006     ALZA     DITROPAN       | APR 15,2006 : M-28 | OCT 15,2006 : PED
017577     001     PED     OCT 15,2006     ALZA     DITROPAN       | APR 15,2006 : M-28 | OCT 15,2006 : PED

for both records??

this output is actually a join of the two tables...
017577     001     M-28     APR 15,2006     ALZA     DITROPAN       | APR 15,2006 : M-28
017577     001     PED     OCT 15,2006     ALZA     DITROPAN       | APR 15,2006 : M-28

here are the individual listing within the two tables

select * from exclusivity where nda_number = '017577' and product_number  = '001'
017577      001      M-28      APR 15,2006
017577      001      PED      OCT 15,2006

select * from products where nda_number = '017577' and product_number  = '001'
15848      OXYBUTYNIN CHLORIDE      TABLET      ORAL      DITROPAN      ALZA      5MG      017577      001      AB      Approved Prior to Jan 1, 1982      Yes      RX      ALZA CORP      7        | APR 15,2006 : M-28


in the products table...the nda_number and product_number are distinct and unique for each row....but the same combination of nda_number and product_number could appear multiple times in the exclusivity table; as you see above....so in short, there are no distinct product or nda numbers in the exclusivity table...nor in combination are they unique...

i can wait...i can finish up the web programming side...

Samir
still confused about your explaination...
but here is a quick solution.....

Create Table #Product ( Id int Identity(1,1), Product_Number varchar(100), NDA_Number Varchar(100))

Declare
@ID int,
@Product_Number varchar(100),
@NDA_Number varchar(100),
@exclusivity_information Varchar(8000)

Insert #Product (Product_Number,NDA_Number)
Select distinct Product_Number NDA_Number from products

Select   @ID=1,@exclusivity_information=''
Select   @Product_Number=Product_Number,@NDA_Number=NDA_Number from #Product where ID=@ID

While @ID<=(Select Max(ID) from #Product)
Begin
Select      @exclusivity_information = IsNull(products.exclusivity_information,'Null') + ' | ' +
                        IsNull(exclusivity.exclusivity_date,'Null') + ' : ' +
                        IsNull(exclusivity.exclusivity_code,'Null')
FROM
      exclusivity
Inner Join
      products
ON      products.nda_number = exclusivity.nda_number and
      products.product_number = exclusivity.product_number
WHERE    products.nda_number =@NDA_Number and
      products.product_number = @Product_Number

Update      products
SET      exclusivity_information = @exclusivity_information
FROM       products
Inner Join
      exclusivity
ON      products.nda_number = exclusivity.nda_number and
      products.product_number = exclusivity.product_number
WHERE    products.nda_number =@NDA_Number and
      products.product_number = @Product_Number

Select   @ID=@ID+1,@exclusivity_information=''
Select   @Product_Number=Product_Number,@NDA_Number=NDA_Number from #Product where ID=@ID
End

get this error:

Msg 120, Level 15, State 1, Line 9
The select list for the INSERT statement contains fewer items than the insert list. The number of SELECT values must match the number of INSERT columns.

crap... missed a comma

here is the correct script


Create Table #Product ( Id int Identity(1,1), Product_Number varchar(100), NDA_Number Varchar(100))

Declare
@ID int,
@Product_Number varchar(100),
@NDA_Number varchar(100),
@exclusivity_information Varchar(8000)

Insert #Product (Product_Number,NDA_Number)
Select distinct Product_Number, NDA_Number from products

Select   @ID=1,@exclusivity_information=''
Select   @Product_Number=Product_Number,@NDA_Number=NDA_Number from #Product where ID=@ID

While @ID<=(Select Max(ID) from #Product)
Begin
Select     @exclusivity_information = IsNull(products.exclusivity_information,'Null') + ' | ' +
                    IsNull(exclusivity.exclusivity_date,'Null') + ' : ' +
                    IsNull(exclusivity.exclusivity_code,'Null')
FROM
     exclusivity
Inner Join
     products
ON     products.nda_number = exclusivity.nda_number and
     products.product_number = exclusivity.product_number
WHERE    products.nda_number =@NDA_Number and
     products.product_number = @Product_Number

Update     products
SET     exclusivity_information = @exclusivity_information
FROM      products
Inner Join
     exclusivity
ON     products.nda_number = exclusivity.nda_number and
     products.product_number = exclusivity.product_number
WHERE    products.nda_number =@NDA_Number and
     products.product_number = @Product_Number

Select   @ID=@ID+1,@exclusivity_information=''
Select   @Product_Number=Product_Number,@NDA_Number=NDA_Number from #Product where ID=@ID
End
 

nah...this wasn't good.  when a record only has one match it inserts two sets into the exclusivity_information column and also...it doesn't go beyond 2.  some of records may have 4 or so matches.

then I totally misunderstood your comments...
I dont see any limitation in my code thats keeping it from going beyond 2

also if your  products table have this record repeated twice it will be updated for both records
this should give you an idea of such records....

Select products.nda_number,products.product_number from products group by products.nda_number,products.product_number having count(*)>1

nope...that command bring back nothing.

is there a way we chat on the phone...this way i could send you an excel file and we can walk it through....

I dont think EE allows that,
can you instead post some real life data from your both tables, all columns if possible and then give me whats the final result you want as well and I will create something for you

rw3admin

i have an excel file with the data.

what is your email address?

that way it would be easier to work through

ayee carumba....
try following
http://www.ee-stuff.com/login.php?PHPSESSID=6863cb9aee60cdd4a13565b79823b7fd

somehow its not working for me... if not let me contact a moderator if I can give you my email, they are pretty strict about this you know and I dont want to be in trouble

rw3admin

Samir I have asked a moderator to help us with file upload here is my question string

https://www.experts-exchange.com/questions/22044554/Can-someone-please-help-with-a-file-upload-on-EE-Stuff-com.html

I am in USA CST time zone I dont know if you are in India or in US, so lets wait a while and see if a moderator can solve this issue for us today

thanks

rw3admin

oh i am in new jersey...

Samir looks like you the EE-Stuff.com is not working you can send me this file in an email to ... as Netminder reminded me some spammer software scans such posts for emails to it can spam them. so i I have to send you email in broken string, email id is same as my name here and I am using google's mail.

again per netminder's suggestion I have to post you replies here only so its visible to others as well.
Thanks

rw3admin

hey man..i got it working...i wrote this statement...just copy and paste the code and i will give you the points...i

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER procedure [dbo].[usp_update_exclusivity_information]
as
begin

/* declare local variables here */
/* ---------------------------- */
declare @ls_nda_number            nvarchar(50)
declare @ls_product_number      nvarchar(50)
declare @ls_exclusivity_information nvarchar(2000)

/* initialize local variables here */
/* ------------------------------- */
set @ls_nda_number = ''
set @ls_product_number = ''
set @ls_exclusivity_information = ''

/* initialize the exclusivity_information in the products table */
/* ------------------------------------------------------------ */
update
      products
set
      exclusivity_information = ''

/* declare cursor */
/* -------------- */
declare
      lcur_products
cursor for
      select
            nda_number,
            product_number
      from
            products
      order by
            nda_number,
            product_number

/* open cursor */
/* ----------- */
open lcur_products

/* fetch the first record i.e. nda_number + product_number from products */
/* --------------------------------------------------------------------- */
fetch next from
      lcur_products
into
      @ls_nda_number,
      @ls_product_number

/* get into the loop */
/* ----------------- */
while @@fetch_status = 0
begin
      /* update the exclusivity_date and exclusivity_code into a memory variable */
      /* ----------------------------------------------------------------------- */
      set @ls_exclusivity_information = ''
      update
            exclusivity
      set
            @ls_exclusivity_information = isnull(@ls_exclusivity_information,'') + isnull(b.exclusivity_date, 'No Data Available') + ':' + isnull(b.exclusivity_code, 'No Data Available') + ' | '
      from
            products a,
            exclusivity b
      where
            a.nda_number                  = b.nda_number
            and a.product_number      = b.product_number
            and a.nda_number            = @ls_nda_number
            and a.product_number      = @ls_product_number
      
      --print @ls_nda_number + ':' + @ls_product_number + ';' + @ls_exclusivity_information
      
      /* update the exclusivity_information in the products table */
      /* -------------------------------------------------------- */
      update
            products
      set
            exclusivity_information = @ls_exclusivity_information
      where
            nda_number                  = @ls_nda_number
            and product_number      = @ls_product_number

      /* fetch the next record */
      /* --------------------- */
      fetch next from
            lcur_products
      into
            @ls_nda_number,
            @ls_product_number
end

/* close the cursor */
/* ---------------- */
close lcur_products

/* deallocate the cursor */
/* --------------------- */
deallocate lcur_products
end /* endof ups_update_exclusivity_information*/

cool :) thats good news
I will take a look at your cursor tomorrow morning, and I will only take points if you think I helped you in anyway, else you can ask a moderator to close this question.

Thanks

Rizwan

yes you did help.  i am not that great at explaining but since this is only refreshed once every 4 months...i can run this script hours while users are not here.

samir

well...this will be part of a big dts/ssis thing to execute several sql commands.

samir

per Samir's comments I helped him, but I personally dont think that I did a whole lot, I wouldnt mind to get some points though as you can see I put some effort in this question

rw3admin

the actual (code) provided was helpful in ways to me.  i didn't give me the nearly the exact resolution but it guided me in the right direction and also a good amount of work was put into this.  i have posted the final solution i want that shared with other users but i also want rw2admin to get some recogniztion for his work.

thanks
samir