July 1, 2008 at 11:29 am
hi,
i have a table from which i am using 2 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.
Can anybody give me the solution using pivot/ unpivot or any other means.
July 1, 2008 at 11:53 am
You can do this with the pivot/unpivot commands. I recommend against it. Use Excel or Report Services.
Set up Excel with a database connection and a view of the data you want to pivot. Create a pivot table in Excel. Bang! Takes a few minutes, and will pivot much better than anything in SQL Server.
Reporting Services can do the same thing. Slightly more complex to set up, but has the advantage of being useful for a whole lot more things as well.
To start off though, I'd use Excel.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 1, 2008 at 12:05 pm
Hi
You can use the PIVOT and UNPIVOT relational operators to change a table-valued expression into another table. PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output, and performs aggregations where they are required on any remaining column values that are wanted in the final output. UNPIVOT performs the opposite operation to PIVOT by rotating columns of a table-valued expression into column values.
Check out the below link
http://technet.microsoft.com/en-us/library/ms177410.aspx
Thanks -- Vj
July 1, 2008 at 12:26 pm
Thanxs for quick reply. 🙂 I am new to sql server . I have seen the pivot ,unpivot. But didn't understood the concept. bcaz in pivot it requires one aggregate function, but i didn't understand ,on which field i should aggregate function.
And using excel , i don't know excel concept.
can anybody give me sample code using above data and pivot.......
I am also trying with pivot, but haven't got success....
July 1, 2008 at 5:25 pm
Hi
Check out the below link
http://www.cpearson.com/excel/pivots.htm
Thanks -- Vj
July 1, 2008 at 7:51 pm
hi Vijaya
I have seen the link u posted. but i prefer pivot insted of excel.
i have applied the pivot concept to above data.Here is code
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]
The structure of above result is according to the result waht i want, but I am not getting the values in output, as i specified previously, I think used pivot incorrectly.
I u tell me what is wrong, or can u make changes to above pivot code. can u post correct code for above using pivot.?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply