Solved

Stored procedure to save column values in array

Posted on 2011-09-13
10
207 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
  • 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
 

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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql query help 7 96
Stored procedure query with if 27 36
Delete from table 6 45
Find data in a column which is not in  a date format 29 10
If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

910 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now