Can i have a one to many from a select case statement?-CodePudding

I have two tables that I'm working on for a project at work. The first table (crm_users) has id, username and 16 fields that start with "state_license_" with a state abbreviation at the end.

The second table has a unique constraint on the username field, so 1 username could have up to 16 records, is how I would like the table to look.

The following query returns only the first state licensing and stops, but I would like to have all of the records populate in the second table (users_licenses), again with just one username for up to 16 records.

For example, Greg is a user in the crm_users table and is licensed in all 16 states. I would like to have the users_licenses have Greg having his username with 16 records displaying each of the state's names.

-- insert into users_licenses(crm_userid, username, licensed_in)
select distinct id, username, 
(case when state_license_AL = 'Yes' then 'Alabama' end) as licensed_in,
(case when state_license_CA = 'Yes' then 'California' end) as licensed_in,
(case when state_license_CO = 'Yes' then 'Colorado' end) as licensed_in,
(case when state_license_CT = 'Yes' then 'Connecticut' end) as licensed_in,
(case when state_license_FL = 'Yes' then 'Florida' end) as licensed_in,
(case when state_license_GA = 'Yes' then 'Georgia' end) as licensed_in,
(case when state_license_MD = 'Yes' then 'Maryland' end) as licensed_in,
(case when state_license_MI = 'Yes' then 'Michigan' end) as licensed_in,
(case when state_license_MN = 'Yes' then 'Minnesota' end) as licensed_in,
(case when state_license_NJ = 'Yes' then 'New Jersey' end) as licensed_in,
(case when state_license_OH = 'Yes' then 'Ohio' end) as licensed_in,
(case when state_license_OR = 'Yes' then 'Oregon' end) as licensed_in,
(case when state_license_PA = 'Yes' then 'Pennsylvania' end) as licensed_in,
(case when state_license_TN = 'Yes' then 'Tennessee' end) as licensed_in,
(case when state_license_VA = 'Yes' then 'Virginia' end) as licensed_in,
(case when state_license_WA = 'Yes' then 'Washington' end) as licensed_in
from crm_users
group by id, username

Any advice is always appreciated. Thank you.

CodePudding user response：

I have a hard time that query runs at all without throwing an error. At any rate, your schema is bad and it's leading you to frustrating logic. You may have no control over the schema, but if you do then change this table to be id, username, region, is_licensed. The current schema feels like an "Excel" way of thinking about data.

If you can't control the schema then a UNION query is the way to go:

SELECT id, username, 'Alabama' as region, state_license_AL AS is_licensed FROM crm_users
UNION ALL
SELECT id, username, 'California', state_license_CA FROM crm_users
UNION ALL
SELECT id, username, 'Colorado', state_licens_CO FROM crm_users
UNION ALL
SELECT id, username, 'Connecticut'... /*so on and so forth*/

While that doesn't get your exact output, it does get your data into a better state for querying. You can wrap that thing in a SELECT and filter:

SELECT *
FROM
    (
      SELECT id, username, 'Alabama' as region, state_license_AL as is_licensed FROM crm_users
      UNION ALL
      SELECT id, username, 'California', state_license_CA FROM crm_users
      UNION ALL
      SELECT id, username, 'Colorado', state_licens_CO FROM crm_users
      UNION ALL
      SELECT id, username, 'Connecticut'... /*so on and so forth*/
    )subquery
WHERE subquery.is_licensed = 'Yes'

Obviously you can just go straight to that in the UNION query without the subquery if you like:

SELECT id, username, 'Alabama' as region, state_license_AL FROM crm_users WHERE state_license_AL = 'Yes'
UNION ALL
SELECT id, username, 'California', state_license_CA FROM crm_users WHERE state_license_CA = 'Yes'
UNION ALL
SELECT id, username, 'Colorado', state_licens_CO FROM crm_users WHERE state_license_CO = 'Yes'
UNION ALL
SELECT id, username, 'Connecticut'... /*so on and so forth*/

But that feels clunky and with the first suggestion you can re-use that code over and over again when dealing with this data.

CodePudding user response：

You would need the function UNPIVOT, but MySQL does not have it. In its absence the best option is UNION ALL.

select id,  username, 'Alabama' as State, state_license_AL from crm_users union all
select id,  username, 'California' as State, state_license_CA from crm_users union all
select id,  username, 'Colorado' as State, state_license_CO from crm_users union all
select id,  username, 'Connecticut' as State, state_license_CT from crm_users union all
select id,  username, 'Florida' as State, state_license_FL from crm_users union all
select id,  username, 'Georgia' as State, state_license_GA from crm_users union all
select id,  username, 'Maryland' as State, state_license_MD from crm_users union all
select id,  username, 'Michigan' as State, state_license_MI from crm_users union all
select id,  username, 'Minnesota' as State, state_license_MN from crm_users union all
select id,  username, 'New Jersey' as State, state_license_NJ from crm_users union all
select id,  username, 'Ohio' as State, state_license_OH from crm_users union all
select id,  username, 'Oregon' as State, state_license_OR from crm_users union all
select id,  username, 'Pennsylvania' as State, state_license_PA from crm_users union all
select id,  username, 'Tennessee' as State, state_license_TN from crm_users union all
select id,  username, 'Virginia' as State, state_license_VA from crm_users union all
select id,  username, 'Washington' as State, state_license_WA from crm_users ;

id | username | State        | state_license_AL
-: | :------- | :----------- | :---------------
 1 | Greg     | Alabama      | Yes             
 1 | Greg     | California   | Yes             
 1 | Greg     | Colorado     | Yes             
 1 | Greg     | Connecticut  | Yes             
 1 | Greg     | Florida      | Yes             
 1 | Greg     | Georgia      | Yes             
 1 | Greg     | Maryland     | Yes             
 1 | Greg     | Michigan     | Yes             
 1 | Greg     | Minnesota    | Yes             
 1 | Greg     | New Jersey   | Yes             
 1 | Greg     | Ohio         | Yes             
 1 | Greg     | Oregon       | Yes             
 1 | Greg     | Pennsylvania | Yes             
 1 | Greg     | Tennessee    | Yes             
 1 | Greg     | Virginia     | Yes             
 1 | Greg     | Washington   | Yes

db<>fiddle here