Grouping set of values into multiple groups

Database
MBR
Enthusiast

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,

3 REPLIES
Junior Contributor

Re: Grouping set of values into multiple groups

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-141...

MBR
Enthusiast

Re: Grouping set of values into multiple groups

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,

Enthusiast

Re: Grouping set of values into multiple groups

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