SQLite 3.27

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE files (
  `filename` VARCHAR(12),
  `id` INTEGER,
  `name` VARCHAR(19),
  `value` VARCHAR(10)
);

✓
INSERT INTO files
  (`filename`, `id`, `name`, `value`)
VALUES
  ('orig_db_1.db', '1', 'A_USERNAME_VAR', 'user'),
  ('orig_db_1.db', '1', 'A_PASSWORD_VAR', 'password'),
  ('orig_db_1.db', '1', 'SOME_OTHER_VAR', 'helloworld'),
  ('orig_db_1.db', '2', 'A_USERNAME_VAR', 'user'),
  ('orig_db_1.db', '3', 'A_PASS_VAR', 'password'),
  ('orig_db_2.db', '12', 'A_USER_VAR', 'user'),
  ('orig_db_2.db', '12', 'A_PASS_VAR', 'password'),
  ('orig_db_2.db', '12', 'SOMETHING_DIFFERENT', '12345'),
  ('orig_db_3.db', '42', 'IDK_WHAT_TO_TYPE_HERE', 'idksomething');
✓
SELECT
  filename,
  id,
  name,
  value
FROM (
    SELECT *,
      COUNT(CASE WHEN name LIKE '%USER%' THEN 1 END) OVER (PARTITION BY id) usr,
      COUNT(CASE WHEN name LIKE '%PASS%' THEN 1 END) OVER (PARTITION BY id) pass
    FROM files
) f
WHERE usr > 0
  AND pass > 0;


filename
id
name
value


orig_db_1.db
1
A_USERNAME_VAR
user

orig_db_1.db
1
A_PASSWORD_VAR
password

orig_db_1.db
1
SOME_OTHER_VAR
helloworld

orig_db_2.db
12
A_USER_VAR
user

orig_db_2.db
12
A_PASS_VAR
password

orig_db_2.db
12
SOMETHING_DIFFERENT
12345

filename	id	name	value
orig_db_1.db	1	A_USERNAME_VAR	user
orig_db_1.db	1	A_PASSWORD_VAR	password
orig_db_1.db	1	SOME_OTHER_VAR	helloworld
orig_db_2.db	12	A_USER_VAR	user
orig_db_2.db	12	A_PASS_VAR	password
orig_db_2.db	12	SOMETHING_DIFFERENT	12345