SQLTeam.com | Weblogs | Forums

SQL Query

I am setting up a database view which will be called from a web application. Having trouble construct this query.

The database backend is on MySQL and site is being developed in PHP. I've tried a combination of joins but still don't seem to be getting the results I need. Frustrated.

Assume I have the following tables:

CREATE TABLE #tmp_employees
(
name VARCHAR(30),
age INT
)

insert into #tmp_employees
(name, age)
values
('BM', 45),
('JL', 44),
('DH', 46);

CREATE TABLE #tmp_sites
(
name VARCHAR(30),
social_type VARCHAR(30),
site VARCHAR(100)
)

insert into #tmp_sites
(name, social_type, site)
values
('BM', 'instagram', 'instangramlink/site1'),
('BM', 'instagram', 'instangramlink/site2'),
('BM', 'instagram', 'instangramlink/site3'),
('BM', 'twitter', 'twitterlink/site1'),
('JL', 'twitter', 'twitterlink/site1a');

CREATE TABLE #tmp_site_type
(site VARCHAR(30))

insert into #tmp_site_type
(site )
values
('instagram'),
('twitter'),
('snapchat');

I am trying to get a query result in this format.
The site_types are pivoted with a listing of the Name and social links for each site_type.

NAME; AGE; INSTAGRAM; TWITTER; SNAPCHAT
BM; 45;instangramlink/site1, instangramlink/site2, instangramlink/site3;twitterlink/site1;NULL
JL; 44;NULL;twitterlink/site1a;NULL
DH;46;NULL;NULL;NULL

Thank you in advance.

possibly (this is a Microsoft SQL Server forum)

create view vw_nmistri
as
select e.name, s.site, t.site as social_type
  from tmp_employees e
  left join tmp_sites s on e.name = s.name
  left join tmp_site_type t on t.site = s.social_type
go

I'm aware it's sql server. but the query is almost essentially the same. (i don't think there's a pivot function in mysql). The results of your query:

name; site; social_type
BM; instagramsite/site1; instagram
BM; instagramsite/site2; instagram
BM; instagramsite/site3; instagram
BM; twittersite/site1; twitter
JL; twittersite/site1a; twitter
DH;NULL;NULL

but in your case I would go with a dynamic pivot .

declare @col2 nvarchar(max), @query nvarchar(max)

 Select @col2 = stuff(( Select ',' + quotename(site)
                          From tmp_site_type
                         Group By site
                         Order By site
                For xml Path(''), Type).value('.', 'NVARCHAR(MAX)'), 1, 1, '');

-- Pivot table with one row and five columns  
select @query = 'SELECT name, age, ' + @col2 + '
FROM  
(
	SELECT name, age,  social_type
		FROM dbo.vw_nmistri
) AS SourceTable  
PIVOT  
(  
max(site)  
FOR social_type IN (' + @col2  + ')
) AS PivotTable';  


exec (@query)