July 16, 2003 at 2:57 am
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
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
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
hi,
use the substring function:-
SUBSTRING(hour, 1, 2) will return "08" etc ...
Paul
July 16, 2003 at 6:18 am
Is this waht you are looking for
SELECTRefID,
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'
FROMtableA
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