Q&A for professionals and students of engineering
WITH reports as (SELECT 1 Report_Number, 123 writer1, 1 loc1, 456 writer2, 2 loc2 UNION SELECT 2, 456, 2, 789, 3 UNION SELECT 3, 789, 3, 123, 1 UNION SELECT 4, 789, 3, 123, 1 UNION SELECT 5, 456, 2, 789, 3 UNION SELECT 6, 123, 1, 456, 2 UNION SELECT 7, 123, 1, 789, 3 UNION SELECT 8, 456, 2, 123, 1 UNION SELECT 9, 789, 3, 123, 1 UNION SELECT 10, 123, 1, 456, 2) SELECT COUNT(report_number), writer FROM (SELECT report_number, writer1, writer2 FROM reports ) p UNPIVOT (writer FOR writer_type IN ( writer1, writer2) )AS unpvt GROUP BY writer