CODEWITHSUNDEEP
sqlsql-server
bjbreitling
bjbreitling

Reputation: 51

Join two tables and concatenate same column names in same statement

I have a large raw data table. It's long and I'm trying to sort of transpose it. I'm joining two select statements from within it. As such I get multiple columns with the same name. It's a full outer join and I would like to have the two separate columns of the same name as one column. Since it is an outer join I don't want to pick just one tables column for it either like select t1.c1

Thanks!

SELECT *
  FROM (SELECT * FROM [LabData].[dbo].[FermHourlyDCSData] where Attribute='Urea') P
  full outer JOIN 
(SELECT * FROM [LabData].[dbo].[FermHourlyDCSData] where Attribute='Water to Mash Total Water') FPD ON 
P.[TimeStamp] = FPD.[TimeStamp] 
and P.Site = FPD.Site
and P.Element = FPD.Element

Actual:

Site Attribute Timestamp Value Site Attribute Timestamp Value
AD   Urea      1/1/2019  127   Null Null      Null      Null
Null Null      Null      Null  AD   Water     1/1/2019  7.5

Expected/Desired:

Site Attribute Timestamp Value Value
AD   Urea      1/1/2019  127  Null
AD   Water     1/1/2019  Null 7.5

Upvotes: 0

Views: 3072

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

Maybe I'm missing something, but you seem to want a much simpler query:

select Site, Attribute, Timestamp,
       (case when Attribute = 'Urea' then Value end) as value_u,
       (case when Attribute = 'Water to Mash Total Water' then Value end) as value_2
from [LabData].[dbo].[FermHourlyDCSData]
where Attribute  in ('Urea', 'Water to Mash Total Water')

Upvotes: 0

Mr Slim
Mr Slim

Reputation: 1468

Try this, it's not very pretty, but it does work:

SELECT 
[Site] = ISNULL(P.[Site], FPD.[Site]),  
[Attribute] = ISNULL(P.[Attribute], FPD.[Attribute]),   
[Timestamp] =   ISNULL(P.[Timestamp], FPD.[Timestamp]), 
[Value] =   ISNULL(P.[Value], FPD.[Value]), 
[Element] =ISNULL(P.[Element], FPD.[Element])
FROM (SELECT * FROM [dbo].[FermHourlyDCSData] where Attribute='Urea') P
full outer JOIN 
(SELECT * FROM [dbo].[FermHourlyDCSData] where Attribute='Water to Mash Total Water') FPD ON 
P.[TimeStamp] = FPD.[TimeStamp] 
and P.Site = FPD.Site
and P.Element = FPD.Element

Upvotes: 1

Toni H
Toni H

Reputation: 83

ISNULL is what you should use for this

ISNULL(p.Site,fpd.Site) as [Site]

Upvotes: 1

Related Questions