SQL select variables from table-1 and return count for IDs

1
votes

Table-A

color_id- color
1- green
2- blue
3- orange
.
.
55- yellow

Table-B

person-id, color
1, green
1, blue
2, orange
3, yellow
3, blue

There are 55+ colors in table A used by thousands of persons, I need to return count of each color used by each person i.e

it should return result like 

person_id, green, orange, blue, ......, yellow
1, 1,0,1,0,....0
2,0,1,0,....,0
3,0,0,1,...1

The problem is that there are 55+ different color types, they all are saved in Table-A, I can write a select statement and then type all these 55 different variables and get their counts, is their a quick way to select variables from Table-A and then return their count instead of select person_id, green count().......yellow count()

I am using SQL server 2014.

Thanks

2
sqlsql-server
Why do you store the color string in two places? The second table should have color_id, not color. - Gordon Linoff
A simple join and group by should handle this just fine. - durbnpoisn

2 Answers

0
votes

you can use a Dynamic Pivot here.. since your TableB has the actual color, instead of the color id, you can just pivot the one table..

DECLARE @cols VARCHAR(MAX)

SELECT @cols = COALESCE(@cols + ',','') + '[' + color + ']' FROM TableA 

DECLARE @sql VARCHAR(MAX)

SET @Sql = 'SELECT person_id, ' + @cols + ' FROM TableB PIVOT (COUNT(color) FOR color IN (' + @cols + ')) p'

EXEC (@Sql)
0
votes
SELECT * 
FROM tableName
 PIVOT (COUNT(color) 
        FOR color
        IN (green,blue,Orange,yellow)  --<-- just mention all the colors here once
        )p