i have a job table that reference the dateid on dates table i want to created a view that populated the job dates according to the months. when use the query below i get - subquery returned more than 1 value.
SELECT dbo.Dates_1.DateID,
(SELECT DateValue
FROM dbo.Dates
WHERE (DATEPART(mm, DateValue) = 01)) AS Jan,
(SELECT DateValue
FROM dbo.Dates AS Dates_2
WHERE (DATEPART(mm, DateValue) = 02)) AS Feb,
(SELECT DateValue
FROM dbo.Dates AS Dates_3
WHERE (DATEPART(mm, DateValue) = 03)) AS Mar,
(SELECT DateValue
FROM dbo.Dates AS Dates_4
WHERE (DATEPART(mm, DateValue) = 04)) AS Apr,
(SELECT DateValue
FROM dbo.Dates AS Dates_5
WHERE (DATEPART(mm, DateValue) = 05)) AS May,
(SELECT DateValue
FROM dbo.Dates AS Dates_6
WHERE (DATEPART(mm, DateValue) = 06)) AS Jun,
(SELECT DateValue
FROM dbo.Dates AS Dates_7
WHERE (DATEPART(mm, DateValue) = 07)) AS Jul,
(SELECT DateValue
FROM dbo.Dates AS Dates_8
WHERE (DATEPART(mm, DateValue) = 08)) AS Aug,
(SELECT DateValue
FROM dbo.Dates AS Dates_9
WHERE (DATEPART(mm, DateValue) = 09)) AS Sep,
(SELECT DateValue
FROM dbo.Dates AS Dates_10
WHERE (DATEPART(mm, DateValue) = 10)) AS Oct,
(SELECT DateValue
FROM dbo.Dates AS Dates_11
WHERE (DATEPART(mm, DateValue) = 11)) AS Nov,
(SELECT DateValue
FROM dbo.Dates AS Dates_12
WHERE (DATEPART(mm, DateValue) = 12)) AS Dec
FROM dbo.JobData INNER JOIN
dbo.Dates AS Dates_1 ON dbo.JobData.DateID = Dates_1.DateID
pls help!!