how do i get the below output in T-SQL 2008?

Question

  

Hi ,
 
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
 
CREATE TABLE [dbo].[ACTVCODE](
    [actv_code_id] [int] NOT NULL,
    [actv_code_type_id] [int] NOT NULL,
    [short_name] [varchar](60) NOT NULL,
    [actv_code_name] [varchar](120) NULL,

 )
INSERT INTO [ACTVCODE] VALUES (8855,2760,'CCPS_T','Miletones')
INSERT INTO [ACTVCODE] VALUES (8856,2761,'CPP','2*70MW power plant')
SELECT * FROM ACTVCODE

CREATE TABLE [dbo].[TASKACTV](
    [task_id] [int] NOT NULL,
    [actv_code_type_id] [int] NOT NULL,
    [actv_code_id] [int] NOT NULL,
    [proj_id] [int] NOT NULL,
)
INSERT INTO [TASKACTV] VALUES (571647,2760,'8855',11346)
INSERT INTO [TASKACTV] VALUES (571647,2761,'8856',11346)

--REQUIRED OUTPUT-----------
task_id  short_name short_name1(alias name)
 
571647 CCPS_T CPP

 

kindly provide the solution.
 
Thanks in Advance

---

sai

Answer 1

Try this query below

 

Select T1.TaskId,T3.Short_Name
From @TASKACTIVE T1
Join @ACTTYPE T2 on T1.activity_code_Id = T2.activitycode_Type_id
Join @ACTVCODE T3 On T3.activitycode_Type_id =  T2.activitycode_Type_id

Thanks,

Santhosh

---

Please have look on the comment

Answer 2

Santhosh ,

with your query we will get the out put into 2 times like

Taskid Short-Name Short-names(this is alias column)
571647 CCPS_T 

571647 CPP 

but i need with in single statement it should come

---

sai

Answer 3

Check this

;With  Cte As
(
Select T1.TaskId,T3.Short_Name
From @TASKACTIVE T1
Join @ACTTYPE T2 on T1.activity_code_Id = T2.activitycode_Type_id
Cross Join @ACTVCODE T3 
)
Select Distinct T2.TaskId,
Stuff((
Select ',' + T1.Short_Name 
 From Cte T1 
Where T1.TaskId = T2.TaskId 
 For Xml Path ('')),1,1,''
)ShortNames 
From Cte T2

---

Please have look on the comment

Answer 4

Santhosh CTE  is giving the syntatical error

---

sai

Answer 5

I didn't get the requirement fully.. but try this if this helps:

Select e.TaskID,E.Short_Name as ShortName,T3.Short_Name as ShortName from (Select taskid,Proj_id,Short_name
From TASKACTIVE T1
Join ACTVCODE T2 on T1.activity_code_Id = T2. activitycode_Type_id)e
Join ACTVCODE T3 on e.Proj_id = t3.activitycode_Type_id

Regards,Eshwar.

---

Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Answer 6

See this i am not getting any error

Declare @TASKACTIVE Table
(
TaskId Int,
activitycode_Type_id  Int,
Activity_code_id  Int,
Proj_id  Int
)

Declare @ACTTYPE Table
(
activitycode_Type_id  Int,
activitycode_Type   Varchar(30),
 Proj_id Int
) 
 
Declare @ACTVCODE Table
(
Activity_code_id Int,
activitycode_Type_id  Int,
actv_code_name Varchar(50),
Short_name   Varchar(50)
)
Insert @TASKACTIVE
Select 571647, 571647, 2760, 2761

Insert @ACTTYPE
Select 2760, 'CCPS_DIV', 11346 Union All
Select 2761, 'CCPS_PP' ,11346

insert @ACTVCODE
Select 8855, 2760, 'Miletones', 'CCPS_T' Union all
Select 8856, 2761,' 2*70MW power plant', 'CPP '

--Select * From @TASKACTIVE
--Select * From @ACTTYPE
--Select * From @ACTVCODE
 

;With  Cte As
(
Select T1.TaskId,T3.Short_Name
From @TASKACTIVE T1
Join @ACTTYPE T2 on T1.activity_code_Id = T2.activitycode_Type_id
Cross Join @ACTVCODE T3 
)
Select Distinct T2.TaskId,
Stuff((
Select ',' + T1.Short_Name 
 From Cte T1 
Where T1.TaskId = T2.TaskId 
 For Xml Path ('')),1,1,''
)ShortNames 
From Cte T2

---

Please have look on the comment

Answer 7

Hi ,

just now i verified recent updation , thank you for you reply , but its not suitable to my requiremet.

your query out put is like below , in short names it is giving comma separated values , but i required it in separate column

Task_id    Short_names

571647 CCPS_T,CPP 

but my expected out put is

task_id  short_name    short_names

571647    CCPS_T          CPP

---

sai

Answer 8

Is this not meeting your requirement?

Select e.TaskID,E.Short_Name as ShortName,T3.Short_Name as ShortNames from (Select taskid,Proj_id,Short_name
From TASKACTIVE T1
Join ACTVCODE T2 on T1.activity_code_Id = T2. activitycode_Type_id)e
Join ACTVCODE T3 on e.Proj_id = t3.activitycode_Type_id

Regards,Eshwar

---

Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Answer 9

There are 2 records with same task_id. Now you want to show short_name values in 2 diff cols

1 - short_name

2 - short_name1

but, on what basis it is identified which value gonna come in which column

---

Sandeep Mittal | My Blog - IT Developer Zone

Answer 10

Solution, assuming the "actv_code_id" for alias would always be greater than its full name for the same task_id

;with cte as (
	select	task_id, short_name, ROW_NUMBER() over(partition by task_id order by a.actv_code_id) as rid
	from	ACTVCODE a
	inner join [TASKACTV] b on a.actv_code_id = b.actv_code_id
)	
select	a.task_id, a.short_name, b.short_name as short_name1
from	cte a
inner join cte b on a.task_id = b.task_id and a.rid = b.rid-1

---

Sandeep Mittal | My Blog - IT Developer Zone

Answer 11

Many thanks Sandeep

---

sai

Answer 12

Hi ,

just now i verified recent updation , thank you for you reply , but its not suitable to my requiremet.

your query out put is like below , in short names it is giving comma separated values , but i required it in separate column

Task_id    Short_names

571647 CCPS_T,CPP 

but my expected out put is

task_id  short_name    short_names

571647    CCPS_T          CPP

---

sai

I guess i did not read ur requirement initially, belwo query wrks as per ur req 

 ;With  Cte As
(
Select Row_Number () Over (Partition By T1.TaskId Order By T3.Short_Name) As RwId,T1.TaskId,T3.Short_Name
From @TASKACTIVE T1
Join @ACTTYPE T2 on T1.activity_code_Id = T2.activitycode_Type_id
Cross Join @ACTVCODE T3 
)
Select T1.TaskId,T1.Short_Name,T2.Short_Name As ShortNames From Cte T1
Inner Join Cte T2 On T1.TaskId = T2.TaskId And T1.RwId = T2.RwId - 1

Thanks

Santhosh

---

Please have look on the comment