CODEWITHSUNDEEP
sqlsql-server
Abhishek Sirari
Abhishek Sirari

Reputation: 35

SQL - query to create custom table

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
  1. The ProjID fild in "Project Tags Table" is foreign key to ProjID field in "Projects table".
  2. 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

Upvotes: 0

Views: 370

Answers (3)

Sathish
Sathish

Reputation: 4487

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

Upvotes: 0

Negev Nomad
Negev Nomad

Reputation: 123

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

Upvotes: 1

shree.pat18
shree.pat18

Reputation: 21757

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

Upvotes: 2

Related Questions