SQL Query to get User Defined Fields joined into one row

Question

Dearest genius folk,

I have a vendor table, and a UDF (User Defined Functions) table. I need to query some data from the vendor table as well as data from the UDF table. Now, there will be more than one UDF result per vendor_id. (vendor_id and ud_join are the links between tables).

Vendor Table

| vendor_id  |  vendor_name  |  vendor_address  |  vendor_status |
-----------------------------------------------------------------
|    1234    |  ABC Company  |  123 Fourth St.  |    Active      |

UDF Table

|  udjoin  |  udtype  |  udindex  |  udvalue  |
-----------------------------------------------
|   1234   |   VN     |    36     |  Data36   |
-----------------------------------------------
|   1234   |   VN     |    53     |  Data53   |
-----------------------------------------------
|   1234   |   VN     |    67     |  Data67   |

I want to query the vendor_id of "1234", with a returned result of:

| vendor_id  |  vendor_name  |  vendor_address  |  vendor_status |  udf_36  |  udf_53  |  udf_67  |
--------------------------------------------------------------------------------------------------
|    1234    |  ABC Company  |  123 Fourth St.  |     Active     |  Data36  |  Data53  |  Data67  |

Hopefully this can be done with a basic query.
Thank you in advance for all of your assistance.

Answer 1

You may want to investigate the PIVOT operation; its availability and precise semantics depends upon the RDBMS you are using. Here are a few useful links to get you started:

• SQL Server: https://technet.microsoft.com/en-us/library/ms177410%28v=sql.105%29.aspx
• Oracle: http://www.oracle.com/technetwork/articles/sql/11g-pivot-097235.html

If you search the stackoverflow archives for "SQL Pivot" you will find many related questions.