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

abhi2702 gravatar image

48 3 3 4

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
+1 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

TimothyAWiseman gravatar image

15.6k 21 23 32

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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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: 590 times

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