CODEWITHSUNDEEP
sqlsql-server
Vignesh
Vignesh

Reputation: 95

SQL Server Split Single Rows into Two Rows

I have following query which will return a list of values in single row as follows:

  SELECT r_squared = CASE WHEN SS_tot = 0 THEN 1.0 ELSE 1.0 - ( SS_err / SS_tot ) END,
            Std_Dev,
            Alpha,
            SS_tot,
            SS_err,
            Beta1,
            Beta2,
            StdErrVar1,
            StdErrVar2,
            CASE WHEN @Const = 0 THEN NULL ELSE Beta1 + (@Const * StdErrVar1) END UpperLimit1,
            CASE WHEN @Const = 0 THEN NULL ELSE Beta1 - (@Const * StdErrVar1) END LowerLimit1,
            CASE WHEN @Const = 0 THEN NULL ELSE Beta2 + (@Const * StdErrVar2) END UpperLimit2,
            CASE WHEN @Const = 0 THEN NULL ELSE Beta2 - (@Const * StdErrVar2) END LowerLimit2                        
     FROM AlphaCalc
     CROSS JOIN BetaCalc
     CROSS JOIN SSCalc 

r_squared           Std_Dev             Alpha               SS_tot              SS_err               Beta1                  Beta2               StdErrVar1          StdErrVar2          UpperLimit1         LowerLimit1          UpperLimit2       LowerLimit2
+----------------------------------------------------------------------------------------------------------------------------------------+
0.931325401666624   1.08724114970357    -11.8437899314731   154.916666666667    10.6388398584788    -0.00448725387963724    1.00559107829696    0.00927464483890608 0.0909196724390537  0.0161779426244307  -0.0251524503837052 1.20817273286953    0.803009423724394

But I need Output as follows:

Variable    rsquared    StErr   StDev   Coefficients    Standard Error  Lower 95%   Upper 95%
+-----------------------------------------------------------------------------------------+
Beta1   0.931325402 10.63883986 154.9166667 -0.004487254    0.009285177 -0.025491783    0.016517276
Beta2   0.931325402 10.63883986 154.9166667 1.005591078 0.091022919 0.79968293  1.211499227

Upvotes: 0

Views: 46

Answers (1)

Chris du Preez
Chris du Preez

Reputation: 539

Based on my understanding of your question i think this is what you need:

( select 'Beta1' as Variable, r_squared as rsquared, SS_err as StErr, SS_tot as 'StDev', 
        Beta1 as Coefficients, StdErrVar1 as 'Standard Error',
        LowerLimit1 as 'Lower 95%',UpperLimit1 as 'Upper 95%'
from    
    (SELECT r_squared = CASE WHEN SS_tot = 0 THEN 1.0 ELSE 1.0 - ( SS_err / SS_tot ) END,
     Std_Dev, Alpha, SS_tot, SS_err, Beta1, Beta2, StdErrVar1, StdErrVar2,
     CASE WHEN @Const = 0 THEN NULL ELSE Beta1 + (@Const * StdErrVar1) END UpperLimit1,
     CASE WHEN @Const = 0 THEN NULL ELSE Beta1 - (@Const * StdErrVar1) END LowerLimit1,
     CASE WHEN @Const = 0 THEN NULL ELSE Beta2 + (@Const * StdErrVar2) END UpperLimit2,
     CASE WHEN @Const = 0 THEN NULL ELSE Beta2 - (@Const * StdErrVar2) END LowerLimit2  
     FROM AlphaCalc
     CROSS JOIN BetaCalc
     CROSS JOIN SSCalc) as dat1
)
union all
( select 'Beta2' as Variable, r_squared as rsquared, SS_err as StErr, SS_tot as 'StDev', 
        Beta2 as Coefficients, StdErrVar2 as 'Standard Error',
        LowerLimit2 as 'Lower 95%',UpperLimit2 as 'Upper 95%'
from    
    (SELECT r_squared = CASE WHEN SS_tot = 0 THEN 1.0 ELSE 1.0 - ( SS_err / SS_tot ) END,
     Std_Dev, Alpha, SS_tot, SS_err, Beta1, Beta2, StdErrVar1, StdErrVar2,
     CASE WHEN @Const = 0 THEN NULL ELSE Beta1 + (@Const * StdErrVar1) END UpperLimit1,
     CASE WHEN @Const = 0 THEN NULL ELSE Beta1 - (@Const * StdErrVar1) END LowerLimit1,
     CASE WHEN @Const = 0 THEN NULL ELSE Beta2 + (@Const * StdErrVar2) END UpperLimit2,
     CASE WHEN @Const = 0 THEN NULL ELSE Beta2 - (@Const * StdErrVar2) END LowerLimit2  
     FROM AlphaCalc
     CROSS JOIN BetaCalc
     CROSS JOIN SSCalc) as dat2
)

Upvotes: 1

Related Questions