- Home /

There are an interesting query problem I am facing. I am trying solve using single query, opposite to use of temporary tables or string concatenation functions.

Basically, I have Table “T” with an integer id and 6 populated columns A, B, C, D, E, F with integer values. I have to populate columns A1, A2, A3, A4, A5, A6 where:

A1 value should be populated with the number of occurrences of value in the field A over fields A, B, C, D, E, F in the same row

B1 value should be populated with the number of occurrences of value in the field B over fields A, B, C, D, E, F in the same row

Etc…

Sample of values with calculated values for A1, A2, A3, A4, A5, A6 after running a query should be:

sample.png
(9.9 kB)

Comment

**Answer** by Oleg
·
Dec 27, 2018 at 08:22 PM

This problem is actually pretty simple, except that the question description is somewhat confusing. There is a request to populate columns** A1, A2, A3, A4, A5, A6**, while the image displays columns named **A1, B1, C1, D1, E1** and **F1**. The explanation of the requirement mentions columns **A1, B1**, etc. It would be best to ask the question accurately enough as not to cause any ambiguity in what actually needs to be done.

Assuming that there are no NULL values in any of the columns, one possible solution is to simply compare every column value to the value in the given column and sum up if the values are the same. Since the value in any of the columns is always going to be equal to itself, the smallest resulting value in columns A1, B1, C1, D1, E1 and F1 (I picked this one from the available combinations) is going to be 1 while the maximum value is 6 (because there are 6 columns). Here is the script which will work as expected:

select A, B, C, D, E, F, 1 + case when A = B then 1 else 0 end + case when A = C then 1 else 0 end + case when A = D then 1 else 0 end + case when A = E then 1 else 0 end + case when A = F then 1 else 0 end A1, case when B = A then 1 else 0 end + 1 + case when B = C then 1 else 0 end + case when B = D then 1 else 0 end + case when B = E then 1 else 0 end + case when B = F then 1 else 0 end B1, case when C = A then 1 else 0 end + case when C = B then 1 else 0 end + 1 + case when C = D then 1 else 0 end + case when C = E then 1 else 0 end + case when C = F then 1 else 0 end C1, case when D = A then 1 else 0 end + case when D = B then 1 else 0 end + case when D = C then 1 else 0 end + 1 + case when D = E then 1 else 0 end + case when D = F then 1 else 0 end D1, case when E = A then 1 else 0 end + case when E = B then 1 else 0 end + case when E = C then 1 else 0 end + case when E = D then 1 else 0 end + 1 + case when E = F then 1 else 0 end E1, case when F = A then 1 else 0 end + case when F = B then 1 else 0 end + case when F = C then 1 else 0 end + case when F = D then 1 else 0 end + case when F = E then 1 else 0 end + 1 F1 from YourTable

Using the sample data in question, the query produces the following output:

A B C D E F A1 B1 C1 D1 E1 F1 ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- 0 0 2 2 3 3 2 2 2 2 2 2 0 2 2 2 3 1 1 3 3 3 1 1 3 4 1 3 1 2 2 1 2 2 2 1 3 0 0 1 1 3 2 2 2 2 2 2 0 4 1 2 3 3 1 1 1 1 2 2

Hope this helps.

Oleg

Sql 2005 Case Clause Query 2 Answers

query is returning the wrong results 1 Answer

need to change one of the joins 1 Answer

Need some help Please 2 Answers

Issue using the "in Sub query" 3 Answers

Copyright 2019 Redgate Software. Privacy Policy

- Anonymous
- Sign in
- Create
- Ask a question
- Spaces
- Ask SSC Site Issues (meta-askssc)
- Explore
- Topics
- Questions
- Users
- Badges