SQL Server: Combine columns abort same values

Question

I am using ADO to connect SQL Server. I have a table and I want to group some cols to one col. I need the values in the new col is distinct.

This is my needing

Thank for all!

Answer 1

Import your excel file into SQL so you can run queries Then Transpose your table. Transpose means to reverse columns and rows like:

+------+---------+----------+
| Name | Email1  |  Email2  |
+------+---------+----------+
|  A   | A@a.com | A@aa.com |
+------+---------+----------+ 
|  B   | B@b.com | B@bb.com |
+------+---------+----------+ 

To something like this:

+---------+---------+----------+
|   Name  |    A    |     B    |
+---------+---------+----------+
|  Email1 | A@a.com | B@b.com  |
+---------+---------+----------+ 
|  Email2 | A@aa.com| B@bb.com |
+---------+---------+----------+ 

The way is describing here : Simple way to transpose columns and rows in Sql?

Then you can easily SELECT DISTINCT [A] FROM [MyTable] (for each column which is each person) one by one and insert it to a temp table with a single column.
Then:

SELECT  STUFF((
        SELECT ', ' + [temptablecolumn]
        FROM #temptable
        FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

This query it gives you this result: A@a.com, A@aa.com

Answer 2

You can use APPLY to convert your TMs into rows & concat them using FOR XML PATH() clause :

WITH t AS (
     SELECT DISTINCT name, tm
     FROM table t CROSS APPLY
          ( VALUES (TM1), (TM2), (TM3), (TM4), (TM5)
          ) tt (tm)
) 
SELECT nam, 
       (SELECT ''+t1.tm
        FROM t t1
        WHERE t1.nam = t.nam
        FOR XML PATH('')
       ) AS tn
FROM t;

Answer 3

One method uses a giant case expression:

select id,
       (tn1 +
        (case when tn2 not in (tn1) then tn2 else '' end) +
        (case when tn3 not in (tn1, tn2) then tn3 else '' end) +
        (case when tn4 not in (tn1, tn2, tn3) then tn4 else '' end) +
        (case when tn5 not in (tn1, tn2, tn3, tn4) then tn5 else '' end)
       ) as tn
from t;

I will add that having multiple columns with essentially the same data is usually a sign of a bad data model. Normally, you would want a table with one row per tn and id pair.