Post reply

Population of table

  • July 16, 2003 at 2:57 am

    #82010

    I have tableA with the following structure:

    cid    RefID    Day    Hour

    1       100      1        5

    3       100      3        8

    4       100      4        12

    5       100      5        10

    6       100      6        5

    Day Column refers WeekDay, i.e. 1-Sunday, 2-Monday, 3-Tuesday and so on.

    I perform a select query on this table like this:

    SELECT Day, Hour FROM tableA WHERE RefID = 100

    I want to perform an insert operation on another table (tableB) whose structure is like this

    cid

    day1

    day2

    day3

    day4

    day5

    day6

    day7

    Total

    Can there be any mechanism where in I can insert the values from tableA into tableB in this criteria.

    If Day column in tableA is 1 then the corresponding Hour information goes into day1 of tableB and so on..

    So the tableB woule be:

    cid   day1   day2   day3   day4   day5   day6   day7   total

    410   5      NULL     8       12      10       5       NULL     40

    Currently I am performing a cursor operation inside a trigger which I feel is affecting the performance.

    Can anyone help me with a better solution.

    Thanks in advance

    Anand

  • July 16, 2003 at 3:05 am

    #465639

    Hi,

    have a look at pivot table in BOL. This shows how a case statement can be used to do roughly what you want to do.

    Paul

  • July 16, 2003 at 3:41 am

    #465642

    That was a nice suggestion paul. Thanks for that. But if the Hour Column in tableA is of char datatype, then how to handle it. Actually the hour is stored in the form of 'hh:mm' (08:30 for example).

    Again thanks for a speedy reply.

    Anand

  • July 16, 2003 at 3:55 am

    #465644

    hi,

    use the substring function:-

    SUBSTRING(hour, 1, 2) will return "08" etc ...

    Paul

  • July 16, 2003 at 6:18 am

    #465657

    Is this waht you are looking for

    SELECT	RefID, 

    	SUM(CASE WHEN [Day] = 1 THEN CAST(SUBSTRING([Hour],1,2) AS int) ELSE 0 END) AS 'day1', 

    	SUM(CASE WHEN [Day] = 2 THEN CAST(SUBSTRING([Hour],1,2) AS int) ELSE 0 END) AS 'day2', 

    	SUM(CASE WHEN [Day] = 3 THEN CAST(SUBSTRING([Hour],1,2) AS int) ELSE 0 END) AS 'day3', 

    	SUM(CASE WHEN [Day] = 4 THEN CAST(SUBSTRING([Hour],1,2) AS int) ELSE 0 END) AS 'day4', 

    	SUM(CASE WHEN [Day] = 5 THEN CAST(SUBSTRING([Hour],1,2) AS int) ELSE 0 END) AS 'day5', 

    	SUM(CASE WHEN [Day] = 6 THEN CAST(SUBSTRING([Hour],1,2) AS int) ELSE 0 END) AS 'day6', 

    	SUM(CASE WHEN [Day] = 7 THEN CAST(SUBSTRING([Hour],1,2) AS int) ELSE 0 END) AS 'day7', 

    	SUM(CAST(SUBSTRING([Hour],1,2) AS int)) AS 'total' 

    FROM	tableA 

    GROUP BY RefID

    Far away is close at hand in the images of elsewhere.
    Anon.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply