PIVOT/other trick - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

PIVOT/other trick

Author

Topic

ajmaly
Starting Member

11 Posts

Posted - 2010-07-26 : 06:44:20

I have a below output from a query,

SELECT T.AS_Transaction_Key, MAN.AS_Metadata_Attribute_Name, TV.AS_Trans_Values_Value
FROM AS_Metadata_Attributes MAN
INNER JOIN AS_Transaction_values TV ON TV.AS_Trans_Values_AS_MetadataAttributes_Key = MAN.AS_Metadata_Attributes_Key
INNER JOIN AS_TRANSACTION T ON T.AS_Transaction_Key = TV.AS_Trans_Values_AS_Transaction_Key

AS_Transaction_Key AS_Metadata_Attribute_Name AS_Trans_Values_Value

1 Transaction WF_TS
1 Submitted 2/6/2010
1 By pdi/vmuser3
1 RefNo 34
1 TotalHours 15
2 Transaction WF_TS
2 Submitted 2/6/2010
2 By pdi/vmuser3
2 RefNo 34
2 TotalHours 20

Now, I need output like below,

Txn_Key Transaction Submitted By RefNo TotalHours
1 WF_TS 2/6/2010 pdi/vmuser3 34 15
2 WF_TS 2/6/2010 pdi/vmuser3 34 20

How can I achieve this by using PIVOT/other trick? Any help is appreciated.

ajmal

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-07-26 : 08:17:34

[code]; with result
as
(
SELECT T.AS_Transaction_Key, MAN.AS_Metadata_Attribute_Name, TV.AS_Trans_Values_Value
FROM AS_Metadata_Attributes MAN
INNER JOIN AS_Transaction_values TV ON TV.AS_Trans_Values_AS_MetadataAttributes_Key = MAN.AS_Metadata_Attributes_Key
INNER JOIN AS_TRANSACTION T ON T.AS_Transaction_Key = TV.AS_Trans_Values_AS_Transaction_Key
)
select *
from result r
pivot
(
max(AS_Trans_Values_Value)
for AS_Metadata_Attribute_Name in ([Transaction], [Submitted], [By], [RefNo], [TotalHours])
) p[/code]

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-07-26 : 08:44:08

For unknown number of values, use http://beyondrelational.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources