SELECT Dynamic columns with values from other table

Question

I need some help with a query. I want to select extra columns to a database query that are stored in another table. I will try to explain what I want to do. I have two tables a Task table and a TaskField table. When I query the task table then sometimes I need some extra fields selected from the TaskField table.

+-------------------+ +---------------------------+
|       Tasks       | |        TaskFields         |
+-------------------+ +---------------------------+
| TaskId PK int     | | TaskFieldId PK int        |
| TaskName string   | | TaskId FK -> Tasks.TaskId |
| TaskAuthor string | | TaskFieldName string      |
|                   | | TaskFieldValue string     |
+-------------------+ +---------------------------+

The rows in those tables will look like this. The Tasks table looks as follows

+--------+--------------+------------+
| TaskId |   TaskName   | TaskAuthor |
+--------+--------------+------------+
|      1 | Testing Task | Me         |
+--------+--------------+------------+

The taskfields table will contain this rows.

+-------------+--------+---------------+----------------+
| TaskFieldId | TaskId | TaskFieldName | TaskFieldValue |
+-------------+--------+---------------+----------------+
|           1 |      1 | Created_at    | 2017-31-05     |
|           2 |      1 | TaskLocation  | Berlin         |
+-------------+--------+---------------+----------------+

Now I want to dynamically select the data from taskfields into a row from table so I will get the following output.

+--------+--------------+------------+------------+--------------+
| TaskId |   TaskName   | TaskAuthor | Created_at | TaskLocation |
+--------+--------------+------------+------------+--------------+
|      1 | Testing Task | Me         | 2017-31-05 | Berlin       |
+--------+--------------+------------+------------+--------------+

I have no idea where to begin with this query and I don't even know if it's possible in mysql. I tried to start of with the following query.

SELECT `Tasks`.`TaskId`, `Tasks`.`TaskName`, `Tasks`.`TaskAuthor` FROM `Tasks`
JOIN `TasksFields` ON `Tasks`.`TaskId` = `TasksFields`.`TaskId`

This will return all rows joined but this is not the result I hoped for I need to dynamically append the colums to the result, but I have no idea how to get it done.

Answer 1

what is the logic behind above database design ?

Is TaskfieldName dynamic for particular taskid ?

In Sql Server,

    create table #t (TaskId int,TaskName varchar(50)
 ,TaskAuthor varchar(50) )
insert into #t VALUES(1,'Testing Task','Me')


create table #t1 (TaskFieldId int,TaskId int
 ,TaskFieldName varchar(50), TaskFieldValue varchar(50))
 insert into #t1 VALUES
(1 ,1,'Created_at'   ,'2017-31-05')
,(2 ,1,'TaskLocation' ,'Berlin') 
,(3 ,2,'TaskLocation' ,'Berlin') 
declare @TaskID varchar(10) =1
declare @Taskfield varchar(200)
declare @Sql varchar(max)=''
select top 1 
@Taskfield=stuff((select ','+'['+TaskFieldName +']'
from #t1 t11 where taskid=t1.taskid for xml path('') ),1,1,'')
from #t1 t1
where t1.TaskId=@TaskID
--select @Taskfield
set @Sql=
'SELECT *
FROM (
    SELECT t.*
        ,t1.TaskFieldName
        ,t1.TaskFieldValue
    FROM #t t
    INNER JOIN #t1 t1 ON t.TaskId = t1.TaskId
    where t.taskid='+@TaskID+'
    ) AS src
pivot(max(TaskFieldValue) FOR TaskFieldName IN (
            '+@Taskfield+'
            )) pv;'

--print @Sql
EXEC (@Sql)
drop table #t
drop table #t1

Answer 2

Your db is not normalized (2NF) the fields TaskFieldName | TaskFieldValue don't depend on the taskFiledId, they depend on the taskId, so Create At and taskLocation should be on the Task table. And when you need the report, just select it all from the same table.