CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Gug-Hong Jang
Gug-Hong Jang

Reputation: 11

(SQL Server) Merge rows with character and binary

Can Somebody teach me how to merge the column 1 row into the same value and the column 2 row join together with binary and character?

For example : (TABLE A)

column1       column2

1              james
1              12345
2              jane
2              54321

to

1            james,12345
2            jane,54321

Your answer would be appreciated!

Upvotes: 1

Views: 344

Answers (4)

Dheerendra
Dheerendra

Reputation: 308

SELECT DISTINCT
       Rs.Col1,
       STUFF(
            (
                SELECT ','+CAST(Column2 AS NVARCHAR)
                FROM TableA
                WHERE Col1 = Rs.Col1 FOR XML PATH('')
            ), 1, 1, '') AS Field
FROM TableA Rs;

Upvotes: 0

Nilesh Vishwakarma
Nilesh Vishwakarma

Reputation: 129

SQL Sever in Use Stuff Query to merge.

SELECT column1, 
    column2 = STUFF((SELECT ',' + column2 FROM Testing1 as t1 where
     t1.column1=t2.column1  FOR XML PATH ('')), 1, 1, '') 
FROM Testing1 as t2 GROUP BY column1

Upvotes: 2

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

Here is simple approach is to use STUFF() function :

SELECT DISTINCT
       T.column1,
       [column2] = STUFF(
                        (
                            SELECT ', '+column2
                            FROM <table_name>
                            WHERE column1 = T.column1
                            ORDER BY ROW_NUMBER() OVER(PARTITION BY column1 ORDER BY column2 DESC) FOR XML PATH('')
                        ), 1, 1, '')
FROM <table_name> T;

Result :

column1 column2
1       james, 12345
2       jane, 54321

Upvotes: 0

deepak
deepak

Reputation: 13

Following query will answer your Question for MySQL.

 SELECT column_1,GROUP_CONCAT(column_2) FROM your_table GROUP BY column_1

column_1 have repeated value so you can perform group by on that column and the GROUP_CONCAT(column_2) will produce output as you desire by combining the value of column_2 using comma.

Upvotes: 0

Related Questions