Query help

Transact-SQL
1

Can i do the same thing in one query .

Basically i want to get both records into same record

drop table if exists dbo.DT
drop table if exists dbo.T1
drop table if exists #temp
Create table dbo.T1( col1 int , phase varchar(100), value varchar(100))
create table dbo.DT ( phase int,phase_name varchar(100))

INSERT INTO T1
select 1, 1, 'V1' union
select 1 , 2, 'V2' union
select 2, 1 , 'CT1' union
select 3 , 1, 'VX1' union
select 3 ,2,'VX2'

insert into dbo.DT
select 1 , 'Phase1' union
select 2, 'Phase2'

select t1.col1, DT.phase as phase1_name, case when t1,phase=1 THEN T1.value else NULL end as phase1_value
INTO #temp
from T1
left join dbo.DT dt ON t1.phase=dt.phase AND t1.phase=1

select * from T1

select temp.col1,temp.phase1_name,Temp.phase1_value, dt.phase_name as phase2_name ,T1.value as phase2_value
from #temp temp
left join dbo.T1 t1 ON t1.col1=temp.col1 and t1.phase=2
left join dbo.DT dt ON t1.phase=dt.phase

2

What your database version?

3

Yes, you can do it in one query:

;WITH cte_DT AS (
    SELECT * FROM (VALUES(1, 'Phase1'),(2, 'Phase2')) DT(phase, phase_name)
),
cte_T1 AS (
    SELECT* FROM (VALUES(1, 1, 'V1'),(1 , 2, 'V2'),(2, 1, 'CT1'),(3, 1, 'VX1'),(3, 2,'VX2')) T1(col1, phase, value)
)
select temp.col1,temp.phase1_name,Temp.phase1_value, dt.phase_name as phase2_name ,T1.value as phase2_value
from (
    select t1.col1, DT.phase as phase1_name, case when t1.phase=1 THEN T1.value else NULL end as phase1_value
    from cte_T1 t1
    left join cte_DT dt ON t1.phase=dt.phase AND t1.phase=1
) as temp
left join cte_T1 t1 ON t1.col1=temp.col1 and t1.phase=2
left join cte_DT dt ON t1.phase=dt.phase
--order by col1, phase1_name, phase2_name
4

Thanks . Only issue for me is for phase1_name and Value should also appear for both rows for a given col1 , becuase phase 1 exists for all records

5

I believe I used exactly the data and joins you used. You'd need to verify that the original data, joins, and column selects are correct.