November 4, 2010 at 8:01 am
Hello
I have a table "Task", a table "Status" and a middle table "TaskStatus".
A task can have several status and a status can belong to several task.
I need to display 3 particular status for each task on the same row.
Based on the data provided in this post, the result should be this
IDTask Name Status A Status B Status D
------- ----- --------- -------- ---------
1 Task1 8 7 4
2 Task2 2 5 2
3 Task3 3 5 5
How can I do this?
CREATE TABLE [dbo].[Task](
[IDTask] [int] NOT NULL,
[Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_Task] PRIMARY KEY CLUSTERED
(
[IDTask] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Status](
[IDStatus] [int] NOT NULL,
[Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_Status] PRIMARY KEY CLUSTERED
(
[IDStatus] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[TaskStatus](
[IDTask] [int] NOT NULL,
[IDStatus] [int] NOT NULL,
[Status] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_TaskStatus] PRIMARY KEY CLUSTERED
(
[IDTask] ASC,
[IDStatus] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO Task([IDTask],[Name]) VALUES(1,'Task1')
INSERT INTO Task([IDTask],[Name]) VALUES(2,'Task2')
INSERT INTO Task([IDTask],[Name]) VALUES(3,'Task3')
INSERT INTO Status([IDStatus],[Name]) VALUES(1,'StatusA')
INSERT INTO Status([IDStatus],[Name]) VALUES(2,'StatusB')
INSERT INTO Status([IDStatus],[Name]) VALUES(3,'StatusC')
INSERT INTO Status([IDStatus],[Name]) VALUES(4,'StatusD')
INSERT INTO Status([IDStatus],[Name]) VALUES(5,'StatusE')
INSERT INTO TaskStatus([IDTask], [IDStatus],[Status]) VALUES(1,1,8)
INSERT INTO TaskStatus([IDTask], [IDStatus],[Status]) VALUES(1,2,7)
INSERT INTO TaskStatus([IDTask], [IDStatus],[Status]) VALUES(1,3,2)
INSERT INTO TaskStatus([IDTask], [IDStatus],[Status]) VALUES(1,4,4)
INSERT INTO TaskStatus([IDTask], [IDStatus],[Status]) VALUES(1,5,5)
INSERT INTO TaskStatus([IDTask], [IDStatus],[Status]) VALUES(2,1,2)
INSERT INTO TaskStatus([IDTask], [IDStatus],[Status]) VALUES(2,2,5)
INSERT INTO TaskStatus([IDTask], [IDStatus],[Status]) VALUES(2,3,4)
INSERT INTO TaskStatus([IDTask], [IDStatus],[Status]) VALUES(2,4,2)
INSERT INTO TaskStatus([IDTask], [IDStatus],[Status]) VALUES(2,5,5)
INSERT INTO TaskStatus([IDTask], [IDStatus],[Status]) VALUES(3,1,3)
INSERT INTO TaskStatus([IDTask], [IDStatus],[Status]) VALUES(3,2,5)
INSERT INTO TaskStatus([IDTask], [IDStatus],[Status]) VALUES(3,3,2)
INSERT INTO TaskStatus([IDTask], [IDStatus],[Status]) VALUES(3,4,5)
INSERT INTO TaskStatus([IDTask], [IDStatus],[Status]) VALUES(3,5,2)
November 4, 2010 at 8:18 am
You just need to join and perform an aggregate calculation between the tables:
SELECT t.IDTask,
t.Name,
[StatusA] = MAX(CASE WHEN s.Name = 'StatusA' THEN ts.Status ELSE '' END),
[StatusB] = MAX(CASE WHEN s.Name = 'StatusB' THEN ts.Status ELSE '' END),
[StatusD] = MAX(CASE WHEN s.Name = 'StatusD' THEN ts.Status ELSE '' END)
FROM dbo.Task t
JOIN dbo.TaskStatus ts
ON t.IDTask = ts.IDTask
JOIN dbo.[Status] s
ON ts.IDStatus = s.IDStatus
GROUP BY t.IDTask, t.Name;
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
November 4, 2010 at 8:49 am
Thank, it works
Can this be done with a PIVOT?
November 4, 2010 at 9:57 am
Got it
SELECT IDTask, name, [1] as [StatusA],[2] as [StatusB],[4] as [StatusD]
FROM
(
SELECT t.idTask, t.name, ts.idstatus, ts.status
FROM dbo.Task t
JOIN dbo.TaskStatus ts
ON t.IDTask = ts.IDTask
)
AS SourceQuery
PIVOT
(MAX(SourceQuery.status
FOR SourceQuery.idStatus IN
([1],[2],[4]))
AS AliasTable
November 4, 2010 at 10:33 am
dubem1-878067 (11/4/2010)
Thank, it worksCan this be done with a PIVOT?
You're welcome.
Sure, it can be done with a PIVOT. Actually, that is a pivot, it's just not using the PIVOT clause.
You might want to read the two articles in my signature on Cross-Tabs and Pivot Tables, Parts 1 & 2. You'll see that the way I did it is usually better performing.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply