@devAdvcte and @Jf_3d ,
First, when posting on a forum, give yourself the best chance possible to get a tested, coded answer and avoid a ton of "clarification" questions. Post the code to create a test table along with the needed goodies and populate it with readily consumable data. Here's one way...
--===== Include any settings that support the data being entered
-- and settings that aren't universal.
SET DATEFORMAT DMY
;
--===== If it exists, drop the test table to make reruns easier.
IF OBJECT_ID('tempdb..#TestTable') IS NOT NULL
DROP TABLE #TestTable
;
--===== Create the test table being sure to identify things like
-- the Primary Key and any Alternate Keys, etc. This provides
-- those trying to help you a huge amount of information
-- without you having to say a word.
CREATE TABLE #TestTable
(
RecNo INT NOT NULL
,MapID INT NOT NULL
,ChnID SMALLINT NOT NULL
,[Date] DATETIME NOT NULL
,Reading INT NOT NULL
,PRIMARY KEY NONCLUSTERED (RecNo) --You might have this as CLUSTERED, but shouldn't be
,UNIQUE CLUSTERED ([Date],MapID,ChnID) --Sorted by DATE so almost no page splits.
)
;
--===== Populate the test table with sample data.
INSERT INTO #TestTable
(Recno,Mapid,Chnid,Date,reading)
SELECT 1,1,1,'23/2/11 00:12:00',2369 UNION ALL
SELECT 2,1,2,'23/2/11 00:12:00',2987 UNION ALL
SELECT 3,1,1,'23/2/11 00:12:30',2569 UNION ALL
SELECT 4,2,1,'24/2/11 00:12:00',2369 UNION ALL
SELECT 5,2,2,'23/2/11 00:12:00',2987
;
Then, we can play to see which solution might be the best for you.
--===== Create the desired output using character-
-- based aggregated in a high performance CROSSTAB.
-- This does the requested pivot for MapID 1.
-- It comes with an unwanted message.
SELECT [Date]
,Channel1 = MAX(CASE WHEN ChnID = 1 THEN Reading END)
,Channel2 = MAX(CASE WHEN ChnID = 2 THEN Reading END)
FROM #TestTable
WHERE MapID = 1
GROUP BY [DATE]
ORDER BY [Date]
;
--===== This does the same thing but without the unwanted message.
SET ANSI_WARNINGS OFF
;
SELECT [Date]
,Channel1 = MAX(CASE WHEN ChnID = 1 THEN Reading END)
,Channel2 = MAX(CASE WHEN ChnID = 2 THEN Reading END)
FROM #TestTable
WHERE MapID = 1
GROUP BY MapID, [DATE]
ORDER BY [Date]
;
--===== This does the same thing but for every MapID in the table.
SET ANSI_WARNINGS OFF
;
SELECT [Date]
,MapID
,Channel1 = MAX(CASE WHEN ChnID = 1 THEN Reading END)
,Channel2 = MAX(CASE WHEN ChnID = 2 THEN Reading END)
FROM #TestTable
GROUP BY [DATE],MapID
ORDER BY [DATE],MapID
;
/********************************************************************
Notice in all 3 cases that no SORT occurred in the Execution Plan
because of the index created by the Unique Clustered constraint.
********************************************************************/