clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2249064 fiddles created (32139 in the last week).

select @@version;
(No column name)
Microsoft SQL Server 2019 (RTM-CU6) (KB4563110) - 15.0.4053.23 (X64) Jul 25 2020 11:26:55 Copyright (C) 2019 Microsoft Corporation Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)
 hidden batch(es)


CREATE TABLE test ( id INTEGER NOT NULL, name VARCHAR (25) NOT NULL, address_type VARCHAR (25) NOT NULL, address VARCHAR (25) NOT NULL, features VARCHAR (25) NOT NULL, CONSTRAINT at_h_w_ck CHECK (address_type IN ('Home', 'Work')) );
 hidden batch(es)


INSERT INTO test VALUES ( 1, 'Bob', 'Home', '123 Nope St', 'JP'), ( 2, 'John', 'Work', '555 Fake St', 'MNGF'), ( 2, 'John', 'Home', '654 Madeup Ln', 'IMP JP'), ( 3, 'Kim', 'Work', '92 Nadda Blvd', 'MP');
4 rows affected
 hidden batch(es)


-- SELECT * FROM test;
 hidden batch(es)


SELECT id, COUNT(id) FROM test GROUP BY id;
id (No column name)
1 1
2 2
3 1
 hidden batch(es)


SELECT t.*, tab1.cnt FROM test t JOIN ( SELECT id, COUNT(id) AS cnt FROM test GROUP BY id ) AS tab1 ON t.id = tab1.id WHERE t.address_type = 'Work' OR cnt = 1;
id name address_type address features cnt
1 Bob Home 123 Nope St JP 1
2 John Work 555 Fake St MNGF 2
3 Kim Work 92 Nadda Blvd MP 1
 hidden batch(es)


SELECT tab2.id, tab2.name, tab2.address_type, CONCAT(tab2.features, ' ', t1.features) AS ft, cnt FROM ( SELECT t.*, tab1.cnt FROM test t JOIN ( SELECT id, COUNT(id) AS cnt FROM test GROUP BY id ) AS tab1 ON t.id = tab1.id WHERE t.address_type = 'Work' OR cnt = 1 ) AS tab2 JOIN test t1 ON tab2.id = t1.id AND tab2.address_type != t1.address_type;
id name address_type ft cnt
2 John Work MNGF IMP JP 2
 hidden batch(es)


CREATE VIEW my_ft_view AS SELECT tab2.id, tab2.name, tab2.address_type, tab2.address, CONCAT(tab2.features, ' ', t1.features) AS features FROM ( SELECT t.*, tab1.cnt FROM test t JOIN ( SELECT id, COUNT(id) AS cnt FROM test GROUP BY id ) AS tab1 ON t.id = tab1.id WHERE t.address_type = 'Work' OR cnt = 1 ) AS tab2 JOIN test t1 ON tab2.id = t1.id AND tab2.address_type != t1.address_type;
 hidden batch(es)


SELECT * FROM my_ft_view;
id name address_type address features
2 John Work 555 Fake St MNGF IMP JP
 hidden batch(es)


SELECT * FROM my_ft_view UNION SELECT * FROM test WHERE id NOT IN (SELECT id FROM my_ft_view);
id name address_type address features
1 Bob Home 123 Nope St JP
2 John Work 555 Fake St MNGF IMP JP
3 Kim Work 92 Nadda Blvd MP
 hidden batch(es)