SQL - query to create custom table

Question

I have the following tables with data:

Projects table:

ProjID   Name
 1        A
 2        B

Project Tags table:

ProjID   TagID
 1        1
 2        2
 2        3

Tags Metadata table:

TagID   TagName
 1       Series:Go
 2       Series:Exploring
 3       Chapter:01

The ProjID fild in "Project Tags Table" is foreign key to ProjID field in "Projects table".
The TagID field in "Project Tags Table" is foreign key to TagID field in "Tags Metadata table".

Here, projects have tags which are of two types: Series and Chapter. I need and SQL query to return a custom table associating the Series and Chapter tag names mentioned in the Tags Metadata Table to the respective projects in the Projects Table.

The final table should look like this:

ProjID  Name  Series             Chapter
 1       A     Series:GO          null
 2       B     Series:Exploring   Chapter:01

The tag name starting with "Series:" is a Series tag and the tag name starting with "Chapter:" is aChapter tag — Abhishek Sirari
– Abhishek Sirari, Commented Jul 21, 2014 at 7:42

shree.pat18 · Accepted Answer · 2014-07-21 07:44:04Z

2

Try this:

select * from
(select 
 p.projid, 
 p.projname,
 m.tagname, 
 case when substring(m.tagname,1,1) = 'S' then 1 else 2 end tagtype --Type
 from projects p
 left join projecttags t on p.projid = t.projid
 left join tagsmetadata m on t.tagid = m.tagid
) as src

pivot
(max(tagname)
 for tagtype in ([1],[2])
) as pvt;

We first create a derived column to check tag type, and then use it along with PIVOT to get the desired results.

Demo

answered Jul 21, 2014 at 7:44

shree.pat18

21.8k3 gold badges45 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Negev Nomad · Accepted Answer · 2014-07-21 07:49:00Z

1

This will work for your sample data:

select p.ProjID, max(p.Name) as ProjName,
max(case when charindex('Series:', t.TagName) > 0 then t.TagName else null end) Series,
max(case when charindex('Chapter:', t.TagName) > 0 then t.TagName else null end) Chapter
from Projects p
join ProjectTags pt on (pt.ProjID = p.ProjID)
join Tags t on (t.TagID = pt.TagID)
group by p.ProjID

See the example: http://sqlfiddle.com/#!3/71f2d/9

answered Jul 21, 2014 at 7:49

Negev Nomad

1233 bronze badges

Comments

Sathish · Accepted Answer · 2014-07-21 08:03:48Z

0

Try like this

select p.ProjID,Name,case when UCase(SUBSTRING(TagName,1,7))='CHAPTER'
then null else TagName end "Series", 
case when UCase(SUBSTRING(TagName,1,7))='CHAPTER'
then TagName else null end "Chapter"
from TagsMetadata t 
join ProjectTags p on p.TagID=t.TagID
join Projects p1 on p1.ProjID=p.ProjId

edited Jul 21, 2014 at 8:03

answered Jul 21, 2014 at 7:45

Sathish

4,4854 gold badges36 silver badges60 bronze badges

Collectives™ on Stack Overflow

SQL - query to create custom table

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related