(SQL Server) Merge rows with character and binary

Question

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!

Possible duplicate of [GROUP BY to combine/concat a column](https://stackoverflow.com/questions/15154644/group-by-to-combine-concat-a-column) — Ousmane D., Nov 10 '17 at 09:27
https://stackoverflow.com/questions/273238/how-to-use-group-by-to-concatenate-strings-in-sql-server — Ousmane D., Nov 10 '17 at 09:27
https://stackoverflow.com/questions/18362653/concatenate-many-rows-into-a-single-text-string-with-grouping — Ousmane D., Nov 10 '17 at 09:27
https://stackoverflow.com/questions/2046037/can-i-comma-delimit-multiple-rows-into-one-column — Ousmane D., Nov 10 '17 at 09:28
https://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string — Ousmane D., Nov 10 '17 at 09:28

score 2 · Answer 1 · answered Nov 10 '17 at 14:14

2

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

answered Nov 10 '17 at 14:14

Nilesh Vishwakarma

107
10

Msg 402, Level 16, State 1, Line 2 The data types varchar and sql_variant are incompatible in the add operator. – Gug-Hong Jang Nov 13 '17 at 01:31
you need to cast, which data type you use saw me. – Nilesh Vishwakarma Nov 14 '17 at 05:27

score 0 · Answer 2 · edited Nov 16 '17 at 06:28

0

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;

edited Nov 16 '17 at 06:28

Yogesh Sharma

49,870
5
26
52

answered Nov 10 '17 at 09:24

Dheerendra

284
1
7

Shown 'The data types varchar and sql_variant are incompatible in the add operator.' – Gug-Hong Jang Nov 10 '17 at 09:48
Edited to cast the sql_variant as nvarchar – Dheerendra Nov 10 '17 at 09:58

deepak · Answer 3 · 2017-11-10T10:12:05.580

0

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.

edited Nov 10 '17 at 10:12

answered Nov 10 '17 at 09:28

deepak

13
4
10

GROUP_CONCAT not exist – Gug-Hong Jang Nov 13 '17 at 01:32

score 0 · Answer 4 · answered Nov 10 '17 at 10:21

0

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

answered Nov 10 '17 at 10:21

Yogesh Sharma

49,870
5
26
52

Msg 402, Level 16, State 1, Line 2 The data types varchar and sql_variant are incompatible in the add operator. – Gug-Hong Jang Nov 13 '17 at 01:31
@Gug-HongJang In order to avoid the error message you should cast the sql_variant variable to the desired data type. – Yogesh Sharma Nov 13 '17 at 04:28

(SQL Server) Merge rows with character and binary

4 Answers4