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. 2249300 fiddles created (32225 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 *, COUNT(*) OVER (PARTITION BY id) AS cnt FROM test;
id name address_type address features cnt
1 Bob Home 123 Nope St JP 1
2 John Work 555 Fake St MNGF 2
2 John Home 654 Madeup Ln IMP JP 2
3 Kim Work 92 Nadda Blvd MP 1
 hidden batch(es)


SELECT tb1.ID, tb1.Name, tb1.Address_Type, tb1.Address, CONCAT(tb1.Features, ISNULL(' home address: ' + tb2.Address, '')) as AltAddress FROM test AS tb1 LEFT JOIN ( SELECT id, Address FROM test AS SUB WHERE Address_Type = 'Home' ) AS tb2 ON tb1.id = tb2.id WHERE Address_Type = 'Work' ;
ID Name Address_Type Address AltAddress
2 John Work 555 Fake St MNGF home address: 654 Madeup Ln
3 Kim Work 92 Nadda Blvd MP
 hidden batch(es)