How to Concatenate name using values from different column in SQL 2012

Question

I want to concatenate values together into a column based on ID.

Is there a way to concatenate two columns based on another column with SQL statements?

My table looks like this

customerID  Name    Values 
1           John    10apples
1           John    20oranges
1           John    30bananas
2           Steve   15apples
2           Steve   20oranges
2           Steve   35bananas

my expected query should give me some thing like this

customerID  Name    Values       FAKENAME
1           John    10apples    10apples_20oranges_30bananas
1           John    20oranges   
1           John    30bananas   
2           Steve   15apples    15apples_20oranges_35bananas
2           Steve   20oranges   
2           Steve   35bananas

basically for distinct Ids it should create a famke name by combining values

Possible duplicate of [SQL Server dynamic PIVOT query?](https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) — Joe Farrell, Oct 11 '17 at 18:44
Possible duplicate of [How to use GROUP BY to concatenate strings in SQL Server?](https://stackoverflow.com/questions/273238/how-to-use-group-by-to-concatenate-strings-in-sql-server) — Lord Peter, Oct 11 '17 at 18:53

Serkan Arslan · Answer 1 · 2017-10-12T06:27:45.693

You can use this.

DECLARE @Table TABLE (CustomerID INT, Name VARCHAR(20), [Values] VARCHAR(20))
INSERT INTO @Table
VALUES 
(1 ,'John','10apples'),
(1 ,'John','20oranges'), 
(1 ,'John','30bananas'), 
(2 ,'Steve','15apples'), 
(2 ,'Steve','20oranges'), 
(2 ,'steve','35bananas')

;WITH CTE
     AS (
     SELECT DISTINCT
            CustomerID,
            Name,
            [Values],
            STUFF(
                 (
                     SELECT '_'+[Values]
                     FROM @Table T2
                     WHERE T1.CustomerID = T2.CustomerID FOR XML PATH('')
                 ), 1, 1, '') FakeName
     FROM @Table T1)
     SELECT CustomerID,
            Name,
            [Values],
            CASE
                WHEN(ROW_NUMBER() OVER(PARTITION BY FakeName ORDER BY FakeName)) > 1
                THEN ''
                ELSE FakeName
            END FakeName
     FROM CTE;

Result:

customerID  Name    Values       FAKENAME
1           John    10apples    10apples_20oranges_30bananas
1           John    20oranges   
1           John    30bananas   
2           Steve   15apples    15apples_20oranges_35bananas
2           Steve   20oranges   
2           Steve   35bananas

C B · Answer 2 · 2017-10-11T19:07:10.940

Try this:

;WITH cte0 AS(     
SELECT 1 AS CustomerID, 'John' AS [Name],'10apples' AS [Values] UNION
SELECT 1  ,             'John'          ,'20oranges'            UNION
SELECT 1  ,             'John'          ,'30bananas'            UNION
SELECT 2  ,             'Steve'         ,'15strawberries'       UNION
SELECT 2  ,             'Steve'         ,'25blueberries'        UNION
SELECT 2  ,             'steve'         ,'35blackberries' )

,cte1 as(
SELECT customerid,[name], 
       STUFF((SELECT '_'+ [values] 
              FROM cte0 t1
              WHERE t1.customerid = t2.customerid
              FOR XML PATH ('')), 1, 1, '') AS FakeName 
FROM cte0 t2
GROUP BY customerid,[name]),

cte2 AS(
SELECT a.CustomerID,a.name,[values],fakename,Row_Number() OVER(PARTITION BY a.CustomerID ORDER BY a.CustomerID) AS RN
FROM cte0 a
    INNER JOIN cte1 b
        ON a.CustomerID = b.customerid)

SELECT CustomerID,[name],[values],CASE WHEN rn = 1 THEN FakeName ELSE '' END AS FakeName
FROM cte2

How to Concatenate name using values from different column in SQL 2012

2 Answers2