There are two columns in the table: COL_LEFT and COL_RIGHT.

I need to select all rows where value of FOO occurs in either of these two columns and generate two columns:

1. Column with the other value that is on the same row with FOO ie value from the other column (COL_LEFT or COL_RIGHT)

2. Column with a number of occurences of that other value

I am guessing there would be GROUP BY in the query but I am not sure how to construct the whole query.

I am using MSSQLLocalDB database.

Here is the example of how it should work:

Thanks a lot.

CodePudding user response：

A simple solution would be the following.

SELECT
  v.OPPOSITE,
  COUNT(*) AS COUNT
FROM YourTable t
CROSS APPLY (VALUES(
    CASE WHEN t.COL_LEFT = 'FOO' THEN t.COL_RIGHT ELSE t.COL_LEFT END
)) v(OPPOSITE)
WHERE 'FOO' IN (t.COL_LEFT, t.COL_RIGHT)
GROUP BY
  v.OPPOSITE;

But I think the most efficient solution would either of the following two:

SELECT
  t.OPPOSITE,
  COUNT(*) AS COUNT
FROM (
    SELECT
      t.COL_RIGHT AS OPPOSITE
    FROM YourTable t
    WHERE t.COL_LEFT = 'FOO'

    UNION ALL

    SELECT
      t.COL_LEFT
    FROM YourTable t
    WHERE t.COL_RIGHT = 'FOO'
) t
GROUP BY
  t.OPPOSITE;

SELECT
  t.OPPOSITE,
  SUM(t.COUNT) AS COUNT
FROM (
    SELECT
      t.COL_RIGHT AS OPPOSITE,
      COUNT(*) AS COUNT
    FROM YourTable t
    WHERE t.COL_LEFT = 'FOO'
    GROUP BY
      t.COL_RIGHT

    UNION ALL

    SELECT
      t.COL_LEFT,
      COUNT(*)
    FROM YourTable t
    WHERE t.COL_RIGHT = 'FOO'
    GROUP BY
      t.COL_LEFT
) t
GROUP BY
  t.OPPOSITE;

This would need two separate indexes, with the columns in opposite order, to be performant.

(COL_LEFT, COL_RIGHT)
(COL_RIGHT, COL_LEFT)

As you can see from this fiddle, the second and third execution plans have no sorts or hash matches, whereas the first requires a sort.

CodePudding user response：

Thinking that you can do with:

1. Filter records with [LEFT] or [RIGHT] is 'Foo'.
2. Place the value which is not 'Foo' in [LEFT] column.
3. Group by [LEFT] column and count.

WITH CTX AS (
    SELECT CASE WHEN [LEFT] = 'Foo' THEN [RIGHT] ELSE [LEFT] END AS [LEFT]
    FROM Ori
    WHERE [LEFT] = 'Foo'
    OR [RIGHT] = 'Foo'
)
SELECT [LEFT], COUNT(*) AS [COUNT]
FROM CTX
GROUP BY [LEFT]

Sample SQL Fiddle