How to 'binarize' a table by SQL

Question

For example there is a table Employee:

id gender   age
--------------------
1  Male     32
2  Female   26
3  Female   45
4  NA       31

I'd like to create another table and insert data like:

id gender_Male gender_Female gender_NA age_26 age_31 age_32 age_45
--------------------------------------------------------------------
1  1           0             0         0       0     1      0
2  0           1             0         1       0     0      0          
3  0           1             0         0       0     0      1          
4  0           0             1         0       1     0      0

Why don't you do normalize your data? Otherwise you will need so many columns representing all possible human ages... — B001ᛦ, Aug 28 '17 at 09:20
@bub I'm just giving an example. Actually the range of column values won't be large. — Deqing, Aug 28 '17 at 09:28
_the range of column values won't be large_ Even then your approach is technically the wrong one. — B001ᛦ, Aug 28 '17 at 09:33
This is called `One-Hot Encoding` in Machine Learning. Not sure why it's technically wrong in SQL? @bub — Deqing, Aug 28 '17 at 09:50
If you want to record or manipulate an arbitrary data structure in an arbitrary way go ahead, just realize that that's what you're doing. The relation(ship)/association here is on id, gender & age. Of course every query result is another relation(ship)/association. But one suspects that you are not interested in this result qua relation(ship)/association but as something to further map to another data structure and/or as formatted output representing the input. Such results are seldom useful for further relational querying. PS Downvotes are likely lack of research, this is a faq (pivoting). — philipxy, Aug 28 '17 at 10:44

score 0 · Answer 1 · answered Aug 28 '17 at 09:28

0

Use CASE to specify every field as either a 1 or 0.

SELECT
 e.[id],
 CASE WHEN e.gender = 'Male' THEN 1 ELSE 0 END AS gender_Male,
 CASE WHEN e.gender = 'Female' THEN 1 ELSE 0 END AS gender_Female,
 CASE WHEN e.gender = 'NA' THEN 1 ELSE 0 END AS gender_NA
INTO
 newTable
FROM
 Employee e

answered Aug 28 '17 at 09:28

cddt

539
5
14

Thanks for the answer. Is it possible to automatically generate the column name? For example if there is another record has gender "unknown" then a column "gender_unknown" can be created without modifying the script. – Deqing Aug 28 '17 at 09:32
2

Yes, for that you will need dynamic SQL. This is a frequently asked question, so please check out the accepted answer here: https://stackoverflow.com/questions/2554826/dynamic-sql-to-generate-column-names. Please post back if you are still having difficulty. – cddt Aug 28 '17 at 09:59

score 0 · Answer 2 · answered Aug 28 '17 at 10:10

You can get your result by using Pivot and dynamic SQL -

Create table #Source  (
id  int ,
gender  nvarchar(6) ,
Age tinyint)
go

insert into  #Source 
 select 1, 'Male' ,  51 
 union all select 2, 'Male' ,   42 
 union all select 4, 'Female' ,   52 
 union all select 5, 'Male' ,   45 
 union all select 6, 'Male' ,   25 
 union all select 7, 'Male' ,   31 
 union all select 8, 'NA' ,   24 

 declare @sql nvarchar(max) , @SelectList nvarchar(max) , @Pvt1 nvarchar(max) , @Pvt2 nvarchar(max)

 select @SelectList = 'id ,[Male] as  [gender_Mail] ,Female as  [gender_Female] , NA as [gender_NA] , ' , @Pvt1 = '' , @Pvt2 = ''
 select @SelectList = @SelectList + '['  + cast(age as varchar(3)) + '] as [age_'  + cast(age as varchar(3)) + '] , '  from #Source
 select @SelectList = SUBSTRING(@SelectList ,1, LEN(@SelectList)-1) 

 select @Pvt1 = ' [Male], [Female] , [NA]'   
 select @Pvt2       =  @Pvt2 +  '['  + cast(age as varchar(3))+ '] , '  from #Source
 select @Pvt2 = SUBSTRING(@Pvt2 ,1, LEN(@Pvt2)-1)

 select @sql = N'select '+@SelectList+ '
  from #Source  s 
  PIVOT
(
    count(gender)
    FOR gender IN ('+ @Pvt1+ ')
)AS pvt1 PIVOT
(
    count(age)
    FOR age IN (' + @Pvt2 + ')
)AS pvt2'

exec sp_executesql  @sql

How to 'binarize' a table by SQL

2 Answers2