CODEWITHSUNDEEP
sqlsql-serverjoinlookup
AJBloomie
AJBloomie

Reputation: 25

lookup tables with temporary fields?

I have a query that produces the following results:

table1:

degree_code  occupation_code  degree_completions  degree_level
1.0000       20-2021          10                  1
1.0000       20-2022          10                  1
1.1051       52-2095          2                   3
1.1051       52-2095          41                  2
1.5010       15-1100          2                   3

I have another small, lookup table that relates degree_level to custom categories that I need to use:

table2

degree_level  degree_level_recode
1             AADEGREE
2             AADEGREE
3             BACHDEGREE

I would like to build the output of of the first query to report the following format:

degree_code  occupation_code  degree_completions  degree_level AADEGREE BACHDEGREE
1.0000       20-2021          10                  1            10       0
1.0000       20-2022          10                  1            10       0
1.1051       52-2095          2                   3            3        0
1.1051       52-2095          41                  2            0        41
1.5010       15-1100          2                   3            2        1

Basically, create new temporary recode fields in the original query that report the degree_completions underneath them when they match the degree_level_recode, input 0 if no match. This is highly simplified; I will be performing operations on the recode fields against other fields in each element in the query.

I've shown the degree_completions field for reference, but I'd leave it out in the final query for obvious redundancy reasons.

I'm thinking I need to use a CASE statement or something similar for comparison checking, but I'm new to SQL.

EDIT:

In reference to Cha's answer below, take a revised table1 output (after mapping the recode):

degree_code  degree_level  degree_completions  degree_level_recode
01.0601      2             11                  LESSCOLL
01.0601      3             22                  AADEGR
01.0605      2             3                   LESSCOLL

Consider this code (table2 is referenced above the edit):

SELECT degree_code
    ,degree_level
    ,[LESSCOL] AS LESSCOL
    ,[AADEGR] AS AADEGR
    ,[BACHDEGR] AS BACHDEGR
    ,[MADEGR] AS MADEGR
    ,[DOCDEGR] AS DOCDEGR
FROM 
    (
    SELECT degree_code
        ,table1.degree_level
        ,degree_level_recode
        ,degree_code
     FROM table1
          ,table2 
     WHERE table1.degree_level = table2.degree_code
     ) AS p
     PIVOT
         (
         SUM (degree_completions)
         FOR degree_level_recode IN ([LESSCOL], [AADEGR], . . .)
         ) AS pvt

Which produces these results:

degree_code  degree_level  LESSCOL  AADEGR  BACHDEGR  MADEGR  DOCDEG  
01.0601      2             NULL     NULL    NULL      NULL    NULL
01.0601      3             NULL     22      NULL      NULL    NULL
01.0505      2             NULL     NULL    NULL      NULL    NULL

I'm trying to get to:

degree_code  degree_level  LESSCOL  AADEGR  BACHDEGR  MADEGR  DOCDEG  
01.0601      2             11       NULL    NULL      NULL    NULL
01.0601      3             NULL     22      NULL      NULL    NULL
01.0505      2             3        NULL    NULL      NULL    NULL

Additionally, replace NULL with 0.

Upvotes: 1

Views: 275

Answers (1)

cha
cha

Reputation: 10411

This is what you are after (assumptions: your first table is called #temp1, your second table is called #temp2):

SELECT *
FROM
#temp1,
(SELECT degree_level, [AADEGREE] as col1, [BACHDEGREE] as col2
FROM 
(SELECT degree_completions, #temp1.degree_level, degree_level_recode
FROM #temp1, #temp2 WHERE #temp1.degree_level = #temp2.degree_level) AS p
PIVOT
(
SUM (degree_completions)
FOR degree_level_recode IN
([AADEGREE], [BACHDEGREE])
) AS pvt
) as V
WHERE #temp1.degree_level = V.degree_level
ORDER BY 1

Upvotes: 1

Related Questions