Please help me for this qusetion

Robymon

I have two tables "CM_Master" and "CM_Details" and the sample records are below, i want to make a report like under the result. How can i join two tables and create table heading like the shift names in CM_Master table. CM_Master ShiftID ShiftName 1 Day 2 Night CM_Details CM_ID Site_ID Level_ID ShiftID Number 1 1 1 1 5 2 1 2 1 4 3 1 1 2 2 4 2 2 2 8 Result Site Level Day Night 1 1 5 2 1 2 4 0 2 2 0 8

__TR__

Try the below approach

CREATE TABLE #CM_Master
(
ShiftID INT, ShiftName VARCHAR(50)
)

INSERT INTO #CM_Master
SELECT 1, 'Day' UNION
SELECT 2, 'Night'

CREATE TABLE #CM_Detail
(
CM_ID INT, Site_ID INT, Level_ID INT, ShiftID INT, Number INT
)

INSERT INTO #CM_Detail
SELECT 1, 1, 1, 1, 5 UNION
SELECT 2, 1, 2, 1, 4 UNION
SELECT 3, 1, 1, 2, 2 UNION
SELECT 4, 2, 2, 2, 8

--SELECT * FROM #CM_Detail

SELECT DISTINCT CD.Site_ID, CD.Level_ID, ISNULL(A.Day,0) AS [Day], ISNULL(B.Night,0) AS Night
FROM #CM_Detail CD
LEFT JOIN
(
SELECT Site_ID, Level_ID,
SUM(Number) [Day]
FROM #CM_Detail
WHERE ShiftID = 1
GROUP BY Site_ID, Level_ID
) A ON CD.Site_ID = A.Site_ID AND CD.Level_ID = A.Level_ID
LEFT JOIN
(
SELECT Site_ID, Level_ID,
SUM(Number) AS [Night]
FROM #CM_Detail
WHERE ShiftID = 2
GROUP BY Site_ID, Level_ID
) B ON CD.Site_ID = B.Site_ID AND CD.Level_ID = B.Level_ID

and here is a generic approach where shift names are not hard coded.

DECLARE @PivotColumnHeader VARCHAR(MAX)
SELECT @PivotColumnHeader = COALESCE(@PivotColumnHeader + ', [' + ShiftName + ']', '[' + ShiftName + ']')
FROM #CM_Master

DECLARE @SQL VARCHAR(MAX)
SET @SQL = N'SELECT * FROM
(
SELECT Site_ID, Level_ID, ShiftName, ISNULL(Number,0) AS Number FROM #CM_Detail CD
INNER JOIN #CM_Master CM ON CD.ShiftID = CM.ShiftID
) P
PIVOT
(
SUM(Number) FOR ShiftName IN (' + @PivotColumnHeader + ')
) AS PivotTable'

EXECUTE (@SQL)

Robymon

Is it possible to make the sql dynamic, instead of hard code. eg: Day, Night some time the master table have more records.

Lost User

Robymon wrote:

Is it possible to

Yes, that's possible. The correct response to the answer would have been "thank you".

Bastard Programmer from Hell :suss: if you can't read my code, try converting it here[^]

__TR__

I have modified the solution to include the dynamic query.