4

I am running SQL Server 2014

I have a table that looks like this:

 ID   | Name | AddressType | Address       | Features
 ========================================================
 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 

I am trying to write a SQL Server query that looks for duplicate IDs and always returns the Line containing a "Work" Address, but I want it to concatenate the Features from both lines so that I get something like this:

 ID   | Name | AddressType | 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 

The closest I've been able to figure out how to get looks something like this:

SELECT ID, Name, MAX(AddressType), MAX(Address), CONCAT(Features) FROM 
  (SELECT * FROM myTable ORDER BY ID, AddressType DESC)
GROUP BY ID, NAME

... but MAX(AddressType) and MAX(Address) are being pulled separately, so sometimes I get the Work Address and sometimes I get the Home Address. What I really need is the MAX(AddressType) and whatever Address is on the same line as that result.

The other thing I've tried is:

SELECT ID, Name, AddressType, Address, CONCAT(Features), COUNT(ID) OVER(PARTITION BY ID) AS IDcount
FROM myTable 
GROUP BY ID, NAME
WHERE AddressType = 'Work' OR IDcount = 1

... this always gives me all the right Addresses, but filters out the Features for the "Home" AddressType so that I can not Concatenate them in.

1
  • 1
    Maybe you should give some thought to a schema redesign - or to using VIEWs. You should perhaps (strongly recommended) an address table - that would greatly simplify your SQL!
    – Vérace
    Commented Sep 14, 2021 at 13:09

5 Answers 5

2

I tackled your problem in the following manner - I used neither window functions nor CTE-s, just in case you're running an antique version of SQL Server - in any case, I found it an interesting intellectual exercise (+1 for a thought provoking question).

Two points:

  • in future, could you please provide a working fiddle (at dbfiddle.uk or elsewhere) a) so that there's a single source of truth and b) so there's no duplication of effort on the part of those trying to answer your question - help us to help you!

  • you should perhaps think about changing your schema (if possible obviously, I realise that some people have no choice in the matter - PHB-s who don't listen or legacy issues) - but having an address table would greatly simplify your SQL (and your life...).

So, I created a test table as follows (all SQL below is available on the fiddle here):

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'))
);

and populated it using your data:

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');

I then "simulated" the ROW_COUNT() function by doing the following:

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;

Result:

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

I then extracted John's record (the only one with 2 addresses) like this:

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;

Result:

id  name  address_type  ft            cnt
 2  John  Work          MNGF IMP JP     2

So, using this result, I created a VIEW using the same SQL:

CREATE VIEW my_ft_view AS
SELECT 
  tab2.id, tab2.name, tab2.address_type, tab2.address,
  CONCAT(tab2.features, ' ', t1.features) AS features
...
... snipped for brevity
...

and then ran this query:

SELECT * FROM my_ft_view
UNION 
SELECT * FROM test WHERE id NOT IN (SELECT id FROM my_ft_view);

Result:

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

which is the desired result!

0
7

If you're using SQL Server 2017 or higher, you can use the STRING_AGG function to concatenate values within a group.

You can then use a CTE to fetch the Features list separately to simplify the query then join that back to your data to get the desired row plus the complete Features list for each ID.

Script:

;WITH AddressFeatures AS 
(
  SELECT ID, STRING_AGG(Features, ' ') AS Features
  FROM Addresses
  GROUP BY ID
)

SELECT s1.ID, s1.Name, s1.AddressType, s1.Address, af.Features
FROM
(
  SELECT ID, Name, AddressType, Address
    , ROW_NUMBER() 
      OVER (PARTITION BY ID 
            ORDER BY 
            CASE WHEN AddressType = 'Work' THEN 1 ELSE 2 END) 
      AS AddressTypeOrder
  FROM Addresses
) s1
INNER JOIN AddressFeatures af ON af.ID = s1.ID
WHERE AddressTypeOrder = 1

Output:

ID Name AddressType 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

You can see a working example in this db<>fiddle.

The advantage of this method is that you can work with multiple address type values and order them as per your required logic by simply adding WHEN clauses to the CASE statement in the ROW_NUMBER() function in the inner SELECT statement.

UPDATE: For versions prior to SQL 2017, you can use a combination of STUFF and FOR XML to replicate the behaviour of STRING_AGG:

;WITH AddressFeatures AS 
(
  SELECT ID, 
     (SELECT STUFF((
       SELECT ' ' + a2.[Features]
       FROM Addresses a2
       WHERE a1.ID = a2.ID
       FOR XML PATH ('')), 1, 1, '')) AS Features
  FROM Addresses a1
  GROUP BY ID
)

SELECT s1.ID, s1.Name, s1.AddressType, s1.Address, af.Features
FROM
(
  SELECT ID, Name, AddressType, Address
    , ROW_NUMBER() OVER 
      (PARTITION BY ID 
       ORDER BY CASE WHEN AddressType = 'Work' THEN 1 ELSE 2 END) 
      AS AddressTypeOrder
  FROM Addresses
) s1
INNER JOIN AddressFeatures af ON af.ID = s1.ID
WHERE AddressTypeOrder = 1

See updated db<>fiddle with this solution. Functionally this behaves the same, though performance may be impacted.

5
  • 1
    @AaronBertrand Thanks for the heads up, updated the answer
    – HandyD
    Commented Sep 14, 2021 at 2:14
  • Very elegant use of a CASE within an ORDER BY all wrapped up in a window function - +1!
    – Vérace
    Commented Sep 14, 2021 at 12:59
  • Very nice solution... unfortunately I've got SQL Server-2014 running. Sorry I should have put my SQL version in my question. Still, I think this is helpful information since I know I've run into this problem before and will probably see it again.
    – Nosajimiki
    Commented Sep 14, 2021 at 14:52
  • @Nosajimiki I've updated the answer with a solution for prior versions. It basically replicates the behaviour of STRING_AGG using language features available in SQL 2014
    – HandyD
    Commented Sep 14, 2021 at 22:07
  • +1 This should be the accepted answer for recent versions of sql server. Commented Nov 17, 2023 at 17:45
2
;WITH AddressLine AS (
    SELECT id, MAX(address_type) as AddressType
    FROM test sub1
    GROUP BY id
)
, WorkFeatures AS (
    SELECT sub2.id, ' home address: ' + sub2.Address as Address
    FROM test sub2
    JOIN AddressLine a on sub2.id = a.id 
    WHERE a.AddressType = 'Work' and sub2.address_type = 'Home'
)
SELECT tb1.ID, tb1.Name, tb1.Address_Type, tb1.Address,
       CONCAT(tb1.Features,ISNULL(tb2.Address,'')) as features 
FROM test AS tb1
JOIN AddressLine a2 on tb1.id = a2.id and tb1.address_type = a2.AddressType
LEFT JOIN WorkFeatures as tb2
  ON tb1.id = tb2.id   

If you only have two address types, this should work, otherwise, you will need to do some extra work in your derived table (name for subquery that's in the FROM clause).

You want an isnull in your concat (assuming there is other info in the Features column too). if there is no home address, the value will be NULL from tb2. Null + Value = Null. It's kind of like multiplying by zero.

2
  • Your solution doesn't work! I managed to solve it using "classic" SQL if you're interested?
    – Vérace
    Commented Sep 14, 2021 at 14:04
  • didn't notice he wanted to include home address too.
    – user238855
    Commented Sep 14, 2021 at 16:45
1

Another approach:

SELECT t1.ID, t1.Name, t1.AddressType, t1.Address, 
       CONCAT(t1.Features, ' ', t2.Features) as Features
FROM myTable AS t1
LEFT JOIN myTable AS t2
ON t1.ID = t2.ID 
WHERE t1.AddressType = 'Work' AND t2.AddressType = 'Home'
UNION
SELECT t3.ID, t3.Name, t3.AddressType, t3.Address, t3.Features
FROM myTable AS t3
WHERE (SELECT COUNT(*) FROM myTable WHERE ID = t3.ID) = 1
0

FWIW, another approach:

CREATE TABLE _testt
(
  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'))
);

INSERT INTO _testt 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');

go

CREATE function  [dbo].[_concatTesttFeatures]  
(
    @testID int
)
returns varchar(2000)
as
begin

declare @out varchar(2000)
declare @features varchar(25)
declare @count int

select @count = count(*) from _testt where id = @testID

if @count > 1
begin

declare curse cursor for 

select distinct  features from _testt where id =  @testID

set @out = ''

open curse
fetch next from curse into @features

while @@FETCH_STATUS  = 0
begin

if @out <> ''
begin
    set @out = @out + ',' + @features
end
else 
begin 
    set @out = @features
end 

fetch next from curse into @features
end

close curse

deallocate curse
end
else

begin
select @out = features from _testt where id = @testID 
end

return @out
end

GO

select *
from 
   (select a.id, [name], address_type, address, [dbo].[_concatTesttFeatures](a.id) features from _testt a left join  (select id from _testt where address_type = 'work') b on a.id = b.id   where b.id is null group by a.id, [name], address_type, address) a 
union  
    select id, [name], address_type, address, [dbo].[_concatTesttFeatures](id) features  from _testt where address_type = 'work' group by id, [name], address_type, address

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.