How to write an SQL query that uses table data as columns

I have a table named tblTimelineData with the following columns

ProjectID
TaskName
WeeksOut
Active

I have another table named tblProjects with the following columns

ProjectID
ProjectName

I'm trying to create a query in which I can display the two columns from tblProjects (ProjectID and ProjectName)
AND
Display columns for each value of TaskName from tblTimeLineData where Active = True in which the data in each row would be the associated weeks out (or "N/A" or whatever if it's blank or NULL)

I'm sure I'm not totally explaining this in a way that makes sense so I've put some sample data and a correlating result set in a Google Spreadsheet here:
http://bit.ly/1Jtu9Nr
Mike MillerSoftware EngineerAsked:
Who is Participating?
 
ste5anSenior DeveloperCommented:
Then you need a PIVOT. In your case combined with dynamic SQL:

CREATE TABLE #t
    (
      ProjectID INT ,
      TaskName VARCHAR(30) ,
      WeeksOut INT ,
      Active BIT
    );

CREATE TABLE #p
    (
      ProjectID INT ,
      ProjectName VARCHAR(30)
    );

INSERT  INTO #t
        ( ProjectID, TaskName, WeeksOut, Active )
VALUES  ( 1, 'taskTEST5 a', 10, 1 ),
        ( 1, 'test task 5g', 667, 1 ),
        ( 1, 'testing7a', 32, 0 ),
        ( 3, 'Task Test 3a', 21, 1 ),
        ( 3, 'Task Test 3b', 33, 1 ),
        ( 3, 'TaskTest3c', 4, 1 ),
        ( 3, 'Task Test 3d', 5, 1 ),
        ( 3, 'Tasktest3e', 33, 0 ),
        ( 5, 'taskTEST5 a', 10, 1 ),
        ( 5, 'cqewiojcewiocjew', 0, 1 ),
        ( 5, 'tasktest5c', 4, 0 ),
        ( 5, 'TASK TEST 5f', 2, 1 ),
        ( 5, 'test task 5g', 667, 1 ),
        ( 7, 'testing7a', 32, 1 ),
        ( 7, 'TESTING 7b', 15, 0 ),
        ( 8, 'task8a', 21, 1 ),
        ( 8, 'TASK 8b', 90, 1 ),
        ( 8, 'Task 8c', 98, 1 ),
        ( 8, 'Task8d', 77, 0 ),
        ( 8, 'Task Test 3a', 21, 1 );

INSERT  INTO #p
        ( ProjectID, ProjectName )
VALUES  ( 1, 'MyProject1' ),
        ( 3, 'Testing Project' ),
        ( 5, 'Crazy 8s Project' ),
        ( 7, 'Star Wars' ),
        ( 8, 'Coffee Beans' );


DECLARE @Columns VARCHAR(MAX) = '';
DECLARE @Sql VARCHAR(MAX) = '
WITH    Data
          AS ( SELECT   P.ProjectID ,
                        P.ProjectName ,
                        T.TaskName ,
                        T.WeeksOut
               FROM     #p P
                        INNER JOIN #t T ON T.ProjectID = P.ProjectID
               WHERE    T.Active = 1
             )
    SELECT  *
    FROM    Data D PIVOT	( SUM(D.WeeksOut) FOR D.TaskName IN ( @Columns ) ) P;
';

SELECT  @Columns = @Columns + ', ' + QUOTENAME(T.TaskName)
FROM    #t T
WHERE   T.Active = 1
GROUP BY T.TaskName;

SET @Columns = STUFF(@Columns, 1, 2, '');
SET @Sql = REPLACE(@Sql, '@Columns', @Columns);

EXECUTE ( @SQL );


DROP TABLE #t;
DROP TABLE #p;

Open in new window


p.s. such pivots are often better handled in the front-end.
0
 
ste5anSenior DeveloperCommented:
Do you mean a JOIN?

E.g.

SELECT  P.ProjectName ,
        T.ProjectID ,
        T.TaskName ,
        T.WeeksOut ,
        T.Active
FROM    tblProjects P
        INNER JOIN tblTimelineData T ON P.ProjectID = T.ProjectID;

Open in new window

0
 
Mike MillerSoftware EngineerAuthor Commented:
I suppose I do in some fashion, but that query won't give me the data in the TaskName column as 'columns' in the result set as shown in my example.
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
Mike EghtebasDatabase and Application DeveloperCommented:
ste5an,

Below, I have the temp tables created if you want to take a look at it (or try it). He is using some of the data in  column [tblTimelineData].[TaskName] (#t) as meta data in the output as column names:

create table #t(ProjectID int	,TaskName varchar(30)	,WeeksOut int	,Active bit);
create table #p(ProjectID int	,ProjectName varchar(30));

Insert Into #t(ProjectID,	TaskName,	WeeksOut,	Active) values
 (1	,'taskTEST5 a',	10	,1)
, (1	,'test task 5g',	667	,1)
, (1	,'testing7a',	32,	0)
, (3	,'Task Test 3a',	21,	1)
, (3	,'Task Test 3b',	33	,1)
, (3	,'TaskTest3c',	4	,1)
, (3	,'Task Test 3d',	5,	1)
, (3	,'Tasktest3e',	33	,0)
, (5	,'taskTEST5 a',	10,	1)
, (5	,'cqewiojcewiocjew',	0	,1)
, (5	,'tasktest5c',	4,	0)
, (5	,'TASK TEST 5f',	2,	1)
, (5	,'test task 5g',	667,	1)
, (7	,'testing7a',	32,	1)
, (7	,'TESTING 7b',	15,	0)
, (8	,'task8a',	21	,1)
, (8	,'TASK 8b',	90,	1)
, (8	,'Task 8c',	98,	1)
, (8	,'Task8d',	77	,0)
, (8	,'Task Test 3a',	21,	1)

Insert Into #p(ProjectID,	ProjectName) values
  (1,	'MyProject1')
, (3,	'Testing Project')
, (5,	'Crazy 8s Project')
, (7,	'Star Wars')
, (8,	'Coffee Beans')

select * from #t;
select * from #p;

Open in new window


result expected(no solution yest):
ID	ProjectName	Task Test 3a	Task Test 3b	TaskTest3c	Task Test 3d	taskTEST5 a	cqewiojcewiocjew	TASK TEST 5f	test task 5g	testing7a	task8a	TASK 8b	Task 8c
1	MyProject1	N/A	N/A	N/A	N/A	10	N/A	N/A	667	N/A	N/A	N/A	N/A
3	Testing Project	21	33	4	5	N/A	N/A	N/A	N/A	N/A	N/A	N/A	N/A
5	Crazy 8s Proj	N/A	N/A	N/A	N/A	10	0	2	667	N/A	N/A	N/A	N/A
7	Star Wars	N/A	N/A	N/A	N/A	N/A	N/A	N/A	N/A	32	15	N/A	N/A
8	Coffee Beans	21	N/A	N/A	N/A	N/A	N/A	N/A	N/A	N/A	21	90	98

Open in new window


Here we have first two column (ProjectID and ProjectName ftom table tblProjects) following by transformed data from the tblTimelineData table where column [tblTimelineData].[TaskName] is as column names for the output.
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
ste5an,

I appreciate the solution you have posted. It is a great solution I would learn from.

Mike
0
 
Mike MillerSoftware EngineerAuthor Commented:
Got it thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.