-1

I'm trying to make a query and i'm having a bad time with one thing. Suppose I have a table that looks like this:

id Sample Species Quantity Group
1 1 AA 5 A
2 1 AB 6 A
3 1 AC 10 A
4 1 CD 15 C
5 1 CE 20 C
6 1 DA 13 D
7 1 DB 7 D
8 1 EA 6 E
9 1 EF 4 E
10 1 EB 2 E

In the table I filter to have just 1 sample (but i have many), it has the species, the quantity of that species and a functional group (there are only five groups from A to E). I would like to make a query to group by the samples and make columns of the counts of the species of certain group, something like this:

Sample N_especies Group A Group B Group C Group D Group E
1 10 3 0 2 2 3

So i have to count the species (thats easy) but i don't know how to make the columns of a certain group, can anyone help me?

Alvaro Jorquera
  • 1
  • Turning rows into columns is called a "pivot". If you search on that term you can find examples and discussions. Conditional aggregation (as already noted) is much easier for simple situations like this with a fixed number of "values". Reporting tools can also easily do this if a "report" is your actual goal. – SMor Nov 04 '21 at 12:13
  • Does this answer your question? [Convert Rows to columns using 'Pivot' in SQL Server](https://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server) – Pred Nov 04 '21 at 12:31

1 Answers1

0

You can use PIVOT :

Select a.Sample,[A],[B],[C],[D],[E], [B]+[A]+[C]+[D]+[E] N_especies  from 
(select t.Sample,t.Grp from [WS_Database].[dbo].[test1] t) t
  PIVOT (
  COUNT(t.Grp)
  for t.Grp in ([A],[B],[C],[D],[E])
  ) a
Ali Fidanli
  • 1,342
  • 8
  • 12