Is a sort of foreach statement possible in t-sql?

user_15361018 0
Viewed 72

I have data like this and I am trying to make a table based on t-sql that allows me to see for each unique CPR if the table has a line with both paymenttype 23 and 26, which is the case with CPR 123 below.

It should be fairly simple, but I am new to t-sql and haven't found solutions. Years back with SAS I vaguely recall using a "foreach" statement for stuff like this.

Any help would be very appreciated.

CPR Paymenttype
123 23
123 26
111 28
144 26
144 27
foreach tsql sql-server
2 Answers

Here's how to do this in more-or-less portable SQL. It's a job for GROUP BY ... HAVING.

SELECT CPR
  FROM tbl
 WHERE PaymentType IN (23,26)
 GROUP BY CPR
HAVING COUNT(*) = 2;

If you were looking for items with any combination of three out of five PaymentTypes, you could do this.

SELECT CPR
  FROM tbl
 WHERE PaymentType IN (23, 26, 28, 50, 31)
 GROUP BY CPR
HAVING COUNT(*) = 3;

The trick here is to remember SQL is a declarative language, not a procedural language. You describe the set of results you need, and SQL figures out how to get it for you. foreach is a procedural sort of operation.

user_2056080

Regarding whether T-SQL supports ForEach

While there is no ForEach, there are similar constructs (like WHILE loop and CURSOR etc.) that can be used to simulate a ForEach loop. However, I would advise you not to use them, unless you are out of options.

Regarding solution to your problem

There are many ways. Here is one easy way to do it using co-related query:

SELECT DISTINCT CPR
FROM YourTable T
WHERE EXISTS(SELECT * FROM YourTable WHERE CPR = T.CPR AND Paymenttype = 23)
  AND EXISTS(SELECT * FROM YourTable WHERE CPR = T.CPR AND Paymenttype = 26)
user_12522280
Related