How to combine certain data from different rows based on a certain column?

Question

I have a table that looks like this:

--------------------------------
  | name | email      | friend |
--------------------------------
1 | bob  | bobs email | kate   |
--------------------------------
2 | bob  | bobs email | joe    |
--------------------------------
3 | tim  | tims email | eddie  |

How can I create new columns (friend1, friend2, etc.) and move friends there, on the condition that name and email are the same (there might be two bobs, for instance, bob and bob with a different email).

My desired table looks like this:

-----------------------------------------------------
  | name | email      | friend1 | friend2 | friend3 |
-----------------------------------------------------
1 | bob  | bobs email | kate   | joe      |         |
-----------------------------------------------------
2 | tim  | tims email | eddie  |          |         |

Do you have to do this exclusively in SQL, or is there another program you can use for arranging some of the data? — StingyJack, Jan 30 '15 at 17:28

score 1 · Answer 1 · edited May 23 '17 at 12:06

This can't be achieved as the query you need has no static metadata (i.e. you don't know the columns) as it might change over time if a friend is added. But if you mean that you need only just three columns for friends, you can use the PIVOT command. You can use the below link as an example:

http://blogs.msdn.com/b/spike/archive/2009/03/03/pivot-tables-in-sql-server-a-simple-sample.aspx

Another solution (which is unfortunately not easily available in SQL Server) is to aggregate the friends, i.e. you will have only one column containing all friends regardless their count and separated with comma. This can be achieved using CLR function (Example: http://www.mssqltips.com/sqlservertip/2022/concat-aggregates-sql-server-clr-function/), CTE (Example: Optimal way to concatenate/aggregate strings) or FOR XML (Example: Does T-SQL have an aggregate function to concatenate strings?).

Hope this helps...

score 0 · Answer 2 · answered Feb 08 '15 at 18:27

Having these sample data:

DECLARE @DataSource TABLE
(
    [name] VARCHAR(12)
   ,[email] VARCHAR(24)
   ,[friend] VARCHAR(12)
)

INSERT INTO @DataSource ([name], [email], [friend])
VALUES ('bob', 'bobs email', 'kate')
      ,('bob', 'bobs email', 'joe')
      ,('tim', 'tim email', 'edie')

The following query:

SELECT DD.[name]
      ,DD.[email]
      ,Friends.[friend]
      ,ROW_NUMBER() OVER (PARTITION BY DD.[name], DD.[email] ORDER BY Friends.[friend]) AS [FriendNumber]
FROM 
(
    SELECT DISTINCT [name]
                   ,[email]
    FROM @DataSource
) DD -- Distinct Data
CROSS APPLY
(
    SELECT [friend]
    FROM @DataSource DS
    WHERE  DS.[name] = DD.[name]
        AND DS.[email] = DD.[email]
) Friends

will give you:

enter image description here

So, you can now build want you want using pivot, but note that you need to know the maximum number of friends which a person could have:

SELECT *
FROM
(   
    SELECT DD.[name]
          ,DD.[email]
          ,Friends.[friend]
          ,'friend' + CAST(ROW_NUMBER() OVER (PARTITION BY DD.[name], DD.[email] ORDER BY Friends.[friend]) AS VARCHAR(2)) AS [FriendNumber]
    FROM 
    (
        SELECT DISTINCT [name]
                       ,[email]
        FROM @DataSource
    ) DD -- Distinct Data
    CROSS APPLY
    (
        SELECT [friend]
        FROM @DataSource DS
        WHERE  DS.[name] = DD.[name]
            AND DS.[email] = DD.[email]
    ) Friends
) DS
PIVOT
(
    MAX([friend]) FOR [FriendNumber] IN ([friend1], [friend2], [friend3])
) PVT

enter image description here

How to combine certain data from different rows based on a certain column?

2 Answers2