CODEWITHSUNDEEP
sqlpivot-table
eggie5
eggie5

Reputation: 1960

alias column name by lookup query

I have a 'Table A' if I query that returns something like this:

DPSF0010001 | 4973
DPSF0010002 | 139
DPSF0010003 | 266
DPSF0010004 | 437
DPSF0010005 | 391
DPSF0010006 | 146
DPSF0010007 | 100
DPSF0010008 | 78
DPSF0010009 | 141
DPSF0010010 | 253
DPSF0010011 | 425
DPSF0010012 | 491
DPSF0010013 | 501
DPSF0010014 | 477
DPSF0010015 | 382
...
.
.

The query has multiple columns DPSFxxxx. The DPSFxxxxx can be referenced another table, where "DPSF0010001" can be decoded to a string: "Total: ". e.g.:

 Total:                      | dpsf0010001
  Under 5 years              | dpsf0010002
  5 to 9 years               | dpsf0010003
  10 to 14 years             | dpsf0010004
  15 to 19 years             | dpsf0010005
  20 to 24 years             | dpsf0010006
  25 to 29 years             | dpsf0010007
  30 to 34 years             | dpsf0010008
  35 to 39 years             | dpsf0010009
  40 to 44 years             | dpsf0010010
  45 to 49 years             | dpsf0010011
  50 to 54 years             | dpsf0010012
  55 to 59 years             | dpsf0010013
  60 to 64 years             | dpsf0010014
  65 to 69 years             | dpsf0010015
...
.
.

I want to replace DPSF0010001 in my query w/ it's associated string in Table B. e.g.:

BEFORE:

SELECT "DPSF0010001" from TableA;

 DPSF0010001
-------------
        4973
(1 row)

AFTER:

SELECT "DPSF0010001" from TableA;

 Total:
-------------
        4973
(1 row)

How can I replace the column names in my query w/ the lookup value? I know I can use AS to rename, how can I extend this to lookup the value in a different table?

Upvotes: 1

Views: 2135

Answers (2)

Hogan
Hogan

Reputation: 70529

How to solve this problem has to do with what you are doing with the result. If you have a front end with some ability program you can do a select like this (I'm assuming all column names are the same in both tables)

    SELECT "Column Head" as RowType, * FROM TABLEA
UNION ALL
    SELECT "Column Value" as RowType, * FROM TABLEB

This will give you something like this:

RowType         DPSF0010001     DPSF0010002     DPSF0010003     DPSF0010004     DPSF0010005     DPSF0010006     DPSF0010007     DPSF0010008     DPSF0010009     DPSF0010010     DPSF0010011     DPSF0010012     DPSF0010013     DPSF0010014     DPSF0010015
Column Head     Total:          Under 5 years   5 to 9 years    10 to 14 years  15 to 19 years  20 to 24 years  25 to 29 years  30 to 34 years  35 to 39 years  40 to 44 years  45 to 49 years  50 to 54 years  55 to 59 years  60 to 64 years  65 to 69 years
Column Value    4973            139             266             437             391             146             100             78              141             253             425             491             501             477             382

Which should be easy to display in whatever your front end is.

Upvotes: 0

Bill Karwin
Bill Karwin

Reputation: 562631

It's not totally clear what you're asking, but it sounds like you need a pivot query.

SQL doesn't allow data values to be used as column names or alias names dynamically in the same query that reads the data. The column names and alias names must be fixed before the query is parsed, which naturally comes before it reads any data from the tables. So there's no opportunity for data values to override, rename, or extend the set of columns.

Doing a pivot query therefore requires that you know the codes and their labels before you write the query. You can query your decoding table, and from those results, it's pretty simple to generate a series of expressions like the following:

SELECT 
  MAX(CASE code WHEN 'dpsf0010001' THEN value END) AS 'Total',
  MAX(CASE code WHEN 'dpsf0010002' THEN value END) AS 'Under 5 years',
  MAX(CASE code WHEN 'dpsf0010003' THEN value END) AS '10 to 14 years'
  . . .
FROM TableA;

Upvotes: 1

Related Questions