Solved

Stored procedure to save column values in array

Posted on 2011-09-13
10
215 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 59

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 59

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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

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 59

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 59

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
passing parameter in sql procedure 9 68
Strange msg in the SSMS pane 13 60
LAG_ROWID - how do I get the right order using this query? 2 22
Change this SQL to get all nodes 3 37
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
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.
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

762 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