Solved

How to return records from Pivot View with multiple criteria

Posted on 2012-04-13
16
329 Views
Last Modified: 2012-08-14
I am trying to figure out a way to return records from a PivotView based on multiple selections from two list boxes.  Below is the code for retrieving records based on selections made from two comboboxes (See Original code below.  It worked fine, however the requirements have changed and now management wants to be able to make multiple selections from two list boxes so that essentially @Review and @Status would each contain a comma delimited string each string consisting of multiple reviews and statuses respectively.  I think what need to do is to build the where clause via the following pseudocode.  Basically I need some brainstorming

--------------------Psuedocode
For Each Review in @Review
       ADD the following to the Where
            Review1 = ISNULL(@Review, Review1) OR
            Review2 = ISNULL(@Review, Review2) OR
            Review3 = ISNULL(@Review, Review3) OR
            Review4 = ISNULL(@Review, Review4) OR
            Review5 = ISNULL(@Review, Review5) OR
            Review6 = ISNULL(@Review, Review6) OR
            Review7 = ISNULL(@Review, Review7) OR
            Review8 = ISNULL(@Review, Review8)
NEXT

For Each Status in @Review
      Add the following to the where clause
            Status1 = ISNULL(@Status, Status1) OR
            Status2 = ISNULL(@Status, Status2) OR
            Status3 = ISNULL(@Status, Status3) OR
            Status4 = ISNULL(@Status, Status4) OR
            Status5 = ISNULL(@Status, Status5) OR
            Status6 = ISNULL(@Status, Status6) OR
            Status7 = ISNULL(@Status, Status7) OR
            Status8 = ISNULL(@Status, Status8)
NEXT

------------------------------------ORIGINAL CODE-----------------------------------
CREATE PROC [dbo].[spRetrieveByReviewOrStatusREV]
@Review AS varchar(500) = NULL,
@Status AS varchar(500) = NULL
AS




SELECT USI, WorkStream, GFP, Review1, Status1, Review2, Status2, Review3, Status3, Review4, Status4,
            Review5, Status5, Review6, Status6, Review7, Status7, Review8, Status8
FROM [dbo].[vwPivotedReviewsRevised]
WHERE
(
Review1 = ISNULL(@Review, Review1) OR
Review2 = ISNULL(@Review, Review2) OR
Review3 = ISNULL(@Review, Review3) OR
Review4 = ISNULL(@Review, Review4) OR
Review5 = ISNULL(@Review, Review5) OR
Review6 = ISNULL(@Review, Review6) OR
Review7 = ISNULL(@Review, Review7) OR
Review8 = ISNULL(@Review, Review8)
)
AND
(
Status1 = ISNULL(@Status, Status1) OR
Status2 = ISNULL(@Status, Status2) OR
Status3 = ISNULL(@Status, Status3) OR
Status4 = ISNULL(@Status, Status4) OR
Status5 = ISNULL(@Status, Status5) OR
Status6 = ISNULL(@Status, Status6) OR
Status7 = ISNULL(@Status, Status7) OR
Status8 = ISNULL(@Status, Status8)
)
      

ORDER BY USI
GO
0
Comment
Question by:chtullu135
  • 11
  • 5
16 Comments
 

Author Comment

by:chtullu135
Comment Utility
I've been working on it and I think I'm making some headway.


CREATE PROC [dbo].[spRetrieveByReviewOrStatusREV]
@Review AS varchar(500) = NULL,
@Status AS varchar(500) = NULL
AS

DECLARE @R varchar(max)
SET @R = @Review
SET @R = @R + ','

DECLARE @S varchar(max)
set @S = @Status
set @S = @S + ','

Declare @SqlString as varchar(max)
SET @SqlString =      'SELECT USI, WorkStream, GFP, Review1, Status1, Review2, Status2, Review3, Status3, Review4, Status4,
                              Review5, Status5, Review6, Status6, Review7, Status7, Review8, Status8
                              FROM [dbo].[vwPivotedReviewsRevised] WHERE '
WHILE len(@R)>0
BEGIN
@SqlString = @SqlString +
                                          (
                                          Review1 = ISNULL(@R, Review1) OR
                                          Review2 = ISNULL(@R, Review2) OR
                                          Review3 = ISNULL(@R, Review3) OR
                                          Review4 = ISNULL(@R, Review4) OR
                                          Review5 = ISNULL(@R, Review5) OR
                                          Review6 = ISNULL(@R, Review6) OR
                                          Review7 = ISNULL(@R, Review7) OR
                                          Review8 = ISNULL(@R, Review8)
                                          )

END
AND
WHILE len(@S)>0
BEGIN
@SqlString = @SqlString + 'AND'
                                          (
                                          Status1 = ISNULL(@S, Status1) OR
                                          Status2 = ISNULL(@S, Status2) OR
                                          Status3 = ISNULL(@S, Status3) OR
                                          Status4 = ISNULL(@S, Status4) OR
                                          Status5 = ISNULL(@S, Status5) OR
                                          Status6 = ISNULL(@S, Status6) OR
                                          Status7 = ISNULL(@S, Status7) OR
                                          Status8 = ISNULL(@S, Status8)
                                          )
END      

ORDER BY USI
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
0
 

Author Comment

by:chtullu135
Comment Utility
To make it simpler to code, I'm going to work on one section at a time.  I will be first working on the Review portion

ALTER PROC [dbo].[spRetrieveByReviewOrStatusREV]
@Review AS varchar(500) = NULL,
@Status AS varchar(500) = NULL
AS
DECLARE @R varchar(max)
SET @R = @Review
SET @R = @R + ','

Declare @SqlFrom as varchar(max)
SET @SqlFrom = 'SELECT USI, WorkStream, GFP, Review1, Status1, Review2, Status2, Review3, Status3, Review4, Status4,
                              Review5, Status5, Review6, Status6, Review7, Status7, Review8, Status8
                              FROM [dbo].[vwPivotedReviewsRevised] WHERE Review'
DECLARE @Where as varchar(max)

WHILE len(@R)>0
BEGIN
      SET @Where = (
                                          Review1 = ISNULL(@R, Review1) OR
                                          Review2 = ISNULL(@R, Review2) OR
                                          Review3 = ISNULL(@R, Review3) OR
                                          Review4 = ISNULL(@R, Review4) OR
                                          Review5 = ISNULL(@R, Review5) OR
                                          Review6 = ISNULL(@R, Review6) OR
                                          Review7 = ISNULL(@R, Review7) OR
                                          Review8 = ISNULL(@R, Review8))

END

DECLARE @Sql as varchar(max)
SET @Sql = @SqlFrom + ' ' + @Where
0
 
LVL 41

Expert Comment

by:ralmada
Comment Utility
>>essentially @Review and @Status would each contain a comma delimited string each string consisting of multiple reviews and statuses respectively<<

If I understood correctly

you can just try this where clause without the dynamic SQL there:

WHERE
(
',' + Review1 + ',' LIKE '%,' + @Review ',%' OR
',' + Review2 + ',' LIKE '%,' + @Review ',%' OR
',' + Review3 + ',' LIKE '%,' + @Review ',%' OR
',' + Review4 + ',' LIKE '%,' + @Review ',%' OR
',' + Review5 + ',' LIKE '%,' + @Review ',%' OR
',' + Review6 + ',' LIKE '%,' + @Review ',%' OR
',' + Review7 + ',' LIKE '%,' + @Review ',%' OR
',' + Review8 + ',' LIKE '%,' + @Review ',%'
)
AND
(
',' + Status1 + ',' LIKE '%,' + @Status1 ',%' OR
',' + Status2 + ',' LIKE '%,' + @Status2 ',%' OR
',' + Status3 + ',' LIKE '%,' + @Status3 ',%' OR
',' + Status4 + ',' LIKE '%,' + @Status4 ',%' OR
',' + Status5 + ',' LIKE '%,' + @Status5 ',%' OR
',' + Status6 + ',' LIKE '%,' + @Status6 ',%' OR
',' + Status7 + ',' LIKE '%,' + @Status7 ',%' OR
',' + Status8 + ',' LIKE '%,' + @Status8 ',%'
)
0
 
LVL 41

Expert Comment

by:ralmada
Comment Utility
or if you create the function below you can use it in your WHERE clause like this

WHERE
(
Review1 in (select value from dbo.parmstolist(@Review)) OR
Review2 in (select value from dbo.parmstolist(@Review)) OR
Review3 in (select value from dbo.parmstolist(@Review)) OR
Review4 in (select value from dbo.parmstolist(@Review)) OR
Review5 in (select value from dbo.parmstolist(@Review)) OR
Review6 in (select value from dbo.parmstolist(@Review)) OR
Review7 in (select value from dbo.parmstolist(@Review)) OR
Review8 in (select value from dbo.parmstolist(@Review))
)
AND (
Status1 in (select Value from dbo.parmstolist(@Status)) OR
Status2 in (select Value from dbo.parmstolist(@Status)) OR
Status3 in (select Value from dbo.parmstolist(@Status)) OR
Status4 in (select Value from dbo.parmstolist(@Status)) OR
Status5 in (select Value from dbo.parmstolist(@Status)) OR
Status6 in (select Value from dbo.parmstolist(@Status)) OR
Status7 in (select Value from dbo.parmstolist(@Status)) OR
Status8 in (select Value from dbo.parmstolist(@Status))
)



-----------------------------------
create FUNCTION [dbo].[ParmsToList] (@Parameters varchar(8000), @delimiter varchar(10) )
returns @result TABLE (Value varchar(500), rn int identity)
AS
begin
declare @dx varchar(9)
-- declare @loops int
--set @loops = 0

DECLARE @TempList table
(
Value varchar(500)
)

if @delimiter is null set @delimiter = ' '
if len(@delimiter) < 1 set @delimiter = ' '
set @dx = left(@delimiter, case when @delimiter = ' ' then 1 else len(@delimiter) end -1)

DECLARE @Value varchar(8000), @Pos int

SET @Parameters = LTRIM(RTRIM(@Parameters))+ @delimiter
SET @Pos = CHARINDEX(@delimiter, @Parameters, 1)

IF REPLACE(@Parameters, @delimiter, @dx) <> ''
BEGIN
WHILE @Pos > 0 -- AND @Loops < 100
BEGIN
--set @loops = @loops + 1
SET @Value = LTRIM(RTRIM(LEFT(@Parameters, @Pos - 1)))
IF @Value <> ''
BEGIN
INSERT INTO @TempList (Value) VALUES (@Value) --Use Appropriate conversion
END
SET @Parameters = SUBSTRING(@Parameters, @Pos+ case when @delimiter = ' ' then 1 else len(@delimiter) end, 8000)
SET @Pos = CHARINDEX(@delimiter, @Parameters, 1)

END
END
INSERT @result
SELECT value
FROM @TempList
RETURN
END
0
 

Author Comment

by:chtullu135
Comment Utility
Basically @Review would contain the data in the following format.
Loss Mitigation, Complaints, Bankruptcy
@Status would contain the respective data in the following format
345, 566, 2             where the numbers represent the statusid code,  I was using the status names but figured using the id numbers would improve performance a little bit more
0
 
LVL 41

Expert Comment

by:ralmada
Comment Utility
So have you tried my suggestions?
0
 

Author Comment

by:chtullu135
Comment Utility
I'll be trying it tomorrow at work.  I'm really interested in the performance gain.
0
 

Author Comment

by:chtullu135
Comment Utility
Hello ralmada,

The dynamic sql method though while it works is way to messy.  I've been trying your method that employs a function however I am getting the following error message

Msg 102, Level 15, State 1, Procedure spRetrieveByReviewOrStatusRev3, Line 12
Incorrect syntax near ','.

Now I did test the function via the following and it worked fine, so I'm not sure what is going on

SELECT * from dbo.ParmsToList('Complaints,Bankruptcy', ',')
CREATE PROC [dbo].[spRetrieveByReviewOrStatusRev3]
@Review AS varchar(max) = NULL,
@Status as varchar(max) = NULL
AS
DECLARE @Delimiter As varchar(10)
SET @Delimiter = ','
SELECT USI, WorkStream, GFP, Review1, Status1, Review2, Status2, Review3, Status3, Review4, Status4,
                              Review5, Status5, Review6, Status6, Review7, Status7, Review8, Status8
                              FROM [dbo].[vwPivotedReviewsRevised]
WHERE
(
Review1 in (select value from dbo.parmstolist(@Review), ',') OR
Review2 in (select value from dbo.parmstolist(@Review), ',') OR
Review3 in (select value from dbo.parmstolist(@Review), ',') OR
Review4 in (select value from dbo.parmstolist(@Review), ',') OR
Review5 in (select value from dbo.parmstolist(@Review), ',') OR
Review6 in (select value from dbo.parmstolist(@Review), ',') OR
Review7 in (select value from dbo.parmstolist(@Review), ',') OR
Review8 in (select value from dbo.parmstolist(@Review), ',')
)
AND (
Status1 in (select Value from dbo.parmstolist(@Status), ',') OR
Status2 in (select Value from dbo.parmstolist(@Status), ',') OR
Status3 in (select Value from dbo.parmstolist(@Status), ',') OR
Status4 in (select Value from dbo.parmstolist(@Status), ',') OR
Status5 in (select Value from dbo.parmstolist(@Status), ',') OR
Status6 in (select Value from dbo.parmstolist(@Status), ',') OR
Status7 in (select Value from dbo.parmstolist(@Status), ',') OR
Status8 in (select Value from dbo.parmstolist(@Status), ',')
)
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:chtullu135
Comment Utility
Never mind I found the problem,
Review1 in (select value from dbo.parmstolist(@Review), ',') should be
Review1 in (select value from dbo.parmstolist(@Review, ','))
0
 
LVL 41

Expert Comment

by:ralmada
Comment Utility
yep, extra brackets there

same with @Status
0
 

Author Comment

by:chtullu135
Comment Utility
It's working.  However, I want to account for when one or both input parameters to the stored procedure are null
0
 
LVL 41

Accepted Solution

by:
ralmada earned 500 total points
Comment Utility
just add this

WHERE
(
@Review is null OR
Review1 in (select value from dbo.parmstolist(@Review, ',') OR
Review2 in (select value from dbo.parmstolist(@Review, ',') OR
Review3 in (select value from dbo.parmstolist(@Review, ',') OR
Review4 in (select value from dbo.parmstolist(@Review, ',') OR
Review5 in (select value from dbo.parmstolist(@Review, ',') OR
Review6 in (select value from dbo.parmstolist(@Review, ',') OR
Review7 in (select value from dbo.parmstolist(@Review, ',') OR
Review8 in (select value from dbo.parmstolist(@Review, ',')
)
AND (
@Status is null OR
Status1 in (select Value from dbo.parmstolist(@Status, ',') OR
Status2 in (select Value from dbo.parmstolist(@Status, ',') OR
Status3 in (select Value from dbo.parmstolist(@Status, ',') OR
Status4 in (select Value from dbo.parmstolist(@Status, ',') OR
Status5 in (select Value from dbo.parmstolist(@Status, ',') OR
Status6 in (select Value from dbo.parmstolist(@Status, ',') OR
Status7 in (select Value from dbo.parmstolist(@Status, ',') OR
Status8 in (select Value from dbo.parmstolist(@Status, ',')
)
0
 

Author Comment

by:chtullu135
Comment Utility
This is what I'm thinking to deal with nulls
ALTER PROC [dbo].[spRetrieveByReviewOrStatusRev3]
@Review AS varchar(max) = NULL,
@Status as varchar(max) = NULL
AS
DECLARE @Delimiter As varchar(10)
SET @Delimiter = ','
SELECT USI, WorkStream, GFP, Review1, Status1, Review2, Status2, Review3, Status3, Review4, Status4,
                              Review5, Status5, Review6, Status6, Review7, Status7, Review8, Status8
                              FROM [dbo].[vwPivotedReviewsRevised]

WHERE

(
Review1 in (select value from dbo.parmstolist(@Review,',')) OR
Review2 in (select value from dbo.parmstolist(@Review,',')) OR
Review3 in (select value from dbo.parmstolist(@Review,',')) OR
Review4 in (select value from dbo.parmstolist(@Review,',')) OR
Review5 in (select value from dbo.parmstolist(@Review,',')) OR
Review6 in (select value from dbo.parmstolist(@Review,',')) OR
Review7 in (select value from dbo.parmstolist(@Review,',')) OR
Review8 in (select value from dbo.parmstolist(@Review,','))
)
AND (

IF @Status IS NOT NULL THEN
      BEGIN
            Status1 in (select Value from dbo.parmstolist(@Status,',')) OR
            Status2 in (select Value from dbo.parmstolist(@Status,',')) OR
            Status3 in (select Value from dbo.parmstolist(@Status,',')) OR
            Status4 in (select Value from dbo.parmstolist(@Status,',')) OR
            Status5 in (select Value from dbo.parmstolist(@Status,',')) OR
            Status6 in (select Value from dbo.parmstolist(@Status,',')) OR
            Status7 in (select Value from dbo.parmstolist(@Status,',')) OR
            Status8 in (select Value from dbo.parmstolist(@Status,','))
      END
ELSE
      BEGIN
            Status1 LIKE ' % ' OR
            Status2 LIKE ' % ' OR
            Status3 LIKE ' % ' OR
            Status4 LIKE ' % ' OR
            Status5 LIKE ' % ' OR
            Status6 LIKE ' % ' OR
            Status7 LIKE ' % ' OR
            Status8 LIKE ' % ' OR
      END
)
0
 

Author Comment

by:chtullu135
Comment Utility
I just saw what you had posted for dealing with nulls.  It can't get any simpler - thanks
0
 

Author Comment

by:chtullu135
Comment Utility
Everything is working.  Although the dynamic sql method worked, the code you posted was much simpler and is easier to maintain.  I've learned quite a bit.  Thanks again.
0
 

Author Closing Comment

by:chtullu135
Comment Utility
Thanks for your help.  I've learned a lot.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

771 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

10 Experts available now in Live!

Get 1:1 Help Now