SQL Join / Pivot / Unpivot = Madness

Question

So, I've done quite a bit of searching and fiddling and can't quite find a solution. Maybe my situation is unique - or more likely I just don't know what the heck I'm doing. I'm much closer than when I started, so that's uplifting. Anyway - here we go braintrust- any assistance is much appreciated.

I need to join 2 lookup tables that look like this (not enough rep points yet to post images so here's my pretend tables):

Social Network Member [table]
member_id     social_network_id     connection_id_string
16972         1                     www.linkedin.com/somename
16972         2                     www.twitter.com/somename
16972         3                     www.facebook.com/somename
180301        1                     www.linkedin.com/anothername

Social Network [table]
social_network_id     name     calling_string
1                     Linkedin www.linkedin.com
2                     Twitter  www.twitter.com
3                     Facebook www.facebook.com

This is what I want. I've tried a number of things including pivots and unpivots, cross apply - but I cant seem to get this result:

member_id   linkedin                     facebook                     twitter
16972       www.linkedin.com/somename    www.facebook.com/somename    www.twitter.com/somename

I'll be able to work with that, I have no use for the social_network_id or calling_string after the join. Here's my query that's not quite doing the job.

SELECT member_id, [facebook],[linkedin],[myspace],[twitter]
FROM (
SELECT member_id,name,social_network_id,calling_string,connection_id_string
FROM social_network_member INNER JOIN
social_network ON social_network_member.social_network_id = social_network.social_network_id
CROSS APPLY (VALUES ('NAME',name),
('CONNECTION STRING', connection_id_string),
('CALLING STRING',calling_string))
Unpivoted(club_id,member_id)) as Sourcetable
Pivot (MAX(connection_id_string) For name in([facebook],[linkedin],[myspace],[twitter])) AS PVT

The best I can tell is the cross apply really isn't doing anything. I kind of guessed on the syntax there..can you tell?

This is what I'm getting (somewhat typical from what I've been seeing in searches):

member_id     facebook                  linkedin                    myspace     twitter
16972         NULL                      www.linkedin.com/somename   NULL        NULL
16972         www.facebook.com/somename NULL                        NULL        NULL
...
...

Is what I want even possible? Any pointers on how to get there? Is my query all jacked up?

Thanks in advance Ladies and Gents.

I forgot to mention this earlier, but I'm using SQL server 2012 - SSMS.

RESOLVED I used the answer provided by Bluefeet below and it worked like a charm. Also thanks to Cha for taking the time to help.

Answer 1

Your current query is really close. If your tables above are the correct structure, then it doesn't seem that you need to use CROSS APPLY because you don't have anything that needs to be unpivoted.

If you remove the CROSS APPLY, then you can easily get the result using PIVOT:

SELECT member_id, [facebook],[linkedin],[myspace],[twitter]
FROM
(
  SELECT m.member_id,
    n.name,
    m.connection_id_string
  FROM social_network_member m
  INNER JOIN social_network n
    ON m.social_network_id = n.social_network_id
) d
pivot
(
  max(connection_id_string)
  for name in ([facebook],[linkedin],[myspace],[twitter])
) piv;

See SQL Fiddle with Demo.

If you have an unknown number of values, then you can use dynamic SQL:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(name) 
                    from social_network
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT member_id, ' + @cols + ' 
            from 
            (
              SELECT m.member_id,
                n.name,
                m.connection_id_string
              FROM social_network_member m
              INNER JOIN social_network n
                ON m.social_network_id = n.social_network_id
            ) x
            pivot 
            (
                max(connection_id_string)
                for name in (' + @cols + ')
            ) p '

execute sp_executesql @query;

See SQL Fiddle with Demo