How do I get all values of each Extension_ID

Question

I have a multitenant schema and here is my Query to retrieve the Column name from 1 table and values from another table.

SELECT 
MAX(CASE WHEN TME.EXTENSION_ID = '555' THEN EXT.value END) A,
MAX(CASE WHEN TME.EXTENSION_ID = '556' THEN EXT.value END) B,
MAX(CASE WHEN TME.EXTENSION_ID = '559' THEN EXT.value END) C,
MAX(CASE WHEN TME.EXTENSION_ID = '560' THEN EXT.value END) D,
MAX(CASE WHEN TME.EXTENSION_ID = '557' THEN EXT.value END) E,
MAX(CASE WHEN TME.EXTENSION_ID = '558' THEN EXT.value END) F,
MAX(CASE WHEN TME.EXTENSION_ID = '561' THEN EXT.value END) G,
MAX(CASE WHEN TME.EXTENSION_ID = '562' THEN EXT.value END) H
 FROM TENANTDATAEXTENSION EXT, TENANTMETADATAEXTENSION TME
 WHERE TME.EXTENSION_ID = EXT.EXTENSION_ID;

This only returns 1 row of each Extension_ID. But, I have more than 1 rows. How do I get all?

Current Output:

Column Name:    A    B    C    D     E    F     G     H
Column Values:  A1   B1   C1   D1    E1   F1    G1    H1

Expected Output:

Column Name:    A    B    C    D     E    F     G     H
Column Values:  A1   B1   C1   D1    E1   F1    G1    H1
                A2   B2   C2   D2    E2   F2    G2    H2
                A3   B3   C3   D3    E3   F3    G3    H3

TENANTDATAEXTENSION Table looks like this:

Column Name: Extension_ID Extension_Label
Column Values: 
                555      A
                556      B
                557      E
                558      F
                559      C
                560      D
                561      G
                562      H

TENANTMETADATAEXTENSION Table looks like this:

Column Name: Extension_ID Value.

Column Values:
                555      A1
                555      A2
                555      A3
                556      B1
                556      B2
                556      B3
                557      E1
                557      E2
                557      E3
                etc.........

Your queue is returning `MAX(EXT.value)` for each listed `EXTENSION_ID`. Why would you get more than one row? — PM 77-1, Apr 30 '15 at 03:46
I understand, but I need help to get more than 1 row. I am not sure how to get all rows? I tried without including MAX, but it returned null values. Thanks. — Katie, Apr 30 '15 at 03:52
Post some sample data and the desired output. May be it would clarify the task you need to perform. — PM 77-1, Apr 30 '15 at 03:53
We do not need your real data, nor we need every column. Post your two tables with some imaginary values of the actual data types and explain how exactly they should be flattened into your desired result. — PM 77-1, Apr 30 '15 at 17:05
I believe I understand now. Let me think how to achieve this. — PM 77-1, May 02 '15 at 01:49
Is it ok if you get all the values in one column as a comma delimited list? — Bohemian, May 02 '15 at 04:28

score 0 · Answer 1 · answered May 02 '15 at 03:01

See if the below code meets your needs.

SET @ext_id = '***';
SET @level = 0;


SELECT 
    MAX(CASE WHEN ext.EXTENSION_ID = '555' THEN tme.value ELSE '' END) A,
    MAX(CASE WHEN ext.EXTENSION_ID = '556' THEN tme.value ELSE '' END) B,
    MAX(CASE WHEN ext.EXTENSION_ID = '559' THEN tme.value ELSE '' END) C,
    MAX(CASE WHEN ext.EXTENSION_ID = '560' THEN tme.value ELSE '' END) D,
    MAX(CASE WHEN ext.EXTENSION_ID = '557' THEN tme.value ELSE '' END) E,
    MAX(CASE WHEN ext.EXTENSION_ID = '558' THEN tme.value ELSE '' END) F,
    MAX(CASE WHEN ext.EXTENSION_ID = '561' THEN tme.value ELSE '' END) G,
    MAX(CASE WHEN ext.EXTENSION_ID = '562' THEN tme.value ELSE '' END) H
FROM TENANTDATAEXTENSION ext
LEFT JOIN 
 ( SELECT CASE WHEN @ext_id <> tme.extension_id THEN @level := 0 ELSE @level := @level +1 END level,
        @ext_id := tme.extension_id extension_id,
        tme.value 
 FROM TENANTMETADATAEXTENSION tme
 ORDER BY extension_id, value ) tme
ON ext.extension_id = tme.extension_id
GROUP BY tme.level
HAVING CONCAT(A, B, C, D, E, F, G, H) <> ''

Here's SQL Fiddle DEMO

You may want to try your test cases there.

How do I get all values of each Extension_ID

1 Answers1