SQL Pivot Issue - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

SQL Pivot Issue

Author

Topic

timelery
Starting Member

1 Post

Posted - 2011-03-29 : 17:02:06

I am struggling with a t-ql query. Here are the 3 tables I am refrencing,

I am struggling with a t-ql query. Here are the 3 tables I am refrencing,

I need a query that will give me the following results,

Where the field is Marked True if there is a corresponding record in the Tracker table that has the Complete column marked True.

Servername L&S Shutdown L&S Mon Off L&S Backup Exec
server1 TRUE NULL NULL
server2 TRUE NULL NULL
server3 TRUE NULL NULL
server4 NULL NULL NULL
server5 NULL NULL NULL

The following query gets me close but it does not include the Complete status (True or False) in the results.

SET NOCOUNT ON
DECLARE @CMD VARCHAR(1000) 
  
--Direct to output 
SELECT @CMD=ISNULL(@CMD+','+'['+Name+']','['+Name+']') 
FROM dbo.Task 
WHERE ProjectID=7  
GROUP BY Name
ORDER BY Name
SELECT @CMD=' 
  SELECT * 
  FROM (SELECT A.Name AS ServerName,T.Name AS TaskName FROM dbo.Asset A 
    JOIN (SELECT ProjectID,Name FROM dbo.Task WHERE ProjectID=7 GROUP BY ProjectID,Name) T ON A.ProjectID=T.ProjectID) T 
  PIVOT (COUNT(TaskName) FOR TaskName IN ('+@CMD+')) P 
  ORDER BY ServerName'
PRINT 'Here is the direct query result:'
PRINT ''
EXEC(@CMD)

Results of above query,
ServerName Resync Shutdown Signoff Startup TastApps
Server1 1 1 1 1 1
Server2 1 1 1 1 1
Server3 1 1 1 1 1
Server4 1 1 1 1 1
Server5 1 1 1 1 1

TIm

Llewellyn
Starting Member

13 Posts

Posted - 2011-03-30 : 08:22:10

This will get you closer but without data to work with its a bit difficult to determine if the results are coming out correct

declare @cmd varchar(max)
SELECT @CMD=ISNULL(@CMD+','+'['+Name+']','['+Name+']')
FROM dbo.Task
WHERE ProjectID=7
GROUP BY Name
ORDER BY Name
SELECT @CMD='
SELECT *
FROM (SELECT A.Name AS ServerName,CAse when isnull(complete,0) = 1 then ISNULL(T.Name,'''') else '''' end AS TaskName FROM dbo.Asset A
left JOIN (SELECT distinct ID,ProjectID,Name FROM dbo.Task WHERE ProjectID=7) T ON A.ProjectID=T.ProjectID
left JOIN Tracker K ON T.ID = K.TaskID AND A.ID = K.AssetID) T
PIVOT (count(TaskName) FOR TaskName IN ('+@CMD+')) P
ORDER BY ServerName'
PRINT 'Here is the direct query result:'
PRINT ''

EXEC(@CMD)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources