Creating a Querie

Transact-SQL
1

Hi guys!

I'm trying to create a querie, but my problem is that I have in the SELECT statement two columns with the same field TEXTE. Basically, what I need is that in the column DESIGN1 the querie gives me the description when the field IDENT1 is '20' and the DESIGN2 when the same filed is '21'.

My querie is:

SELECT T1.NUM_0 AS NUMERO, T1.INVTYP_0 AS TIPO, T1.ACCDAT_0 AS DATA, T1.BPR_0 AS CLIENTE,
T2.BPRNAM_0 AS DESCR, T3.TSICOD_0 AS FAMILIA, T4.TEXTE_0 AS DESIGN1, T3.TSICOD_1 AS SUBFAM,
T4.TEXTE_0 AS DESIGN2, T3.QTY_0 AS QTD, T3.SAU_0 AS UNID, T3.NETPRINOT_0 AS PREÇO, T3.PFM_0 AS MARGEM,
T3.CPRPRI_0 AS CUSTO, T1.FCY_0 AS ESTAB, T3.REP1_0  AS REPRES
FROM FERTINAGRO.SINVOICE T1
LEFT JOIN FERTINAGRO.BPARTNER T2
ON T1.BPR_0=T2.BPRNUM_0
LEFT JOIN FERTINAGRO.SINVOICED T3
ON T1.NUM_0=T3.NUM_0
INNER JOIN FERTINAGRO.ATEXTRA T4
ON T3.TSICOD_0=T4.IDENT2_0
WHERE T1.ACCDAT_0 BETWEEN '01-01-2016' AND '31-01-2016' and T4.CODFIC_0='ATABDIV' AND T4.ZONE_0='LNGDES'
AND  T4.IDENT1_0 IN ('20','21')

Can you please help me with this? :confounded:

2

select ...,case when t4.ident1_0='20' then t4.texte_0 else null end as design1,...,case when t4.ident1_0='21' then t4.texte_0 else null end as design2,...

Ps.: You left join table alias t3, but as you inner join table alias t4, the t3 is in effect inner joined. Same goes with table alias t2.