CODEWITHSUNDEEP
sqlsql-servercountpivotunpivot
güven
güven

Reputation: 33

Count number of values across multiple columns

I have a table with 11 columns. The first column includes the category names. The remaining 10 columns have values like white, green, big, damaged etc. and these values can change in time.

I need a SQL query to find how many are there in table (in 10 columns) each value.

Table 1: 

+------------+------------+
|  ID        | decription |
+------------+------------+
| 1          | white      |
| 2          | green      |
| 3          | big        |
| 4          | damaged    |
+------------+------------+

Table 2: 

+------------+-----------+-----------+-----------+
|  CATEGORY  | SECTION 1 | SECTION 2 | SECTION 3 |
+------------+-----------+-----------+-----------+
| Category 1 | white     | green     | big       |
| Category 2 | big       | damaged   | white     |
| Category 1 | white     | green     | big       |
| Category 3 | big       | damaged   | white     |
+------------+-----------+-----------+-----------+

Desired result:

+------------+-------+-------+-----+---------+
|  CATEGORY  | White | Green | Big | Damaged |
+------------+-------+-------+-----+---------+
| Category 1 |    20 |    10 |   9 |      50 |
| Category 2 |    25 |    21 |  15 |       5 |
+------------+-------+-------+-----+---------+

Is it possible doing like this dynamically just as query ?

its on MS sql in visual studio reporting

Thanks

Upvotes: 3

Views: 3304

Answers (2)

Taryn
Taryn

Reputation: 247720

You've got yourself a bit of a mess with the design and the desired result. The problem is that your table is denormalized and then the final result you want is also denormalized. You can get the final result by unpivoting your Section columns, then pivoting the values of those columns. You further add to the mess by needing to do this dynamically.

First, I'd advise you to rethink your table structure because this is far too messy to maintain.

In the meantime, before you even think about writing a dynamic version to get the result you have to get the logic correct via a static or hard-coded query. Now, you didn't state which version of SQL Server you are using but you first need to unpivot the Section columns. You can use either the UNPIVOT function or CROSS APPLY. Your query 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 gets your data into the format:

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

Now you have multiple Category rows - one for each value that previously were in the Section columns. Since you want a total count of each word in the Category, you can now apply the pivot 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 that you want but now you need this to be done dynamically. You'll have to use dynamic SQL which will create a SQL string that will be executed giving you the final result.

If the number of columns to UNPIVOT is limited, then you will create a list of the new column values in a string and then execute it similar to:

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 unpivot, then your process will be a bit more complicated. You'll need to generate a string with the columns to unpivot, you can use the sys.columns table 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'll need to get a list of the new column values - but since these are dynamic we will need to generate this list with a bit of work. You'll need to unpivot the table to generate the list of values into a temporary table for use. Create a temp table to store the values:

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

Load the temp table with the data that you need to unpivot:

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'll see that your data looks the same as the static version above. Now you need to create a string with the values from the temp table that will 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 all this 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 you 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 would 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 still 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 |

Upvotes: 4

Mudassir Hasan
Mudassir Hasan

Reputation: 28751

select category,
       SUM(CASE when section1='white' then 1 when section2='white' then 1 when section3='white' then 1 else 0 end) as white,
       SUM(CASE when section1='green' then 1 when section2='green' then 1 when section3='green' then 1 else 0 end) as green,
       SUM(CASE when section1='damaged' then 1 when section2='damaged' then 1 when section3='damaged' then 1 else 0 end) as damaged,
       SUM(CASE when section1='big' then 1 when section2='big' then 1 when section3='big' then 1 else 0 end) as big
from test
group by category

SQLFiddle

You can extend more to n section values as shown above gor section1,section2,section3

Upvotes: 1

Related Questions