July 2, 2008 at 12:20 am
hi,
i have a table from which i am using 3 fields, i want to transpose these fields, sample data as below
[VAR] Time MilliSec [VALUE]
Stage_num[11] 6/16/2008 9:09:20 AM 375 11
Stage_Status[11] 6/16/2008 9:09:20 AM 375 5
Stage_Result[11] 6/16/2008 9:09:20 AM 375 8
Engine_poka_stage[11] 6/16/2008 9:09:20 AM 375 DUMBPE00152
Stage_num[16] 6/18/2008 8:11:32 AM 187 16
Stage_Status[16] 6/18/2008 8:11:32 AM 187 4
Stage_Result[16] 6/18/2008 8:11:32 AM 187 3
Engine_poka_stage[16] 6/18/2008 8:11:32 AM 187 DUMBPE00120
This is table i have, which contains many rows given as above .I want to use only Time, MilliSec, [VALUE].
I want to transpose this table.
the result should be like this.
SNumber Status Result EngineNo Time MilliSec
11 5 8 DUMBPE00152 6/16/2008 9:09:20 AM 375
16 4 3 DUMBPE00120 6/18/2008 8:11:32 AM 187
---- so on.
I have tried using Pivot as follows
use pokayoke2
SELECT 'TIMESTAMP_S' as time_s, 'TIMESTAMP_MS' as time_ms,
'[VALUE]' as StageNo, '[VALUE]' as Status, '[VALUE]' as Result, '[VALUE]' as EngineNo
from
(SELECT TIMESTAMP_S, TIMESTAMP_MS,[VALUE] FROM dbo.Alarm_DATA) AS SourceTable
PIVOT
(
max(TIMESTAMP_S)
FOR [VAR] IN ([0], [1], [2], [3])
) AS PivotTable
But problem here is the output. Out put is as follows :
time_s time_ms StageNo Status Result EngineNo
TIMESTAMP_S TIMESTAMP_MS [VALUE] [VALUE] [VALUE] [VALUE]
TIMESTAMP_S TIMESTAMP_MS [VALUE] [VALUE] [VALUE] [VALUE]
can anybody correct this code ?
July 2, 2008 at 1:48 am
First have a look at the part of your statement that says:
SELECT 'TIMESTAMP_S' as time_s, 'TIMESTAMP_MS' as time_ms,
'[VALUE]' as StageNo, '[VALUE]' as Status, '[VALUE]' as Result, '[VALUE]' as EngineNo
and remove the apostrophes from around the column names. '[VALUE]' means a constant string, [VALUE] on the other hand would refer to the column. Similarly 'TIMESTAMP_S', 'TIMESTAMP_MS', etc.
Regards,
Andras
July 2, 2008 at 3:32 am
hello,
I have made changes to the code, but still i am not getting desired result, as specified above.I don't know how pivot works.
use pokayoke_128stages
SELECT TIMESTAMP_MS as milliSec,
[VALUE] as StageNo, [VALUE] as Status, [VALUE] as Result, [VALUE] as EngineNo
from
(SELECT TIMESTAMP_S, TIMESTAMP_MS,[VALUE] FROM dbo.Alarm_DATA) AS SourceTable
PIVOT
(
max(TIMESTAMP_S)
FOR TIMESTAMP_S IN ([0])
) AS PivotTable
Plz can anybody correct this code ? , I want the result as specified at the starting.
July 2, 2008 at 2:03 pm
Try this:
declare @table table ([Var] varchar(50), [Time] datetime, [MilliSec] int, [Value] varchar(50))
insert @table
values ('Stage_num[11]', '6/16/2008 9:09:20 AM', 375, '11')
insert @table
values ('Stage_Status[11]', '6/16/2008 9:09:20 AM', 375, '5')
insert @table
values ('Stage_Result[11]', '6/16/2008 9:09:20 AM', 375, '8')
insert @table
values ('Engine_poka_stage[11]', '6/16/2008 9:09:20 AM', 375, 'DUMBPE00152')
insert @table
values ('Stage_num[16]', '6/18/2008 8:11:32 AM', 187, '16')
insert @table
values ('Stage_Status[16]', '6/18/2008 8:11:32 AM', 187, '4')
insert @table
values ('Stage_Result[16]', '6/18/2008 8:11:32 AM', 187, '3')
insert @table
values ('Engine_poka_stage[16]', '6/18/2008 8:11:32 AM', 187, 'DUMBPE00120')
select num.[Value] as 'SNumber'
, stat.[Value] as 'Status'
, res.[Value] as 'Result'
, stage.[Value] as 'EngineNo'
, num.Time
, num.MilliSec
from @table num
inner join @table stat
on stat.[Var] = 'Stage_Status[' + num.[Value] + ']'
inner join @table res
on res.[Var] = 'Stage_Result[' + num.[Value] + ']'
inner join @table stage
on stage.[Var] = 'Engine_poka_stage[' + num.[Value] + ']'
where num.[Var] like 'Stage_num%'
July 2, 2008 at 8:05 pm
Thanxs Jeff for the reply,
July 4, 2008 at 12:53 am
hi jeff,
The code is really working , but it is working fine for single data. How to use it for whole table ?
When i try to use it with whole table , it repeats the rows, from which some rows are correct , and some are wrong. Here is the changed query according to your solution.
use pokayoke2
select distinct num.ARV,num.[Value] as 'SNumber', stat.[Value] as 'Status', res.[Value] as 'Result', stage.[Value] as 'EngineNo', num.TIMESTAMP_S, num.TIMESTAMP_MS
from dbo.Alarm_DATA num inner join dbo.Alarm_DATA stat
on stat.[Var] = 'Stage_Status[' + substring(num.[VALUE],1,(PATINDEX('%.%', num.[VALUE])-1)) + ']'
inner join dbo.Alarm_DATA res
on res.[Var] = 'Stage_Result[' + substring(num.[VALUE],1,(PATINDEX('%.%', num.[VALUE])-1)) + ']'
inner join dbo.Alarm_DATA stage
on stage.[Var] = 'PLC/Global/Engine_poka_stage[' + substring(num.[VALUE],1,(PATINDEX('%.%', num.[VALUE])-1)) + ']'
where
num.[Var] LIKE 'Stage_num%'
July 4, 2008 at 8:30 am
Please post some data that is causing the problem. Use the format that I created in the previous responses.
September 24, 2009 at 10:17 pm
Hello,
I have a table ITEM_MST
ITEM_TYPE QTY Date
1 2 15 July 2009
1 4 16 July 2009
2 3 17 July 2009
2 1 18 July 2009
1 4 19 July 2009
1 6 20 July 2009
1 2 21 July 2009
I wan the output in this format
ITEM_TYPE QTY1 QTY2 QTY3
1 2 4 4
1 6 2
2 3 1
I dont want to use any aggregate functions as there is nothing to aggregate.
Please let me know the solution for the same ASAP.
Thanks and Regards
Rahul
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply