Click here to Skip to main content
11,647,748 members (70,500 online)
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-Server TSQL
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
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)

SELECT * FROM TASKACTV

SELECT     TASKACTV.actv_code_id, ACTVCODE.short_name, ACTVCODE.actv_code_name, TASKACTV.task_id
FROM         ACTVCODE INNER JOIN
                      TASKACTV ON ACTVCODE.actv_code_id = TASKACTV.actv_code_id

--REQUIRED OUTPUT-----------
task_id  short_name short_name1(alias name)

571647 CCPS_T CPP
kindly provide the solution. Thanks in Advance
Posted 1-Jan-13 18:54pm
Edited 1-Jan-13 21:30pm
v3
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Try
SELECT T.Taskid, A1.Short_name, A2.Short_name AS [Short-Names]
FROM TASKACTIVE T
INNER JOIN ACTVCODE A1 ON T.Activity_code_id = A1.activitycode_Type_id 
INNER JOIN ACTVCODE A2 ON T.Activity_code_id = A2.activitycode_Type_id
  Permalink  
Comments
FranklinRemo at 2-Jan-13 3:41am
   
Hi _TR_21K , actually i have mention table structure also . kindly check it once and share your ideas
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

Hi , finally i resolved the issue my self. for guidance please refer it.
;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
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 jyo.net 500
1 F-ES Sitecore 410
2 OriginalGriff 322
3 DamithSL 315
4 CPallini 250
0 OriginalGriff 1,342
1 jyo.net 994
2 DamithSL 971
3 Sergey Alexandrovich Kryukov 873
4 CPallini 795


Advertise | Privacy | Mobile
Web03 | 2.8.150804.3 | Last Updated 2 Jan 2013
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100