Geek Answers Handbook

Concatenation - T-SQL

I have the following:

DECLARE @Table Table (ID int, Value1 varchar(50), Value2 varchar(50), Value3 varchar(50))
INSERT INTO @Table (ID, Value1, Value2, Value3)
SELECT 1, 'One', 'Uno', 'FIrst'
UNION ALL
SELECT 2, 'Two', 'Dos', 'Second'
UNION ALL
SELECT 3, 'One', 'Uno', 'Third'
UNION ALL
SELECT 4, 'Three', 'Tres', 'Fourth'

SELECT *, CASE
            WHEN COUNT(*) OVER (PARTITION BY  Value1, Value2) > 1 THEN 1
            ELSE 0
        END AS Duplicate  FROM @Table

Which gives me a duplicate. I want to get another column that would have a combined value for duplicate records as "First-Third" (both columns)

Expected Result

ID  Value1  Value2  Value3  Duplicate   Value3
1   One     Uno     FIrst   1           First - Third
3   One     Uno     Third   1           First - Third
4   Three   Tres    Fourth  0           NULL
2   Two     Dos     Second  0           NULL
+4
source share
3 answers

Unfortunately, SQL Server does not have a concatenation function, but you can use the xml trick:

select
   t.ID, t.Value1, t.Value2, t.Value3,
   case
       when count(*) over(partition by t.Value1, t.Value2) > 1 then 1
       else 0
   end as Duplicate,
   stuff(
       (
          select ' - ' + TT.Value3
          from @Table as TT
          where TT.Value1 = t.Value1 and TT.Value2 = t.Value2
          for xml path(''), type
       ).value('.', 'nvarchar(max)')
   , 1, 3, '') as Value3_Dupl
from @Table as t

sql

If you need to show duplicates just for Duplicate = 1, I think it's better to use cte (or a subquery), a little cleaner:

;with cte as (
    select *, count(*) over(partition by Value1, Value2) as cnt
    from @Table
)
select
   t.ID, t.Value1, t.Value2, t.Value3,
   case
       when cnt > 1 then 1
       else 0
   end as Duplicate,
   case
       when cnt > 1 then
         stuff(
             (
                select ' - ' + TT.Value3
                from @Table as TT
                where TT.Value1 = t.Value1 and TT.Value2 = t.Value2
                for xml path(''), type
             ).value('.', 'nvarchar(max)')
         , 1, 3, '')
    end as Value3_Dupl
from cte as t

sql

. type for xml path(''), value('.', 'nvarchar(max)'), , . solutin varchar, '<' '&' .

+4
SELECT *, 
    CASE
        WHEN COUNT(*) OVER (PARTITION BY  Value1, Value2) > 1 THEN 1
        ELSE 0
    END AS Duplicate, 

    CASE WHEN COUNT(*) OVER (PARTITION BY  Value1, Value2) > 1 
        THEN STUFF((SELECT ' - ' + Value3 
                    FROM @Table T 
                    WHERE T.Value1 = V.Value1 
                    AND T.Value2 = V.Value2 
                    FOR XML PATH('')), 
                   1, 3, '')
        ELSE NULL 
    END
FROM @Table V

SQL

+1

TRY THIS ONE:

DECLARE @Table Table (ID int, Value1 varchar(50), Value2 varchar(50), Value3 varchar(50))
INSERT INTO @Table (ID, Value1, Value2, Value3)
SELECT 1, 'One', 'Uno', 'FIrst'
UNION ALL
SELECT 2, 'Two', 'Dos', 'Second'
UNION ALL
SELECT 3, 'One', 'Uno', 'Third'
UNION ALL
SELECT 4, 'Three', 'Tres', 'Fourth'


;WITH CTE_MY
AS
(
SELECT *, 
CASE
    WHEN COUNT(*) OVER (PARTITION BY  Value1, Value2) > 1 THEN 1
    ELSE 0
END AS Duplicate
FROM @Table
)

DECLARE @Table Table (ID int, Value1 varchar(50), Value2 varchar(50), Value3 varchar(50))
INSERT INTO @Table (ID, Value1, Value2, Value3)
SELECT 1, 'One', 'Uno', 'FIrst'
UNION ALL
SELECT 2, 'Two', 'Dos', 'Second'
UNION ALL
SELECT 3, 'One', 'Uno', 'Third'
UNION ALL
SELECT 4, 'Three', 'Tres', 'Fourth'


;WITH CTE_MY
AS
(
SELECT *, 
CASE
    WHEN COUNT(*) OVER (PARTITION BY  Value1, Value2) > 1 THEN 1
    ELSE 0
END AS Duplicate
FROM @Table
)

SELECT *, 
CASE WHEN (SELECT CAST(M1.Value3 AS VARCHAR(MAX)) + ' - ' +CAST(M2.Value3 AS VARCHAR(MAX)) FROM CTE_MY M2 WHERE M1.Value1 = M2.Value1 AND M1.ID <> M2.ID) = 'Third - FIrst' THEN 'FIrst - Third' ELSE 
(SELECT CAST(M1.Value3 AS VARCHAR(MAX)) + ' - ' +CAST(M2.Value3 AS VARCHAR(MAX)) FROM CTE_MY M2 WHERE M1.Value1 = M2.Value1 AND M1.ID <> M2.ID) END VALUE3
FROM CTE_MY M1
+1
source

More articles:


All Articles