I am trying to run sth like following, but it keeps on giving me null 'Value2' in case ID_period is not 10. I've tried WITH clause, but the script is too complex to provide multiple selects from that.
Example:
SELECT Y.ID_period, Y.ID_country, Y.Value,
(select Y.Value where ID_period = 10 and ID_country = Y.ID_country) as Value2
FROM (select A.ID_period, A.ID_country, A.Value
from dbo.xyz as A) Y
Thanks for any idea :)
I would rephrase this as a self join:
SELECT
t1.ID_period,
t1.ID_country,
t1.Value,
COALESCE(t2.Value, 'NA') AS Value2
FROM dbo.xyz t1
LEFT JOIN dbo.xyz t2
ON t1.ID_country = t2.ID_country AND t2.ID_period = 10;
WHERE clause?Here's one that avoids the full re-query on the assumption that the "Period" extracting subquery (A) will be cheaper than the full re-query. If it's not, it's unlikely to be much of an improvement:
declare @t table (Period_ID int, Country varchar(10),Value int)
insert into @t(Period_ID,Country,Value) values
(0,'UK',0),(10,'UK',10),(20,'UK',20),(30,'UK',30)
select
*
from (
select
COALESCE(A.Period_ID,Y.Period_ID) as Period_ID,
Y.Country,
Y.Value,
Z.Col
from
(select * from @t) Y
cross apply
(select CASE WHEN y.Period_ID = 10 THEN 'Value2' ELSE 'Value1' END as Col) Z
outer apply
(select Period_ID from @t t where t.Country = Y.Country
and Y.Period_ID = 10 and t.Period_ID != 10) A
) B
pivot (MAX(Value) for Col in (Value1,Value2)) C
Result:
Period_ID Country Value1 Value2
----------- ---------- ----------- -----------
0 UK 0 10
20 UK 20 10
30 UK 30 10
Note that I'm excluding a row being produced for Period_ID 10 since we've still not established via the comments whether that row is desirable (and if it is, it's definitely more work to do)
(If you take out the t.Period_ID != 10 filter in A, you'll get a row back for Period_ID 10, but it has NULL for Value. I guess we could fix that with a further COALESCE).
self left join can help you
if you have any special condition then you can use sub-query
SELECT Y.ID_period, Y.ID_country, Y.Value, X.value as value2
FROM
(
select A.ID_period, A.ID_country, A.Value
from dbo.xyz as A // where condition
) Y
left join
(
select A.ID_period, A.ID_country, A.Value
from dbo.xyz // where condition
) as x
on x.ID_country = Y.ID_country and x.ID_period=10
if no condition then simple self join can work for you what already said @Tim in his answer
period_ID10 where bothValueandValue2would be identical. Is that row required in your output?Value) will have results from subquery, 2nd column (Value2) will have another computation based on result from subquery withperiod_ID = 1010(as well as all of the others) and in that case, it'll be showing it's value twice. I was asking whether that row was an actually required one.