Solved

Concatinate & Delimiter

Posted on 2006-10-30
33
384 Views
Last Modified: 2008-02-01
Hi.

I have 2 tables within a database.  One stores all the products available and other stores expiration date information about products.  I need to store all the information in one table....(that is easy) but here comes a tricky part.

Each product could have up to 10 expiration dates and also with the date is field labeled description....

So i need to insert all 10 entries in one column in the products table with delimiters for each entry (|) and each item (;) for application parsing reasons

Here is summary.

products table
product_id            product_name
------------            ----------------
001                     Nexus Hair Gel




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


here what the product table should contain


I have 2 tables within a database.  One stores all the products available and other stores expiration date information about products.  I need to store all the information in one table....(that is easy) but here comes a tricky part.

Each product could have up to 10 expiration dates and also with the date is field labeled description....

So i need to insert all 10 entries in one column in the products table with delimiters for each entry (|) and each item (;) for application parsing reasons

Here is summary.

products table
product_id            product_name                  expiration_information
------------            ----------------                  --------------------------
001                     Nexus Hair Gel                  10-01-2006; expires_dsc1 | 11-01-2006; expires_dsc2 | 12-01-2006; null | 01-01-2007; expires_dsc3

thanks
0
Comment
Question by:SamirKumar
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 15
  • 14
33 Comments
 
LVL 11

Expert Comment

by:rw3admin
ID: 17838510
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

0
 

Author Comment

by:SamirKumar
ID: 17839267
well...i need to work for all product_ids...there are about 20,000

thanks
samir
0
 
LVL 29

Expert Comment

by:Gautham Janardhan
ID: 17839832

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
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 11

Expert Comment

by:rw3admin
ID: 17840073
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
0
 

Author Comment

by:SamirKumar
ID: 17842662

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

Expert Comment

by:rw3admin
ID: 17843219
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?
0
 

Author Comment

by:SamirKumar
ID: 17843549


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

0
 

Author Comment

by:SamirKumar
ID: 17843820


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

Expert Comment

by:rw3admin
ID: 17843890
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??

0
 

Author Comment

by:SamirKumar
ID: 17844084
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...





0
 

Author Comment

by:SamirKumar
ID: 17844102
i can wait...i can finish up the web programming side...
0
 
LVL 11

Expert Comment

by:rw3admin
ID: 17844702
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


0
 

Author Comment

by:SamirKumar
ID: 17844828
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.

0
 
LVL 11

Expert Comment

by:rw3admin
ID: 17844883
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
 
0
 

Author Comment

by:SamirKumar
ID: 17845345
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.
0
 
LVL 11

Expert Comment

by:rw3admin
ID: 17845618
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

0
 

Author Comment

by:SamirKumar
ID: 17846007
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....


0
 
LVL 11

Expert Comment

by:rw3admin
ID: 17846086
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
0
 

Author Comment

by:SamirKumar
ID: 17846261
i have an excel file with the data.

what is your email address?

that way it would be easier to work through
0
 
LVL 11

Expert Comment

by:rw3admin
ID: 17846287
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
0
 
LVL 11

Expert Comment

by:rw3admin
ID: 17846321
Samir I have asked a moderator to help us with file upload here is my question string

http://www.experts-exchange.com/Community_Support/Q_22044554.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
0
 

Author Comment

by:SamirKumar
ID: 17847698
oh i am in new jersey...
0
 
LVL 11

Expert Comment

by:rw3admin
ID: 17851231
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

0
 

Author Comment

by:SamirKumar
ID: 17854514
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*/


0
 
LVL 11

Expert Comment

by:rw3admin
ID: 17855919
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
0
 

Author Comment

by:SamirKumar
ID: 17858023
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
0
 
LVL 11

Accepted Solution

by:
rw3admin earned 250 total points
ID: 17859132
yep you can set it up as job and schedule that to run after hours (for example between 12:01 am and 5:00 am every 4 months)...

Rizwan
0
 

Author Comment

by:SamirKumar
ID: 17860581
well...this will be part of a big dts/ssis thing to execute several sql commands.

samir
0
 
LVL 11

Expert Comment

by:rw3admin
ID: 18069070
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
0
 

Author Comment

by:SamirKumar
ID: 18069374
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
0

Featured Post

Enroll in June's Course of the Month

June's Course of the Month is now available! Every 10 seconds, a consumer gets hit with ransomware. Refresh your knowledge of ransomware best practices by enrolling in this month's complimentary course for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how the fundamental information of how to create a table.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question