SQL Join / Pivot / Unpivot = Madness

Question

So, I searched and fiddled a lot and could not find a solution. Perhaps my situation is unique - or, most likely, I just don't know what I'm doing. I’m much closer than when I started, so I heaved. Anyway - here we go braintrust - any help is greatly appreciated.

I need to combine two lookup tables that look like this (there are not enough representative points to send the images, so here are my pretending 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 tried several things, including turning and pivot points, crosswise - but I can not get this result:

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

I will be able to work with this, I will not use for social_network_id or call_string after merging. Here is my request that does not quite do 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 say is a cross that actually does nothing. I kind of guessed about the syntax ... can you tell me?

This is what I get (somewhat typical of what I saw looking):

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

I want this to be possible? Any pointers on how to get there? Was my request raised?

Thank you Ladies and Men in advance.

I forgot to mention this before, but I am using SQL Server 2012 - SSMS.

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

Answer 1

Your current request is really close. If your tables above are the correct structure, then it seems you do not need to use CROSS APPLY , because you have nothing to disable.

If you remove CROSS APPLY, 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, 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

Answer 2

I think this is a very simple PIVOT case. You do not need to complicate it at all with CROSS APPLY / JOIN materials. The query is as simple as this would do:

 SELECT member_id, [facebook],[linkedin],[myspace],[twitter] FROM ( SELECT member_id,name,connection_id_string FROM social_network_member snm INNER JOIN social_network sn ON snm.social_network_id = sn.social_network_id ) as Sourcetable Pivot (MAX(connection_id_string) For [name] in([facebook],[linkedin],[myspace],[twitter])) AS PVT 

Hint: don't include unnecessary columns in your SourceTable for PIVOT

SQL Fiddle