1

I have a table with partners details, like [ID], [PartnerName],[webaddress], [short description] etc.

In another table i have stored Social addresses the schema of this table looks like: enter image description here

My desired output is

ID, PartnerName, webaddress, [short description] from partners table and [Twitter], [Facebook], [LinkedIn] from social table

the values available in Name columns should come in the column heading of my output table for that particular partner

Can we achieve this via sql query?

AChauhan
  • 207
  • 3
  • 15
  • select case when name = 'twitter' then value end as twitter, case when name = 'linkedin' then value end as linkedin.... – artm Mar 09 '17 at 06:07
  • i was also thinking the same...but suppose for x partner there is a new social address say instagram, in that case i need to write a new case. I want to skip these case. what i want is - whatever is present for a particular Partner in social table, should come in heading - values pairs. – AChauhan Mar 09 '17 at 06:11
  • Please give expected result. – Jacky Montevirgen Mar 09 '17 at 06:21

2 Answers2

2

Please use the PIVOT functionality to do as you desire. You can see the answers on this question: Convert Rows to columns using 'Pivot' in SQL Server

You can also read the MSDN documentation on how to pivot a table: https://msdn.microsoft.com/en-us/library/ms177410.aspx

Community
  • 1
  • 1
DK5
  • 317
  • 3
  • 15
1

You can use pivot, but if you do not know the name of the social sites and it may vary as they are added, you would need to do something like this by using dynamic sql. Note that it assumes one distinct address per partner per social type in order to use pivot. 

create table #partners (
    ID int
    ,PartnerName nvarchar(100)
    ,WebAddress nvarchar(100)
    ,ShortDescription nvarchar(100)
)

create table #social  (
    ID int
    ,Name nvarchar(100)
    ,Value nvarchar(100)
    ,PartnerID int
    ,IsActive int 
)

insert into #partners 
values (1, 'Bob Jones', 'www.bobjones.com', 'bob desc')
,(2, 'Mike Smith', 'wwww.mikesmith.com', 'mike desc')

insert into #social
values (1, 'Facebook', 'www.facebook.com/bobjones', 1,1)
,(2, 'Twitter', 'wwww.twitter.com/bobjonestweets', 1,1)
,(3, 'Linkedin','wwww.linkedin.com/msmith', 2,1)
,(4, 'Facebook', 'www.facebook.com/smitty',2,1)

declare @columns nvarchar(max) = (
    select '[' + Name + '],' 
    from (select distinct Name from #social ) Name 
    for xml path ('')) 

declare @SQL nvarchar(max)
set @SQL = 'select *
            from (
                    select p.ID, PartnerName, WebAddress, ShortDescription, Name, Value
                    from #partners p
                    join #social s on p.ID = s.PartnerID
                    where s.IsActive = 1
            ) s
            pivot ( max(Value) for [Name] in (' + left(@columns, len(@columns)-1) +
            ')) as piv'

exec sp_executesql @SQL
MPR
  • 91
  • 6