0

Let's say I have this dataset:

Client_ID Food        House_Type
123       Fruits      A
123       Fruits      B
124       Fruits      B
124       Vegetables  B
124       Vegetables  C
125       Vegetables  C

I'm looking for this output:

Client_ID Food_Fruits Food_Vegetables House_Type_A House_Type_B House_Type_C
123       2           0               1            1            0
124       1           2               0            2            1
125       0           1               0            0            1

It's kind of similar to this question but still that function looks for strings. I'm just looking to count different categories into columns of values (Is there a specific name for the process I'm looking for? It's kind of a mixture of getting the dummies and grouping)

I'm using Teradata SQL Assistant, but also I would try to adapt a Teradata solution.

Chris
  • 2,019
  • 5
  • 22
  • 67
  • This is called *pivoting*, either using *conditional aggregation* or the PIVOT operator in newer releases. How many different values exist and are those values known in advance? – dnoeth May 17 '21 at 17:43
  • I think I don't understand clearly the difference between conditional aggregation and PIVOT operator in newer releases. About your question, I don't know what you mean specifically if the values are known in advance, but I can get them with a SELECT DISTINCT. I have three columns where I want to "pivot" them. The first of them has 2 distinct values, the second has 3 distinct values, and the last one has 19 distinct values. – Chris May 17 '21 at 17:56

1 Answers1

1

The easiest (and most flexible) way is probably conditional aggregation:

SELECT Client_ID
  ,Count(CASE WHEN Food = 'Fruits'     THEN 1 end) AS Food_Fruits
  ,Count(CASE WHEN Food = 'Vegetables' THEN 1 end) AS Food_Vegetables
  ,Count(CASE WHEN House_Type = 'A'    THEN 1 end) AS House_Type_A
  ,Count(CASE WHEN House_Type = 'B'    THEN 1 end) AS House_Type_B
  ,Count(CASE WHEN House_Type = 'C'    THEN 1 end) AS House_Type_C
FROM vt
GROUP BY 1

Newer versions of Teradata support PIVOT syntax, which actually creates conditional aggregation automatically, but as you want to pivot two columns you need to UNPIVOT first.

dnoeth
  • 59,503
  • 4
  • 39
  • 56
  • This works! could be possible for you to give a possible alternative for multiple distinct values? I have in this case 19, sometimes I will have more and writing each line specifically for each value can be tedious. So I'm wondering if this problem can be solved in a generalizable/scaled way. – Chris May 17 '21 at 18:04
  • To get these value dynamically, you need *Dynamic SQL* generating the list of values and based on this the finaly Select – dnoeth May 17 '21 at 18:12