Select multiple column from multiple rows and return rows with total count

ok, this is kind of complicated to explain, i will try my best, you开发者_开发百科 are welcome to ask questions if you dont understand me.

i have a table desiged like this:

TITLE | BRANCH | BRANCH1 | BRANCH2 | BRANCH3

a BRANCH (any of them) might contain "bank","credit" and so on

what i want to do, is return rows with distinct branches and count, where the title equals "xxx"

something like:

BANK | xxx Results

CREDIT | xxx Results and so on.

If you can't alter your tables to conform to 1FN, following should get you started

• UNPIVOT the columns using a UNION ALL giving you a resultset that conforms to 1FN.
• Make this into a subselect
• Filter on the TITLE from this subselect
• GROUP BY and COUNT the results

SQL Statement

SELECT  BRANCH, COUNT(*)
FROM    (
          SELECT TITLE, BRANCH FROM MyTable 
          UNION ALL SELECT TITLE, BRANCH1 FROM MyTable 
          UNION ALL SELECT TITLE, BRANCH2 FROM MyTable 
          UNION ALL SELECT TITLE, BRANCH3 FROM MyTable 
        ) q
WHERE   TITLE = 'ATitle'        
GROUP BY
        BRANCH