All Forums Database
MBR 29 posts Joined 01/13
12 Aug 2015
Grouping set of values into multiple groups

Hi All,
Below is the scenario
i have codes column in this there are multiple values
 

codes
001
002
003
004
005
006
007
008
009
010
 A
 H
 M
 P
 Z
02
03
04
05
06
07
08

now i need to group 3 or 4 values into one group. like below

001, 002 ,003 into one group
004,005,006 into one group like wise.

Actual scenario:
we are fetching the columns

sel col1,col2,sum(col3),sum(col4) from tbl
group by 1,2

Expecting scenario: (adding 'codes' field)

sel col1,codes,col2,sum(col3),sum(col4) from tbl
group by 1,2,3

Note: here codes field has multiple rows as above mentioned and those values we have to combine into multiple groups.
Please suggest me how we can do this.
 
Thanks,
 
 

Bala
dnoeth 4628 posts Joined 11/04
12 Aug 2015

I don't fully understand what you're trying to do.
Seems like a GROUP_CONCAT aggregate, see
http://forums.teradata.com/forum/database/convert-a-column-into-a-comma-separated-list-0#comment-141343

Dieter

MBR 29 posts Joined 01/13
13 Aug 2015

Thanks Dieter for your reply,
Scenario is like this, i have 10 values in a field which are coming from source and i need to assign a value(ex: 1 or 2 or 3 etc..) for each 3 values like grouping.
Ex: from source below are the values i am getting
1,2,3,4,5,6,7,8,9,10
now i need to assign a value like below
for values 1,2,3 i need to assign 1
for values 4,5,6 i need to assign 2
for values 7,8,9,10  i need to assign 3
i have achieved this by using CASE statement for above scenario is there any other method which we can use to achieving this.
(case when col1 in('0','1','2','3','4','5') then 1
   when col1 in('6','7','8','9','10','14') then 2
   when col1 in('17','22','23','25','51','52') then 3
   else 4
   end) as col1
Thanks,

Bala

yuvaevergreen 93 posts Joined 07/09
14 Aug 2015

AFAIK, case would be the best solution for the scenario, if i understand correctly.

You must sign in to leave a comment.