Display fields in a row

Question

I have a multi select query

SELECT C.CATEGORIES_NAME 
FROM CATEGORIES AS C 
WHERE CATEGORIES_ID = 1

UNION

SELECT L.LOCATION_TYPE_NAME 
FROM LOCATIONS_TYPE AS L 
WHERE LOCATION_TYPE_ID = 2 

UNION

SELECT S.SUBSCRIPTION_TYPE 
FROM SUBSCRIPTIONS AS S 
WHERE SUBSCRIPTION_ID = 3

He gives me the result:

 CATEGORIES_NAME |
------------------
       free      |
       Plaza     |
      Mobiles    |

And I need to get this result:

 CATEGORIES_NAME | LOCATION_TYPE_NAME | SUBSCRIPTION_TYPE |
-----------------------------------------------------------
       free      |       Plaza        |      Mobiles      |

How can this be done?

@mkRabbani This is the record ID, I wrote for example 1, 2, 3. It can be integers. — MegaRoks, May 08 '19 at 06:26
It can be integers fine, but there will be 3 different integers only? — mkRabbani, May 08 '19 at 06:32

Fahmi · Answer 1 · 2019-05-08T07:16:30.993

1

Use crosstab() - for reference you can check the details here

CREATE EXTENSION tablefunc;
    SELECT *
    FROM crosstab(
        'SELECT C.CATEGORIES_NAME 
    FROM CATEGORIES AS C 
    WHERE CATEGORIES_ID = 1

    UNION

    SELECT L.LOCATION_TYPE_NAME 
    FROM LOCATIONS_TYPE AS L 
    WHERE LOCATION_TYPE_ID = 2 

    UNION

    SELECT S.SUBSCRIPTION_TYPE 
    FROM SUBSCRIPTIONS AS S 
    WHERE SUBSCRIPTION_ID = 3'
    ) AS ct(CATEGORIES_NAME text, LOCATION_TYPE_NAME text, SUBSCRIPTION_TYPE text)

edited May 08 '19 at 07:16

answered May 08 '19 at 07:00

Fahmi

37,315
5
22
31

Error: `No function matches the given name and argument types. You might need to add explicit type casts.` – MegaRoks May 08 '19 at 07:14
@MegaRoks, you need to create using this command- CREATE EXTENSION tablefunc. Add this and then check – Fahmi May 08 '19 at 07:17
1

@MegaRoks . . . This seems much more complicated than necessary. – Gordon Linoff May 08 '19 at 11:38

score 1 · Accepted Answer · answered May 08 '19 at 11:38

I think the simplest solution is to basically put SELECT before the queries:

SELECT (SELECT C.CATEGORIES_NAME 
        FROM CATEGORIES AS C 
        WHERE CATEGORIES_ID = 1
       ) as CATEGORIES_NAME,
       (SELECT L.LOCATION_TYPE_NAME 
        FROM LOCATIONS_TYPE AS L 
        WHERE LOCATION_TYPE_ID = 2
       ) as LOCATION_TYPE_NAME,
       (SELECT S.LOCATION_TYPE_NAME 
        FROM SUBSCRIPTIONS AS S 
        WHERE SUBSCRIPTION_ID = 3
       ) as LOCATION_TYPE_NAME;

Display fields in a row

2 Answers2