-1

I have 3 tables: Device, Service, DeviceXService

Table (Device) has a list of devices and fields associated to the devices such as serial number device name etc.

DeviceID | Device Name | SerialNumber
1        | iPhone      | 2352532533
2        | iPad        | 2345435435
3        | android     | 2532532656

Table (Service) is a lookup table that has a list of services that can be used on devices such as email, internet, texting etc.

ServiceID  | ServiceName
1          | email
2          | internet
3          | texting

Table (DeviceXService) is a cross-reference table that has a record linking devices to services and the status of those devices.

For example.

DeviceID    | ServiceID   | Status
--------------------------------------
1(iPhone)   | 1(email)    | requested
2(ipad)     | 2(internet) | Approved
1(iPhone)   | 3(texting)  | Approved
3(android)  | 3(texting)  | approved

What I would like to do is create a query that would return all the devices FROM the Devices table, but also create a column for each type of service that exists FROM the Service table and return the status of each service for each device FROM the DeviceXService cross-reference table as one table.

example:

Device ID | Device Name | Device Serial No | email    |  texting   |  internet
--------------------------------------------------------------------------------
    1     | iphone      | 2352532533       | requested|  approved  |  null
    2     | ipad        | 2345435435       | null     |  null      |  approved
    3     | android     | 2532532656       | null     |  null      |  approved

Note: null is if the device doesn't have a record for the service in the DeviceXService cross-reference table

I apologize if I'm not explaining this very well but that may be why I'm having such a hard time trying to find an example of something similar. Any help would be greatly appreciated!

Barranka
  • 20,547
  • 13
  • 65
  • 83
A Work In Progress
  • 11
  • 1
  • 1
  • 3

1 Answers1

0

If the number of rows in Service table is constant and known, you can do this like this:

select
 d.*,
 (select status from DeviceXService where Device_id=d.DeviceID and ServiceID=1) as email,
 (select status from DeviceXService where Device_id=d.DeviceID and ServiceID=2) as texting,
 (select status from DeviceXService where Device_id=d.DeviceID and ServiceID=3) as internet
from
 device d;

The result looks like this:

 id |  name   | serial | email | texting | internet 
----+---------+--------+-------+---------+----------
  1 | iphone  | 123    | req   | app     | 
  2 | ipad    | 234    |       |         | app
  3 | android | 345    |       |         | app
(3 rows)

If you want it to be dynamical, you can simply generate such a query on the basis of the data from Service table in code and then run it against the database.

I don't fell like transposing rows to columns by sql query is the best approach if you can do it much simpler in code.

Edit:

You can look at this question or this one, they concerns quite the same subject - dynamic SQL.

Community
  • 1
  • 1
Michał Kołodziejski
  • 666
  • 5
  • 6
  • Thanks for the response. I think you've pointed me in the right direction. I do want the columns to be dynamic since new services can be added to the table at any time. Can you elaborate on what you mean by "generate such a query on the basis of the data from the service table in code and then run it against the database?" – A Work In Progress Mar 12 '14 at 18:22
  • What I meant is that you can query the Service table to fetch all rows, and then build a second query which will have all the columns you need - it requires a loop on rows from Service table in which you'll add to query those parts: `(select status from DeviceXService where Device_id=d.DeviceID and ServiceID=) as ,`. Also, think about the performance of such statement - for each device it'd need to query N times DeviceXService table to get column values. – Michał Kołodziejski Mar 12 '14 at 21:48