Category: SQL Server

17 Sep 2017 21:08

Joined:	09/12/2005
Level:	Silver
Points:	96

Points:	5

Need the sql query in MS SQL Server

Hi ,
I have a table with two fields:FName and LName with following data.
FName LName
A B
A B
C D
E F
I need a query which will get me the following output
FName LName FullName
A B AB
A B AB
C D
E F
For the duplicate record, full name field should have concatenated values.
I applied the group by query to get only one of the two repeated records.But both repeated records should come in resultset.I tried the following:
select FName,LName,
case
when count(*) > 1 then FName+LName
else ' '
end as 'FullName'
from <tablename> group by FName,LName

#769371

19 Sep 2017 13:24

Joined:	20/07/2017
Level:	Bronze
Points:	19

Points:	0

your Requirement is not clear. let us know more in detail

#769421

07 Nov 2017 15:05

Joined:	06/04/2010
Level:	Gold
Points:	857

Points:	4

Hi,

Use the below SQL Script to get the concatenated values for the duplicate record



DECLARE @tablename TABLE (
  FName char(1),
  LName char(1)
)

INSERT INTO @tablename (FName, LName)
  VALUES ('A', 'B')
INSERT INTO @tablename (FName, LName)
  VALUES ('A', 'B')
INSERT INTO @tablename (FName, LName)
  VALUES ('C', 'D')
INSERT INTO @tablename (FName, LName)
  VALUES ('E', 'F')

SELECT
  t1.*,
  CASE
    WHEN t2.[count] > 1 THEN t1.FName + t1.LName
    ELSE NULL
  END FullName
FROM @tablename t1
JOIN (SELECT
  *,
  COUNT(*) AS [count]
FROM @tablename
GROUP BY FName,
         LName) t2
  ON t1.FName = t2.FName
  AND t1.LName = t2.LName

Please mark this as Answer, if this helps

Regards
Siva

Need the sql query in MS SQL Server

About dotnetspider.com

Quick Links

General

Services