Store procedure

hi i have a table

 Name var value1 value2
 Abc aa 123 10
 Bcd bb 32 21
 Fgr cc 12 34

Etc …..

I need to (create new/update) following result table.

For every aa I should have three entries ie aa, ab and ac. Similarly for every bb I should have 2 entries like bb and ba and cc should have four entries like cc,ca,cb and cd and so on. And these extra entries will remain constant like for aa resulting table will have 3, for bb it will have 2 and for cc it will be 4 etc Rest of the values in table will not change so my resulting table becomes

 Name var value1 value2
 Abc aa 123 10
 Abc ab 123 10
 Abc ac 123 10
 Bcd bb 32 21
 Bcd ba 32 21
 Fgr cc 12 34
 Fgr ca 12 34
 Fgr cb 12 34
 Fgr cd 12 34

I would be really appreciate if some one could tell me about how should i approach this ... thank you in advance ....

more ▼

asked Sep 24, 2012 at 07:04 PM in Default

avatar image

48 3 3 6

Forgive me, I'm not sure I understand correctly. How do you determine that for every AA there should be 3 entries, BB just 2 entries and CC will have 4? Where does that magic number come from? Is it set in stone somewhere?

Sep 24, 2012 at 08:25 PM JohnM

Also, what version of SQL Server are you using?

Sep 24, 2012 at 08:26 PM JohnM

@JohnM sorry about that but i am having another table storing these values say

 actual var
 aa     aa
 aa     ab
 aa     ac
 bb     bb
 bb     ba

cc ....

i am using SQL server 2005 ...

Sep 24, 2012 at 08:35 PM abhi2702
  • for voting an answer.

Sep 25, 2012 at 04:17 AM Tim
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

You could do a join between the two tables to get the results, then you can put it in another table or create a view as needed.

It would look something like:

 select a.name, b.actual as var, c.value1, c.value2
 from tblTheFirst a
 join tblTheSecond b
 on a.var = b.actual
more ▼

answered Sep 24, 2012 at 08:48 PM

avatar image

15.6k 22 51 38

Beat me to it. ;-)

Sep 24, 2012 at 08:54 PM JohnM

I looked after he answered your questions ;-)

Sep 24, 2012 at 09:34 PM TimothyAWiseman

@timothy n @johnM

it worked .... thank you guys for the help ...

Sep 24, 2012 at 09:37 PM abhi2702
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Sep 24, 2012 at 07:04 PM

Seen: 705 times

Last Updated: Sep 25, 2012 at 04:17 AM

Copyright 2016 Redgate Software. Privacy Policy