asked
how do i get the below output in T-SQL 2008 R2
Hi All , Happy new year to all , i got one requirement, for this i am sending my tables structure and also mentioned expected output . kindly have a look on the below tables structure Table1 :TASKACTIVE taskid activitycode_Type_id Activity_code_id Proj_id 571647 571647 2760 2761 Table2 : ACTTYPE activitycode_Type_id activitycode_Type Proj_id 2760 CCPS_DIV 11346 2761 CCPS_PP 11346 Table3 : ACTVCODE Activity_code_id activitycode_Type_id actv_code_name Short_name 8855 2760 Miletones CCPS_T 8856 2761 2*70MW power plant CPP From the above tables i need to get the output like below Taskid Short-Name Short-names(this is alias column) 571647 CCPS_T CPP kindly provide the solution.its very urgent Thanks in Advance
> kindly provide the solution.its very urgent If you need a quick response, then first help us understanding your problem. The better the problem you explain, the better and swift response you get.
I'm not sure that I understand your data. Your first table has activity_code_id field that appears to be populated with an activitycode_type_id value. And the Proj_id value in that first table also appears to be an activitycode_type_id? Or should the first table's data be: taskid activitycode_type_id Activity_code_id Proj_id 571647 2760 8855 11346 571647 2761 8856 11346 ? As for the output - if you want the data in one column, then the [XML string concatenation trick][1] is the way to go. If, on the other hand, you're looking for multiple columns, some form of [PIVOT statement][2] would appear to be in order. [1]:
http://thelonedba.wordpress.com/2011/02/22/string-concatenation-using-xml-path/ [2]:
http://msdn.microsoft.com/en-us/library/ms177410(v=sql.105).aspx
Try This SELECT taskid,a.short_name,AT.Short_name Short_names FROM TASKACTIVE T INNER JOIN ACTVCODE A ON A.activitycode_Type_id = T.Activity_code_id INNER JOIN dbo.ACTVCODE AT ON T.Proj_id=AT.activitycode_Type_id
12 People are following this question.
