SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 PIVOT/other trick
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ajmaly
Starting Member

India
11 Posts

Posted - 07/26/2010 :  06:44:20  Show Profile  Reply with Quote
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)

Singapore
17431 Posts

Posted - 07/26/2010 :  08:17:34  Show Profile  Reply with Quote
; 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



KH
Time is always against us

Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22713 Posts

Posted - 07/26/2010 :  08:44:08  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000