Click here to Skip to main content
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 19:54pm
Edited 1-Jan-13 22: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 OriginalGriff 587
1 Sergey Alexandrovich Kryukov 479
2 Maciej Los 305
3 Mathew Soji 195
4 Richard MacCutchan 145
0 OriginalGriff 7,356
1 Sergey Alexandrovich Kryukov 6,712
2 DamithSL 5,461
3 Manas Bhardwaj 4,916
4 Maciej Los 4,475


Advertise | Privacy | Mobile
Web03 | 2.8.1411023.1 | Last Updated 2 Jan 2013
Copyright © CodeProject, 1999-2014
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