SQLTeam.com | Weblogs | Forums

Pivoting Data in SQL Database

sql2012

#1

I have a table like this

Recno Mapid Chnid Date reading
1 1 1 23/2/11 00:12:00 2369
2 1 2 23/2/11 00:12:00 2987
3 1 1 23/2/11 00:12:30 2569
4 2 1 24/2/11 00:12:00 2369
5 2 2 23/2/11 00:12:00 2987

Each map will have lots of channels and each channel will have lots of dates and readings

I need to get the data as follows, I need data for mapid 1

Date channel1 channel2
23/2/11 00:12:00 2369 2987
23/2/11 00:12:30 2569 NULL

Aplogise for layout, I'm new to this forum
Can anyone help? TIA


#2

If I am reading your data correctly, you have two rows where Reading = 2987, these being Recno=2 and Recno=5. For both those rows, ChnId is 2. Yet in your output, in the first row, you have the two readings of 2987, one categorized as channel1 and the other as channel2. I am not able to follow the logic you used to change the ChnId.

Also, in your output, you have only 3 readings. What is the rule you used to discard the other two readings? You indicated that you want data only for MapId=1, but the you picked up both readings of 2987, even though one of them is for MapId = 2.


#3

This will give you what you're looking for:

SELECT
    tbl.[date]
    ,CASE
        WHEN tbl.chnID = 1
            THEN tbl.reading
    END AS 'channel1'
    ,CASE
        WHEN tbl.chnID = 2
            THEN tbl.reading
    END AS 'channel2'
FROM 
    table tbl
WHERE 
    tbl.mapID = 1
ORDER BY 
    tbl.[date] ASC;

I hope this helps. For anything further I would need clarification regarding your goal with this query.


#4

@devAdvcte and @Jf_3d

It won't. In order to "pivot" data to live on the same row like the OP wants, there must be some form of aggregation. Your code will return output like the following...
date channel1 channel2
----------------------- ----------- -----------
2011-02-23 00:12:00.000 2369 NULL
2011-02-23 00:12:00.000 NULL 2987
2011-02-23 00:12:30.000 2569 NULL

(3 row(s) affected)

... instead of the two lines requested.

Date channel1 channel2
23/2/11 00:12:00 2987 2987
23/2/11 00:12:30 2569 NULL

I'll be back in a couple of minutes with test data and a coded example.


#5

@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.
********************************************************************/

#6

Sorry I made a mistake in one of the numbers - corrected now


#7

devAdvcte

When I try your query, I get multiple dates the same in the date column. If both channels have a reading taken at 12:45:00 I need it displayed 12:45:00 Chn1rrdg Chn2rdg
Thanks


#8

JeffModen Thanks, that's great - I can work with that. Would you be able to recommend a way without fixing the channelid's - there could be as many as 50 channels


#9

If what you meant by "fixing" is "hard coding", the answer is yes. Use a "Dynamic CrossTab". Please see the following article for how easy that can be.
http://www.sqlservercentral.com/articles/Crosstab/65048/


#10

JeffModen you have been very helpful - I am quite new to SQL and a struggling with the Dynamic Cross Tab - could you advise further. You are right I meant I don't want the number of Channelids in MappingID 1 hard coded or the channelids themselves hardcoded.


#11

The article I published for dynamic SQL is laid out in step-by-step cookbook fashion. Please give it a try. Trying things on your own is the only way you'll ever learn.