SQL - Converting one value into two or more possible values-CodePudding

I have one table I would like to convert into another set of values.

Table A's Letter Column	Converts into
A	AA AND AB
B	BB
C	CC

So, what I am doing is...

SELECT all the values from Table A
Convert Table A's values using a CASE

CASE TABLE A COLUMN
 WHEN 'A' THEN 'AA'
 WHEN 'A' THEN 'AB'
 WHEN 'B' THEN 'BB'
 WHEN 'C' THEN 'CC'
 ELSE NULL
END

Of course, a CASE statement can only have one outcome and thus I can't get it show both 'AA' and 'AB' when it converts 'A' from Table A. What can I do in this situation?

The expected outcome I want is that it would show (AA, AB, BB, and CC) using the input of (A, B, and C).

Let me know if this sounds confusing! Thank you!

EDIT: For clarification... I modified the orignal above and added details below.

I have three values that I pulled from one table using a SELECT.
I want these three values to be shown in the converted form as 'AA', 'AB', 'BB', and 'CC'.

CodePudding user response：

You would do this with an additional table and join. So you have table to map the Letter column from TableA to the desired outputs:

Letter	MappedValue
A	AA
A	AB
B	BB
C	CC

Letter	MappedValue
A	AA,AB
B	BB
C	CC

Now you can bring this in with a JOIN. Note that you should strongly prefer the first option. Delimited data in a column is rarely, if ever, the right choice. However, I'm more of a Sql Server guy, and I'm not sure what Hive has in place of Sql Server's string_agg(), which you need in order to avoid spreading the results over multiple records.

Additionally, if you are unable to create the table, Sql Server has a feature called Table Value Constructors that can used to include this mapping directly in the single SQL statement. This feature is actually part of the ansi standard, but it's one of those parts of the standard that tend to get lost by some of the more recent SQL-like projects and again: I'm not sure how it will look in Hive.

CodePudding user response：

You can join with subquery:

with mytable as (--this is example, use your table instead mytable CTE
select 'A' letter union all
select 'B' letter union all
select 'C' letter
)

select t.letter, conv.conv
  from mytable t
       inner join (--conversion data
                   select 'A' letter, 'AA' conv union all
                   select 'A' letter, 'AB' conv union all
                   select 'B' letter, 'BB' conv union all
                   select 'C' letter, 'CC' conv
                  ) conv on t.letter=conv.letter

Result:

letter   conv
A        AA
A        AB
B        BB
C        CC