Clever Geek Handbook

Multiple Fields Pivot Query in SQL

Data table structure:
id1, id2, id3, id4, ... (some other fields).
I want to create a pivot query to find out how many times the ID value is used in each column.


data 1,2,3,4,2008
2,3,5,1,2008
1,3,2,5,2007
1,2,3,6,2007
3,1,2,5,2007

For a value of 1, the result should be 1,0,0,1,2008
2,1,0,0,2007

How to do it with one query (in MySQL).

+1


source to share


4 answers


This seems to be the best solution (from the Wiki ):



select years,
sum(1*(1-abs(sign(id1-56)))) as id1,
sum(1*(1-abs(sign(id2-56)))) as id2,
sum(1*(1-abs(sign(id3-56)))) as id3,
sum(1*(1-abs(sign(id4-56)))) as id4,
from mytable
group by years
+1


source


Use the characteristic function or delta:

DECLARE @look_for AS int
SET @look_for = 1

SELECT SUM(CASE WHEN id1 = @look_for THEN 1 ELSE 0 END) AS id1_count
    ,SUM(CASE WHEN id2 = @look_for THEN 1 ELSE 0 END) AS id2_count
    ,SUM(CASE WHEN id3 = @look_for THEN 1 ELSE 0 END) AS id3_count
    ,SUM(CASE WHEN id4 = @look_for THEN 1 ELSE 0 END) AS id4_count
FROM tbl


There are ways to generate code (also a method using PIVOT and UNPIVOT in SQL Server, which is not ANSI) based on your table and various ID values.

+1


source


select

(select count (id1) from t1 where id1 = @param) as id1,

(select count (id2) from t2 where id2 = @param) as id2

0


source


if X is the id value you are looking for, you would do it something like this.

select (select count(*) where id1 = X) as countid1 ... etc
-1


source






More articles:


All Articles