Aggregate cateric values ​​to SQL logical columns

Question

I want to collapse my data set to facilitate data mining. Each categorical column should be changed to several Boolean columns. I have a column with categorical values, for example:

ID col1 1 A 2 B 3 A 

I am looking for a way to expand this table and have an aggregate function telling me if this identifier has the value A or B:

Result:

  ID col1A col1B 1 1 0 2 0 1 3 1 0 

I tried using PIVOT, but I have no idea which aggregate function to use inside it.

Also searched for answers in SF, but could not find ...

I am using MS-SQL 2012.

Any help would be appreciated! Omri

EDIT:

The number of categories in col1 is unknown, so the solution must be dynamic. Thanks:)

Answer 1

try the following:

 select ID, col1A=(case when col1='A' then 1 else 0 end), col1B=(case when col1='B' then 1 else 0 end) from <table> 

IF you have one identifier with A and B, and you want to have a separate identifier in the output, which you could do

  select ID, col1A=max(case when col1='A' then 1 else 0 end), col1B=max(case when col1='B' then 1 else 0 end) from <table> group by id 

EDIT

According to your comment, if you do not know the number of options for col1, you can go to dynamic PIVOT

 DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX) select @cols = STUFF((SELECT distinct ',' + QUOTENAME(col1) from <table> FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'') set @query = 'SELECT id, ' + @cols + ' from <table> pivot ( count([col1]) for col1 in (' + @cols + ') ) p ' print(@query) execute(@query) 

SQL Fiddle Demo