looping with SQL

Hopefully this is a better explanation of what I tried to explain earlier.
Below is an example of a dataset I build as a #wkfile. It consists of a
count/identity, Company, AccountNo, and Title. I’m trying to loop thru wi
th
SQL to pull out one occurrence of Company Name and AccountNo, into a 2nd
dataset. When there are more than one occurrence, the priority is first -
CEO’ OR like ‘%CEO%’, second - ‘CFO’ or like ‘%CFO%’, and if n
either of them
then take the first title.
id Company AccountNo Title
1 national Publishers A2053145611 CEO
2 (i)STRUCTURE, Inc A5051045506 CFO
3 (i)STRUCTURE, Inc 99091268226 Chief Executive Officer
4 (i)STRUCTURE, Inc A3013151569 Finance Director/CFO
5 (i)STRUCTURE, Inc 99091970756 President
6 (i)STRUCTURE, Inc 99091204766 President/CEO
7 (i)STRUCTURE, Inc 99091268239 Vice President
8 (i)STRUCTURE, Inc A5051045492 VP Finance & Service Management
9 (i)STRUCTURE, Inc A5051045541 VP Infrastructure Operations
10 (i)STRUCTURE, Inc A5051045455 VP Marketing
11 (i)STRUCTURE, Inc A5051045467 VP Sales
12 Andel Amba A3013151463 Finance Director/CFO
13 @.Global Inc A1022852020 President
14 @.plan, Inc. 99113038170 Chairman/CEO
15 @.plan, Inc. 99113038390 President/COO
16 @.plan, Inc. A0080460716 Vice President/CFO/CAO
17 @.radical.media, Inc. A4102749756 Chairman & CEO
18 @.radical.media, Inc. A4102749802 COO
19 @.radical.media, Inc. A4102749784 EVP
20 @.Road, Inc. A4071235030 CFOYou don't need to loop. It would have been nice if you supplied exact DDL
and real INSERT statements. Here's an untested solution:
select
o.*
from
MyTable
where
o.id in
(
select top 1
i.id
from
MyTable i
where
i.Company = o.Company
order by
case
when i.Title like '%CEO%' then 1
when i.Title like '%CFO%' then 2
else 3
end
, o.id
)
It's not clear what you mean by the "first" title, so I used id as the
tiebreaker.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Logger" <Logger@.discussions.microsoft.com> wrote in message
news:635B49E6-0714-46FA-997D-B44B4F1F0085@.microsoft.com...
Hopefully this is a better explanation of what I tried to explain earlier.
Below is an example of a dataset I build as a #wkfile. It consists of a
count/identity, Company, AccountNo, and Title. I’m trying to loop thru wi
th
SQL to pull out one occurrence of Company Name and AccountNo, into a 2nd
dataset. When there are more than one occurrence, the priority is first -
CEO’ OR like ‘%CEO%’, second - ‘CFO’ or like ‘%CFO%’, and if n
either of them
then take the first title.
id Company AccountNo Title
1 national Publishers A2053145611 CEO
2 (i)STRUCTURE, Inc A5051045506 CFO
3 (i)STRUCTURE, Inc 99091268226 Chief Executive Officer
4 (i)STRUCTURE, Inc A3013151569 Finance Director/CFO
5 (i)STRUCTURE, Inc 99091970756 President
6 (i)STRUCTURE, Inc 99091204766 President/CEO
7 (i)STRUCTURE, Inc 99091268239 Vice President
8 (i)STRUCTURE, Inc A5051045492 VP Finance & Service Management
9 (i)STRUCTURE, Inc A5051045541 VP Infrastructure Operations
10 (i)STRUCTURE, Inc A5051045455 VP Marketing
11 (i)STRUCTURE, Inc A5051045467 VP Sales
12 Andel Amba A3013151463 Finance Director/CFO
13 @.Global Inc A1022852020 President
14 @.plan, Inc. 99113038170 Chairman/CEO
15 @.plan, Inc. 99113038390 President/COO
16 @.plan, Inc. A0080460716 Vice President/CFO/CAO
17 @.radical.media, Inc. A4102749756 Chairman & CEO
18 @.radical.media, Inc. A4102749802 COO
19 @.radical.media, Inc. A4102749784 EVP
20 @.Road, Inc. A4071235030 CFO

Looping through source connections

I am trying to build a package that loops through different SQL Server connections and puts the result set in a single SQL Server connection. I have looked at the Flat File example but this does not help. How do I loop through a list of SQL Servers (can be in a table, or file) make the connection to that SQL Server, run a script against Master to gather DB names (have this part) and write it to a reporting server (have this part). I have tried the For Loop and For Each Loop, but can't get either to work with SQL connections.

I have seen this question a lot, with no real answers. (Everyone who answers just points back to the Flat File example)

Thanks,

Pete

Let me make sure I understand what you are trying to do.

You want to loop through a table that basically gives you a servername,username and password. And for each servername found you want to connect to that server and execute a sql stmt or stored proc, get the results back and store the results on your reporting server.

Is that correct?

You could use property expressions to dynamically change the connections as you loop through your table.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=75254&SiteID=1

|||Yes you understand it right. The idea you expressed sounds right, but I don't know how to do it.|||

Peter Cwik wrote:

Yes you understand it right. The idea you expressed sounds right, but I don't know how to do it.

Peter,

The reason everyone points to the flatfile example is because the steps that you need to go through are exactly the same. In the Flat File example you are talking about I am assuming (because you did not link to it) that you are setting the ConnectionString property using a property expression. Exactly the same in your scenario.

Can you explain why the Flat File example is not useful to you?

-Jamie

|||

Jamie,

The Flat File example is not helpful because the syntax on the screens is different. When starting out on a new program like integration services specific instructions are needed. For example there are many places to right click and get properties, but if you don't have focus on the correct object the instructions on what to change in a property sheet won't make sense. Often times those that give advice forget the nuances of the programs. While we all laugh at the "idiot" who complains that "I can't click on your computer", we forget that newbies don't know that there is an icon on the main desktop called My Computer. I'm looking for specifics because I haven't done it before and its not intuitive.

I understand the concept of what I need to do, grab a list, parse through it while passing the information to the connection. But how? While the concept is clear there are obviously steps that need to be followed to get it to work. Its easy to get to the moon too, just build a vehicle that can move fast enough escape the earth's gravity. While the concept is understandable, without the specifics your going no where.

Sorry for my little rant, I do need and want help on the specific steps.

Hope this helps,

Peter Cwik

|||I try to expand on the answer Darren gave in the post Jamie linked to above in this one of my own (GD at the number prepositions in this sentence!): http://forums.microsoft.com/MSDN/ShowPost.aspx?postid=843095&siteid=1

I'll admit there are things missing and not specific to your example, but if it gets you started, you may be able to figure the rest out.|||

Peter Cwik wrote:

Jamie,

The Flat File example is not helpful because the syntax on the screens is different. When starting out on a new program like integration services specific instructions are needed. For example there are many places to right click and get properties, but if you don't have focus on the correct object the instructions on what to change in a property sheet won't make sense. Often times those that give advice forget the nuances of the programs. While we all laugh at the "idiot" who complains that "I can't click on your computer", we forget that newbies don't know that there is an icon on the main desktop called My Computer. I'm looking for specifics because I haven't done it before and its not intuitive.

I understand the concept of what I need to do, grab a list, parse through it while passing the information to the connection. But how? While the concept is clear there are obviously steps that need to be followed to get it to work. Its easy to get to the moon too, just build a vehicle that can move fast enough escape the earth's gravity. While the concept is understandable, without the specifics your going no where.

Sorry for my little rant, I do need and want help on the specific steps.

Hope this helps,

Peter Cwik

Peter,

I don't think that response was warranted. People here are more than willing to help. I recommend you try and make your querie more specific though. i.e. Which particular step you are having problems with? If you're having trouble with all of them then let's take them one at a time - starting with the first one.

You are right that you need to "grab a list, parse through it while passing the information to the connection". Which part of that are you having problems with?

Assuming the list is in a database you can get that list into a SSIS variable using an Execute SQL Task.|||

The last step is where I lose it. I have been able to get the list, pass it to a variable. I couldn't figure out how to pass it a connection string (does that mean connection manager, tsql, vb...) I tried passing it to tsql string, which works as long as I have linked server connections, which is not what I want.

I think you may have misread my intent above. I never said that people here are not willing to help. If I thought that I wouldn't be here asking for help. My point is that experts sometimes get used to the higher level functions and assume others are on their level when giving instructions. In SSIS I am not advanced, I admit that, and I need a little hand holding to get going on this particular topic.

As for the flat file example, there are specific screens and prompts for inputing the name of a file and the directory. There is built-in functionality to handle this type of input for flat files but I have not found the same built-in functionality for cycling through SQL connections.

|||

That was pretty close. Thank you.

I had a developer write a VB script that does it all too, but like your example I'm trying to find the built in functionality of SSIS.

Thanks again.

Looping through source connections

I am trying to build a package that loops through different SQL Server connections and puts the result set in a single SQL Server connection. I have looked at the Flat File example but this does not help. How do I loop through a list of SQL Servers (can be in a table, or file) make the connection to that SQL Server, run a script against Master to gather DB names (have this part) and write it to a reporting server (have this part). I have tried the For Loop and For Each Loop, but can't get either to work with SQL connections.

I have seen this question a lot, with no real answers. (Everyone who answers just points back to the Flat File example)

Thanks,

Pete

Let me make sure I understand what you are trying to do.

You want to loop through a table that basically gives you a servername,username and password. And for each servername found you want to connect to that server and execute a sql stmt or stored proc, get the results back and store the results on your reporting server.

Is that correct?

You could use property expressions to dynamically change the connections as you loop through your table.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=75254&SiteID=1

|||Yes you understand it right. The idea you expressed sounds right, but I don't know how to do it.|||

Peter Cwik wrote:

Yes you understand it right. The idea you expressed sounds right, but I don't know how to do it.

Peter,

The reason everyone points to the flatfile example is because the steps that you need to go through are exactly the same. In the Flat File example you are talking about I am assuming (because you did not link to it) that you are setting the ConnectionString property using a property expression. Exactly the same in your scenario.

Can you explain why the Flat File example is not useful to you?

-Jamie

|||

Jamie,

The Flat File example is not helpful because the syntax on the screens is different. When starting out on a new program like integration services specific instructions are needed. For example there are many places to right click and get properties, but if you don't have focus on the correct object the instructions on what to change in a property sheet won't make sense. Often times those that give advice forget the nuances of the programs. While we all laugh at the "idiot" who complains that "I can't click on your computer", we forget that newbies don't know that there is an icon on the main desktop called My Computer. I'm looking for specifics because I haven't done it before and its not intuitive.

I understand the concept of what I need to do, grab a list, parse through it while passing the information to the connection. But how? While the concept is clear there are obviously steps that need to be followed to get it to work. Its easy to get to the moon too, just build a vehicle that can move fast enough escape the earth's gravity. While the concept is understandable, without the specifics your going no where.

Sorry for my little rant, I do need and want help on the specific steps.

Hope this helps,

Peter Cwik

|||I try to expand on the answer Darren gave in the post Jamie linked to above in this one of my own (GD at the number prepositions in this sentence!): http://forums.microsoft.com/MSDN/ShowPost.aspx?postid=843095&siteid=1

I'll admit there are things missing and not specific to your example, but if it gets you started, you may be able to figure the rest out.|||

Peter Cwik wrote:

Jamie,

The Flat File example is not helpful because the syntax on the screens is different. When starting out on a new program like integration services specific instructions are needed. For example there are many places to right click and get properties, but if you don't have focus on the correct object the instructions on what to change in a property sheet won't make sense. Often times those that give advice forget the nuances of the programs. While we all laugh at the "idiot" who complains that "I can't click on your computer", we forget that newbies don't know that there is an icon on the main desktop called My Computer. I'm looking for specifics because I haven't done it before and its not intuitive.

I understand the concept of what I need to do, grab a list, parse through it while passing the information to the connection. But how? While the concept is clear there are obviously steps that need to be followed to get it to work. Its easy to get to the moon too, just build a vehicle that can move fast enough escape the earth's gravity. While the concept is understandable, without the specifics your going no where.

Sorry for my little rant, I do need and want help on the specific steps.

Hope this helps,

Peter Cwik

Peter,

I don't think that response was warranted. People here are more than willing to help. I recommend you try and make your querie more specific though. i.e. Which particular step you are having problems with? If you're having trouble with all of them then let's take them one at a time - starting with the first one.

You are right that you need to "grab a list, parse through it while passing the information to the connection". Which part of that are you having problems with?

Assuming the list is in a database you can get that list into a SSIS variable using an Execute SQL Task.|||

The last step is where I lose it. I have been able to get the list, pass it to a variable. I couldn't figure out how to pass it a connection string (does that mean connection manager, tsql, vb...) I tried passing it to tsql string, which works as long as I have linked server connections, which is not what I want.

I think you may have misread my intent above. I never said that people here are not willing to help. If I thought that I wouldn't be here asking for help. My point is that experts sometimes get used to the higher level functions and assume others are on their level when giving instructions. In SSIS I am not advanced, I admit that, and I need a little hand holding to get going on this particular topic.

As for the flat file example, there are specific screens and prompts for inputing the name of a file and the directory. There is built-in functionality to handle this type of input for flat files but I have not found the same built-in functionality for cycling through SQL connections.

|||

That was pretty close. Thank you.

I had a developer write a VB script that does it all too, but like your example I'm trying to find the built in functionality of SSIS.

Thanks again.

Looping scripts

I have an application that manages the booking of rooms in several different buildings, and am trying to build an SSIS package to build a table containing records showing the total number of hours per day that each building is being used. Because two bookings in the same building can take place at the same time or overlap with each other, it's not a straightforward sum of the hours per booking.

I've written a VBA script that loops through booking records comparing their start and end times, building a total value for each day, and then writing the building id, date and total hours to a table. My problem is that I'm not sure how to do this

in SSIS. The script component operates on row by row basis so doesn't seem to allow looping through records, and the aggregate component doesn't allow scripts.

I'm still pretty new to SSIS so may be missing something obvious, but if anyone has help to offer it would be greatly appreciated.

Thanks,

Tim

You will need an Execute SQL Task, a few variables, and a ForEach loop.

You need to begin with the Exceute SQL Task to read your table for the appropriate records and write them to a ADO RecordSet. The task will need a variable to write the recordset to and so you will need to define a variable of data type "Object" to hold the record set. In the Execute SQL Task set the ResultSet property to "Full Result Set" as you will be retrieving multiple rows. In the ResultSet option you will keep the ResultName to 0 and in the variable you need to choose the name of the variable you would have created.

In the ForEach Loop you need to use the ForEach ADO Enumerator and set the SourceVariable to the variable that holds the recordset. If you are planning to use the values of the columns retrieved from the Execute SQL Task within the ForEach Loop then you would need to define variables to pass them into, and then define the variables in the Variables option for the ForEach Loop.

I hope this helps.

|||

A few altenative approaches:

One, you can use a script component in the data flow to accomplish this. You'd have to capture the rows in an internal data store (a DataTable or array would work), and then process them once you have all the data for a building.

Two, you could use an Execute SQL task as desibull suggests, to get a recordset. However, rather than processing it in a For Each (which is going to introduce the same single record at a time issue), process it in a Script Task.

Three, it seems (at least theoretically) possible to build a SELECT statement which would do this for you. In particular, CTEs in SQL 2005 could help with this type of problem. More detail on the table structure could help determine if that is possible.

Considering you already have the VBA code for processing the data, I'd probably go with option 2.

Loop thru a SQL Table in stored proc?

Hello,

Does anyone know of a way to loop thru a SQL table using code in a stored
procedure?

I need to go thru each record in a small table and build a string using
values from the fields associated with a part number, and I can't find any
way to process each record individually. The string needs to be initialized
with the data associated with the 1st record's part number, and I need to
build the string until a new part number is incurred. Once a new part number
is found in the table, the string is written to a different table and reset
for this next part number in the table. Need to repeat until all records in
the table have been processed.

I use ADO in access 2000 to work thru local recordsets, I just can't find
anyway to do this in a stored SQL procedure.

Thanks for any suggestions, Eric.Thanks Erland, that worked in the procedure.

"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns93BDF3734C7DEYazorman@.127.0.0.1...
> [posted and mailed, please reply in news]
> Eric Martin (ermartin01@.cox.net) writes:
> > Does anyone know of a way to loop thru a SQL table using code in a
stored
> > procedure?
> Most people here know that you should not do this kind of thing, but
> one should always strive for set-based solutions. Then again...
> > I need to go thru each record in a small table and build a string using
> > values from the fields associated with a part number, and I can't find
> > any way to process each record individually. The string needs to be
> > initialized with the data associated with the 1st record's part number,
> > and I need to build the string until a new part number is incurred. Once
> > a new part number is found in the table, the string is written to a
> > different table and reset for this next part number in the table. Need
> > to repeat until all records in the table have been processed.
> This sounds like it be one of the few cases where you need an iterative
> solution. Yet, then again:
> > I use ADO in access 2000 to work thru local recordsets, I just can't
find
> > anyway to do this in a stored SQL procedure.
> Doing this client-side might be just as effecient. So if there is no
> compelling reason for doing this in a stored procedure, you may keep the
> ADO solution - even if it means that the data needs to do some extra
> roundtrips.
> Here is a sample of how such a procedure would look like:
> DECLARE @.partno varchar(10),
> @.lastpartno varchar(10),
> @.otherdata varchar(10),
> @.output varchar(8000),
> @.err int
> DECLARE part_cur CURSOR LOCAL STATIC FOR
> SELECT partno, otherdata FROM tbl ORDER BY partno
> SELECT @.err = @.@.error
> IF @.err <> 0 BEGIN DEALLOCATE part_cur RETURN @.err END
> OPEN part_cur
> WHILE 1 = 1
> BEGIN
> FETCH part_cur INTO @.partno, @.otherdata
> IF @.@.fetch_status <> 0
> BREAK
> IF @.partno <> coalesce(@.lastpartno, '')
> BEGIN
> IF @.lastpartno IS NOT NULL
> BEGIN
> INSERT othertbl (col1) VALUES (@.output)
> SELECT @.err = @.@.error IF @.err <> 0 BREAK
> END
> SELECT @.lastpartno = @.partno, @.output = @.partno
> END
> SELECT @.output = @.output + ', ' + @.otherdata
> END
> DEALLOCATE part_cur
> IF @.err <> 0
> RETURN @.err
> INSERT othertbl (col1) VALUES (@.output)
> SELECT @.err = @.@.error IF @.err <> 0 RETURN @.err
>
>
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.aspsql

loop through a tables columns

is there a way using t-sql to loop through a tables columns?
i want to build a long string with this type of logic
foreach (column in table.columns)
{
strValues += column.name + "=" + row.value
}
thank you for your helpAbraham,
Can you give me some sample data and desired results to work with?
Not exactly sure what you're asking for.
HTH
Jerry
"Abraham Andres Luna" <abe@.rdk.com> wrote in message
news:e8wfGkB0FHA.2460@.TK2MSFTNGP10.phx.gbl...
> is there a way using t-sql to loop through a tables columns?
> i want to build a long string with this type of logic
> foreach (column in table.columns)
> {
> strValues += column.name + "=" + row.value
> }
> thank you for your help
>|||What are you trying to accomplish? Is this supposed to be some kind of
data interchange format? If so, how about using SELECT ... FOR XML to
generate an XML fragment instead?
If you really want to do this dynamically then take a look at this proc
for inspiration:
http://vyaskn.tripod.com/code/generate_inserts.txt
David Portas
SQL Server MVP
--|||Abraham Andres Luna wrote:
> is there a way using t-sql to loop through a tables columns?
> i want to build a long string with this type of logic
> foreach (column in table.columns)
> {
> strValues += column.name + "=" + row.value
> }
>
No, you will need to use a client tool for this. The best you can do in
T-SQL is (air code):
select 'col1=' + cast(col1 as varchar) + ... + '; colN=' + colN from table
I know you want to avoid this exercise, but the only way would be to use a
client tool: for example: an ADO Recordset.
Oh, I suppose you could go to a great deal of trouble to create a dynamic
sql statement by looping through the result of querying the
information_schema.columns view, but I would not advise this.
Bob Barrows
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||ty for the answer, that proc was a big help
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1129225553.008794.38510@.g14g2000cwa.googlegroups.com...
> What are you trying to accomplish? Is this supposed to be some kind of
> data interchange format? If so, how about using SELECT ... FOR XML to
> generate an XML fragment instead?
> If you really want to do this dynamically then take a look at this proc
> for inspiration:
> http://vyaskn.tripod.com/code/generate_inserts.txt
> --
> David Portas
> SQL Server MVP
> --
>

Loop inside View

Hello,

is it possible to build a loop for the following statement?

CREATE VIEW vwObjects as (

Select 2001 as year, 1 as quarter, id as id
from dbo.objects o
where o.edate >= '20010101' and o.sdate < '20010401'
union

Select 2001 as year, 2 as quarter, id as id
from dbo.objects o
where o.edate >= '20010301' and o.sdate < '20010701'
...
union

Select 2002 as year, 1 as quarter, id as id
from dbo.objects o
where o.edate > '20020101' and o.sdate < '20020401'
...
)

I want a kind of calender for my olap cube, so I can get every active object in a special quarter resp year.

Thank you!Huh?

YEAR(edate), MONTH(edate)

What are you trying to do?

And what's with LOOP? I don't see no loop|||Oh,sorry. I have one Table for the objects. Every object as a startdate and an enddate. For my cube, I need kind of dimension, so the user can pick a quarter and he will get the sum of all active objects. I tried several ways to realize this.

My idea is to create of view, that looks like:

year quarter id
2001 1 1
2001 1 2
2001 1 3
2001 2 2
2001 2 4

From objects table:

id startdate enddate
1 2001/05/01 2001/13/02
2 2001/25/02 2001/03/04
3 2001/03/01 2001/5/01
4 2001/09/05 2001/22/05

I hope it's more more understandable now.|||Ok, forget that, I found another way.|||Ok, forget that, I found another way.

Can you elaborate? Your solution may help other users in the future.|||I couldn't solve this. Even if I could, this will be very slow for big tables. I will have to do a little work off on my design and then I will try this loop with a INSERT INTO, not a view. Greets, Silas

Loop

Hi ,
I like to build a query to generate a virtual running number from the
table.
Something like "Select @.X , fieldA from TableA" . The result should look
like ,
@.x FieldA
-- --
1 AAA
2 BBB
3 CCC
Please help
Travis Tan
On Wed, 5 Oct 2005 00:04:04 -0700, Travis wrote:

>Hi ,
> I like to build a query to generate a virtual running number from the
>table.
>Something like "Select @.X , fieldA from TableA" . The result should look
>like ,
>@.x FieldA
>-- --
>1 AAA
>2 BBB
>3 CCC
>Please help
Hi Travis,
Maybe something like this?
SELECT COUNT(*) AS [@.x], a.FieldA
FROM TableA AS a
INNER JOIN TableA AS b
ON b.FieldA <= a.FieldA
GROUP BY a.FieldA
(untested - see www.aspfaq.com/5006 if you prefer a tested solution)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Lookup task based on a variable

This doesn't seem possible but I'll ask anyway...

Can I build a lookup task where the lookup query is based on a variable, rather than hardcoding the SQL staement?

No, there is no support for this.

The best you can do is write a query and then use values in your input data as parameters, to help filter the rows. Seems obvious, but often I have got stuck thinking I wanted a parameterised query, so that results are filtered before I do the lookup, which cannot be done. This is probably not as effcient, but the closest we have.

|||

That's what I figured...

On the bright side, I was able to use another new SQL Server 2005 feature...

SELECT RecId, CAST(COUNT(*) AS Char(1)) [PVL]
FROM VT
WHERE Elect IN (

SELECT Elect
FROM (
SELECT State,PVLE1,PVLE2,PVLE3,PVLE4
FROM StateOptions) opt
UNPIVOT
(Elect For State IN (PVL1, PVL2, PVL3, PVL4)) AS UPV

)

GROUP BY RecId

Of course this would have been much simpler if the options table had been built vertically instead of horizontally!