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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Acronis Data Cloud 7.8 Enhances Cyber Protection

A closer look at five essential enhancements that benefit end-users and help MSPs take their cloud data protection business further.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.