Solved

Stored procedure to save column values in array

Posted on 2011-09-13
10
219 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
Independent Software Vendors: 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

How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

Question has a verified solution.

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

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

627 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