Solved

Stored procedure to save column values in array

Posted on 2011-09-13
10
218 Views
Last Modified: 2012-05-12
Hello,

MySQL = “SELECT * from contacts”

set oRST = connString.execute(mySQL)

do while oRST.EOF
For i = 1 to 60
      c(i) = oRST("contact" & i)
      if c(i) > 0 then
            callcount = callcount + 1
      end if
      ctid(i) = oRST("contact" & i & "typeID")
Next

oRSt.MoveNext
loop
set oRST = nothing

How can we do very similar functionality within stored procedure?

Thanks,
andrishelp
0
Comment
Question by:andrishelp
[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
  • 5
  • 4
10 Comments
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36531761
Just to make sure we are on the same page about:
MySQL = “SELECT * from contacts”

set oRST = connString.execute(mySQL)

do while oRST.EOF
   For i = 1 to 60
      c(i) = oRST("contact" & i)
      if c(i) > 0 then
            callcount = callcount + 1
      end if
      ctid(i) = oRST("contact" & i & "typeID")
   Next

   oRSt.MoveNext
loop
set oRST = nothing

Open in new window

- select all records from contacts table
- for each row, there are 60 columns like contactN (e.g., "contact1", "contact2", ..., "contact60") that contain integer values
- if integer value is > 0 then increment a counter "callcount"
- additionally return contactNtypeID?

If I got all of this correctly, you could do something like this with UNPIVOT:
;WITH contacts(ContactID, Contact1, Contact60, Contact1TypeID, Contact60TypeID) AS (
   SELECT 1, 2, 3, 'A', 'B'
   UNION SELECT 2, 1, 4, 'B', 'A'
)
SELECT *
FROM (SELECT ContactID, Contact1, Contact60 FROM contacts) v
UNPIVOT (ContactValue FOR ContactNo IN (Contact1, Contact60)) upvt_v
JOIN (SELECT ContactID, Contact1TypeID, Contact60TypeID FROM contacts) t
UNPIVOT (ContactType FOR ContactTypeNo IN (Contact1TypeID, Contact60TypeID)) upvt_t
   ON upvt_t.ContactID = upvt_v.ContactID 
   AND REPLACE(ContactTypeNo, 'TypeID', '') = ContactNo

Open in new window


As you see the actual implementation may get involved with 60 columns total, so you may have to combine this with some other techniques.

But now that the data is normalized, you can GROUP BY ContactID and do COUNT() or SUM() or any other aggregate you want on columns like ContactValue.

If you can confirm what exactly you need, then can help further. To test in your environment, use the query beginning with SELECT, i.e., you don't need the ;WITH ... () part.

Kevin
0
 

Author Comment

by:andrishelp
ID: 36532272
Kevin,

Thanks for your response. I think you did answer my question.

- select all records from contacts table
- for each row, there are 60 columns like contactN (e.g., "contact1", "contact2", ..., "contact60") that contain integer values
- if integer value is > 0 then increment a counter "callcount"
- for each row, there are 60 columns like contactNtypeID (e.g., "contact1typeID","contact2typeID",...,"contact60typeID") that also contains integer values

I have tried this query as you mentioned, but it's only display the "contact1" and "contact1typeID" values. What about other 59 columns value? Do I have to write 60 SELECT statements for that?

SELECT *
FROM (SELECT ContactID, Contact1, Contact60 FROM contacts) v
UNPIVOT (ContactValue FOR ContactNo IN (Contact1, Contact60)) upvt_v
JOIN (SELECT ContactID, Contact1TypeID, Contact60TypeID FROM contacts) t
UNPIVOT (ContactType FOR ContactTypeNo IN (Contact1TypeID, Contact60TypeID)) upvt_t
   ON upvt_t.ContactID = upvt_v.ContactID
   AND REPLACE(ContactTypeNo, 'TypeID', '') = ContactNo


0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36532369
Not 59 more selects, but you would have to add the other 59 columns to the respective derived table(s) and unpivot clauses. What I gave you was meant as an example with contacts 1 and 60...did only contact one show? But anyway, it is the con to unpivot that can be coded around with dynamic SQL. Let me see if I can mock something more complete up. Can you provide the CREATE DDL for the contacts table please?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:andrishelp
ID: 36532423
Here you go...
CREATE TABLE [dbo].[tempSalescalldata](
	[SalesCallID] [int] NULL,
	[contact1] [int] NULL,
	[contact1typeID] [int] NULL,
	[contact2] [int] NULL,
	[contact2typeID] [int] NULL,
	[contact3] [int] NULL,
	[contact3typeID] [int] NULL,
	[contact4] [int] NULL,
	[contact4typeID] [int] NULL,
	[contact5] [int] NULL,
	[contact5typeID] [int] NULL,
	[contact6] [int] NULL,
	[contact6typeID] [int] NULL,
	[contact7] [int] NULL,
	[contact7typeID] [int] NULL,
	[contact8] [int] NULL,
	[contact8typeID] [int] NULL,
	[contact9] [int] NULL,
	[contact9typeID] [int] NULL,
	[contact10] [int] NULL,
	[contact10typeID] [int] NULL,
	[contact11] [int] NULL,
	[contact11typeID] [int] NULL,
	[contact12] [int] NULL,
	[contact12typeID] [int] NULL,
	[contact13] [int] NULL,
	[contact13typeID] [int] NULL,
	[contact14] [int] NULL,
	[contact14typeID] [int] NULL,
	[contact15] [int] NULL,
	[contact15typeID] [int] NULL,
	[contact16] [int] NULL,
	[contact16typeID] [int] NULL,
	[contact17] [int] NULL,
	[contact17typeID] [int] NULL,
	[contact18] [int] NULL,
	[contact18typeID] [int] NULL,
	[contact19] [int] NULL,
	[contact19typeID] [int] NULL,
	[contact20] [int] NULL,
	[contact20typeID] [int] NULL,
	[contact21] [int] NULL,
	[contact21typeID] [int] NULL,
	[contact22] [int] NULL,
	[contact22typeID] [int] NULL,
	[contact23] [int] NULL,
	[contact23typeID] [int] NULL,
	[contact24] [int] NULL,
	[contact24typeID] [int] NULL,
	[contact25] [int] NULL,
	[contact25typeID] [int] NULL,
	[contact26] [int] NULL,
	[contact26typeID] [int] NULL,
	[contact27] [int] NULL,
	[contact27typeID] [int] NULL,
	[contact28] [int] NULL,
	[contact28typeID] [int] NULL,
	[contact29] [int] NULL,
	[contact29typeID] [int] NULL,
	[contact30] [int] NULL,
	[contact30typeID] [int] NULL,
	[contact31] [int] NULL,
	[contact31typeID] [int] NULL,
	[contact32] [int] NULL,
	[contact32typeID] [int] NULL,
	[contact33] [int] NULL,
	[contact33typeID] [int] NULL,
	[contact34] [int] NULL,
	[contact34typeID] [int] NULL,
	[contact35] [int] NULL,
	[contact35typeID] [int] NULL,
	[contact36] [int] NULL,
	[contact36typeID] [int] NULL,
	[contact37] [int] NULL,
	[contact37typeID] [int] NULL,
	[contact38] [int] NULL,
	[contact38typeID] [int] NULL,
	[contact39] [int] NULL,
	[contact39typeID] [int] NULL,
	[contact40] [int] NULL,
	[contact40typeID] [int] NULL,
	[contact41] [int] NULL,
	[contact41typeID] [int] NULL,
	[contact42] [int] NULL,
	[contact42typeID] [int] NULL,
	[contact43] [int] NULL,
	[contact43typeID] [int] NULL,
	[contact44] [int] NULL,
	[contact44typeID] [int] NULL,
	[contact45] [int] NULL,
	[contact45typeID] [int] NULL,
	[contact46] [int] NULL,
	[contact46typeID] [int] NULL,
	[contact47] [int] NULL,
	[contact47typeID] [int] NULL,
	[contact48] [int] NULL,
	[contact48typeID] [int] NULL,
	[contact49] [int] NULL,
	[contact49typeID] [int] NULL,
	[contact50] [int] NULL,
	[contact50typeID] [int] NULL,
	[contact51] [int] NULL,
	[contact51typeID] [int] NULL,
	[contact52] [int] NULL,
	[contact52typeID] [int] NULL,
	[contact53] [int] NULL,
	[contact53typeID] [int] NULL,
	[contact54] [int] NULL,
	[contact54typeID] [int] NULL,
	[contact55] [int] NULL,
	[contact55typeID] [int] NULL,
	[contact56] [int] NULL,
	[contact56typeID] [int] NULL,
	[contact57] [int] NULL,
	[contact57typeID] [int] NULL,
	[contact58] [int] NULL,
	[contact58typeID] [int] NULL,
	[contact59] [int] NULL,
	[contact59typeID] [int] NULL,
	[contact60] [int] NULL,
	[contact60typeID] [int] NULL
) ON [PRIMARY]

Open in new window

0
 

Author Comment

by:andrishelp
ID: 36532446
Yes, It shows only "contact1" value not "contact60" when I run that query.
0
 

Author Comment

by:andrishelp
ID: 36532461
Actually, Contact60 did not have any values for each row, that's why it did not display.
0
 

Author Comment

by:andrishelp
ID: 36532501

Now I know how that select query works. But I have one question, what is the purpose of writing this "AND REPLACE(ContactTypeNo, 'TypeID', '') = ContactNo" to that select query. since it's giving me the same result without it too.

0
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 250 total points
ID: 36532805
Yes, NULL values will be excluded, which is why it fits your need here as it will automatically do some filtering for your callcount.
DECLARE @con_cols VARCHAR(1000), @typ_cols VARCHAR(1000), @sql VARCHAR(4000);

/* set column names to variables using XML concatenation trick */
SET @con_cols = STUFF((SELECT ','+COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'tempSalescalldata'
AND COLUMN_NAME LIKE 'contact%[0-9]'
ORDER BY ORDINAL_POSITION
FOR XML PATH('')), 1, 1, '');

SET @typ_cols = STUFF((SELECT ','+COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'tempSalescalldata'
AND COLUMN_NAME LIKE 'contact%[0-9]typeID'
ORDER BY ORDINAL_POSITION
FOR XML PATH('')), 1, 1, '');

/* create dynamic SQL string using column names collected above */
SET @sql = 'SELECT upvt_v.*, upvt_t.ContactType
FROM (SELECT [SalesCallID], ' + @con_cols + ' FROM [dbo].[tempSalescalldata]) v
UNPIVOT (ContactValue FOR ContactNo IN (' + @con_cols + ')) upvt_v
JOIN (SELECT [SalesCallID], ' + @typ_cols + ' FROM [dbo].[tempSalescalldata]) t
UNPIVOT (ContactType FOR ContactTypeNo IN (' + @typ_cols + ')) upvt_t
   ON upvt_t.[SalesCallID] = upvt_v.[SalesCallID]
   AND upvt_t.ContactTypeNo = upvt_v.ContactNo + ''typeID''';

PRINT (@sql); /* shows SQL, can execute manually at command line */
EXEC (@sql); /* executes the dynamic SQL string */

Open in new window

0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36532822
If contact60 column was not NULL what you would have seen is that you would have had multiple rows that need to be correlated. Basically the correlation is 'contact1' = 'contact1typeid' (if you remove the 'typeid' piece -- alternatively, you can add 'typeid' to 'contact1' which is what the new script does).
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36533489
And of course this would be a good time to ask:  What were you thinking when you designed that table that way?  Or is this something you inherited and cannot change?  If you think converting the data using PIVOT is tough then you have not seen anything yet.

If you can are able this table is screaming:  "Please, normalize me!" :)
0

Featured Post

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

751 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