CODEWITHSUNDEEP
sqldb2
Erik
Erik

Reputation: 137

SQL Select query to pull multiple values from the same row as individual rows

I have a datatable that stores location values as such: CTY_CD, STATE_CD, COUNTRY_CD and I want to select from this table given a city code and select all three location codes as individual rows with another column showing what loc type it is, so the results of the query would look like:

Given city code DEN:

Type         |    Code
CTY_CD            DEN
STATE_CD          CO
COUNTRY_CD        US

Is there any way to do this?

Upvotes: 0

Views: 1195

Answers (1)

Sean
Sean

Reputation: 15164

something like this?

 select 'CTY_CD' AS Type,  CTY_CD AS Code FROM tableName WHERE CTY_CD = 'DEN'
 UNION
 select 'STATE_CD' AS Type, STATE_CD AS Code FROM tableName WHERE CTY_CD = 'DEN'
 UNION
 select 'COUNTRY_CD' AS Type, COUNTRY_CD AS Code FROM tableName WHERE CTY_CD = 'DEN'

Upvotes: 1

Related Questions