Number of numbers in multiple columns

You have a bit of a mess with the design and the desired result. The problem is your table is denormalized and then the end result you want is denormalized as well. The end result can be obtained by dividing the columns Section

and then rotating the values ​​of those columns. You are also adding to the clutter by requiring it to be done dynamically.

First, I would advise you to rethink your table structure because it is too messy to maintain.

In the meantime, before you even think about writing a dynamic version to get the result, you need to get the correct logic with a static or hardcoded query. Now, you haven't specified which version of SQL Server you are using, but first you need to disable the columns Section

. You can use either the UNPIVOT or CROSS APPLY function. Your request will start with something similar to the following:

select 
  category,
  value
from yourtable 
unpivot
(
  value for cols in (Section1,Section2,Section3)
) u

      

        
        
        
      

    

See SQL Fiddle with Demo . This results in your data being in the format:

|   CATEGORY |   VALUE |
|------------|---------|
| Category 1 |   white |
| Category 1 |   green |
| Category 1 |     big |
| Category 2 |     big |
| Category 2 | damaged |
| Category 2 |   white |

      

        
        
        
      

    

You now have multiple rows Category

- one for each value that was previously in the columns Section

. Since you want to get the total count of each word in Category

, you can now apply the rotation function:

select 
  category,
  white, green, big, damaged
from
(
  select 
    category,
    value
  from yourtable 
  unpivot
  (
    value for cols in (Section1,Section2,Section3)
  ) u
) un
pivot
(
  count(value)
  for value in (white, green, big, damaged)
) p;

      

        
        
        
      

    

See SQL Fiddle with Demo . This will give you the result you want, but now you need it to be done dynamically. You will have to use dynamic SQL, which will create a SQL string to be executed giving the final result.

If the number of columns in UNPIVOT is limited, you will create a list of new column values ​​per row and then execute it, similarly:

DECLARE @query  AS NVARCHAR(MAX),
    @colsPivot as  NVARCHAR(MAX);


select @colsPivot 
        = STUFF((SELECT ',' + quotename(SectionValue)
                 from yourtable
                 cross apply
                 (
                   select Section1 union all
                   select Section2 union all
                   select Section3
                 ) d (SectionValue)                
                 group by SectionValue
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query 
  = 'select category, '+@colspivot+'
      from
      (
        select 
          category,
          value
        from yourtable 
        unpivot
        (
          value
          for cols in (Section1, Section2, Section3)
        ) un
      ) x
      pivot
      (
        count(value)
        for value in ('+ @colspivot +')
      ) p'

exec sp_executesql @query 

      

        
        
        
      

    

See SQL Fiddle with Demo

If you have an unknown number of columns to fail, then your process is a little more complicated. You will need to generate a row with columns to fail, you can use a table sys.columns

to get this list:

select @colsUnpivot 
    = stuff((select ','+quotename(C.name)
             from sys.columns as C
             where C.object_id = object_id('yourtable') and
                   C.name like 'Section%'
             for xml path('')), 1, 1, '')

      

        
        
        
      

    

Then you need to get a list of the new column values, but since they are dynamic we will need to generate this list with a little work. You will need to detach the table in order to generate the list of values ​​into a temporary table for use. Create a temporary table to store values:

create table #Category_Section
(
    Category varchar(50),
    SectionValue varchar(50)
);

      

        
        
        
      

    

Load temp table with data you need to disable:

set @unpivotquery 
  = 'select 
        category,
        value
      from yourtable 
      unpivot
      (
        value for cols in ('+ @colsUnpivot +')
      ) u'

insert into #Category_Section exec(@unpivotquery);

      

        
        
        
      

    

See SQL Fiddle with Demo . You will see that your data looks the same as the static version above. Now you need to create a row with values ​​from the temp table to be used in the final query:

select @colsPivot 
        = STUFF((SELECT ',' + quotename(SectionValue)
                 from #Category_Section
                 group by SectionValue
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

      

        
        
        
      

    

Once you have it all, you can put it together into a final query:

DECLARE @colsUnpivot AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX),
    @colsPivot as  NVARCHAR(MAX),
    @unpivotquery  AS NVARCHAR(MAX);

select @colsUnpivot 
    = stuff((select ','+quotename(C.name)
             from sys.columns as C
             where C.object_id = object_id('yourtable') and
                   C.name like 'Section%'
             for xml path('')), 1, 1, '');

create table #Category_Section
(
    Category varchar(50),
    SectionValue varchar(50)
);

set @unpivotquery 
  = 'select 
        category,
        value
      from yourtable 
      unpivot
      (
        value for cols in ('+ @colsUnpivot +')
      ) u';

insert into #Category_Section exec(@unpivotquery);

select @colsPivot 
        = STUFF((SELECT ',' + quotename(SectionValue)
                 from #Category_Section
                 group by SectionValue
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query 
  = 'select category, '+@colspivot+'
      from
      (
        select 
          category,
          value
        from yourtable 
        unpivot
        (
          value
          for cols in ('+ @colsunpivot +')
        ) un
      ) x
      pivot
      (
        count(value)
        for value in ('+ @colspivot +')
      ) p'

exec sp_executesql @query 

      

        
        
        
      

    

See SQL Fiddle with Demo . All versions will get the end result:

|   CATEGORY | BIG | DAMAGED | GREEN | WHITE |
|------------|-----|---------|-------|-------|
| Category 1 |   2 |       0 |     2 |     2 |
| Category 2 |   1 |       1 |     0 |     1 |
| Category 3 |   1 |       1 |     0 |     1 |

      

        
        
        
      

    

If your values ​​are stored in a separate table, then you should generate your list of values ​​from that table:

DECLARE @query  AS NVARCHAR(MAX),
    @colsPivot as  NVARCHAR(MAX);


select @colsPivot 
        = STUFF((SELECT ',' + quotename(decription)
                 from descriptions             
                 group by decription
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query 
  = 'select category, '+@colspivot+'
      from
      (
        select 
          category,
          value
        from yourtable 
        unpivot
        (
          value
          for cols in (Section1, Section2, Section3)
        ) un
      ) x
      pivot
      (
        count(value)
        for value in ('+ @colspivot +')
      ) p'

exec sp_executesql @query 

      

        
        
        
      

    

See SQL Fiddle with Demo and get the same result:

|   CATEGORY | BIG | DAMAGED | GREEN | WHITE |
|------------|-----|---------|-------|-------|
| Category 1 |   2 |       0 |     2 |     2 |
| Category 2 |   1 |       1 |     0 |     1 |
| Category 3 |   1 |       1 |     0 |     1 |