'Use in' Matrix in SQL

Question

I need help with following query.

I have 4 tables:

+------------+------------+--------------+-----------------+
|  project   |   motor    |  component   | motor_component |
+------------+------------+--------------+-----------------+
| project_id | motor_id   | component_id | mc_id           |
| name       | project_id | name         | motor_id        |
|            | name       |              | component_id    |
+------------+------------+--------------+-----------------+

I need to create query that return matrix 'Component is use in MOTOR X':

+----------------+---------+---------+---------+-----+---------+
| component.name | MOTOR 1 | MOTOR 2 | MOTOR 3 | ... | MOTOR X |
+----------------+---------+---------+---------+-----+---------+
| Flange         |       1 |         |         |     |         |
| Shaft          |         |       1 |         |     |         |
+----------------+---------+---------+---------+-----+---------+

In rows I need component name and 1 in columns MOTOR * when this component is in this motor for specyfic project (only one project in query).

I have query that return result for statics motor_id and for static quantity of motors (and static project_id, but this is not a problem):

SELECT c.name
      ,CASE WHEN EXISTS (SELECT i.name
                         FROM component i
                             JOIN motor_component_ i_mc
                               ON i.component_id = i_mc.component_id
                         WHERE i.component_id = c.component_id
                           AND i_mc.id_motor = 7
                        )
            THEN '1'
            ELSE NULL
            END AS 'MOTOR 1'
      ,CASE WHEN EXISTS (SELECT i.name
                         FROM component i
                             JOIN motor_component_ i_mc
                               ON i.component_id = i_mc.component_id
                         WHERE i.component_id = c.component_id
                           AND i_mc.id_motor = 12
                        )
            THEN '1'
            ELSE NULL
            END AS 'MOTOR 2'
FROM component c
    JOIN motor_component mc
      ON c.component_id = mc.component_id
    JOIN motor m
      ON mc.motor_id = m.motor_id
    JOIN project p
      ON m.project_id = p.project_id
WHERE p.project_id = 30

I don't know how to create dynamic query for unknown motors quantity and unknown motor_ids I don't know is it possible after all.

If possible, I would recommend carrying out this kind of transformation in your presentation layer. I appreciate that isn't always an option. Is there a safe upper limit to the number of components? — David Rushton, Feb 09 '17 at 14:47
You can use `PIVOT` to rotate the data, but the number of motors has to be fixed. You can't write a SQL query with an arbitrary number of columns — Panagiotis Kanavos, Feb 09 '17 at 14:51
@PanagiotisKanavos It can be done with dynamic sql, though having the variable columns makes it a bit useless for most reporting purposes besides manually copy-pasting the output. — iamdave, Feb 09 '17 at 15:09
@iamdave "with dynamic sql" means it can't be done. You are *still* writing out a specific number of columns. There is no such thing as `dynamic sql`, it's `dynamic construction of a sql query string` that will be executed at some point. That query though will have a fixed number of columns — Panagiotis Kanavos, Feb 09 '17 at 15:11
@PanagiotisKanavos It very much can be done: http://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query — iamdave, Feb 09 '17 at 15:12
@iamdave which doesn't prove otherwise. The script creates a sql string with a fixed number of columns, then executes it. It would be better to use `STRING_AGG` nowadays — Panagiotis Kanavos, Feb 09 '17 at 15:13
@PanagiotisKanavos I'm not sure we are on the same page here, check my answer below for what I mean. Variable number of `Motors` *and* `Components` both handled without issue... — iamdave, Feb 09 '17 at 15:24
@iamdave I want to query DB via MMS and paste data to Excell sheet. — Matt, Feb 09 '17 at 15:26
Important: it can be maximum 15 motors (could be less, but I think I can just put NULL if motor doesn't exist and always querying for 15 motors). But I still don't know how to manage with motor_id (will be different for project). — Matt, Feb 09 '17 at 15:28
@Matt Is this a regular task? If so, you would be better off using a standard dataset in an [Excel Table](https://support.office.com/en-gb/article/Overview-of-Excel-tables-7ab0bb7d-3a9e-4b56-a3c9-6c94334e492c?ui=en-US&rs=en-GB&ad=GB) which is referenced in a separate PivotTable. If you really just want to run a script and paste the output, adapt my answer below to use your tables and it'll output what you need. — iamdave, Feb 09 '17 at 15:28

score 1 · Accepted Answer · answered Feb 09 '17 at 15:23

1

You can create the pivot you want with dynamic SQL though it is very much not advised. This is better handled in your presentation layer:

if object_id('dbo.t') is not null
drop table t;

create table t(Motor int, Component nvarchar(50));
insert into t values(1,'Flange'),(2,'Shaft');

declare @cols as nvarchar(max),
    @query  as nvarchar(max);

set @cols = stuff((select distinct ',' + quotename(Motor)
            from t
            for xml path(''), type
            ).value('.', 'nvarchar(max)')
        ,1,1,'')

set @query = 'select Component, ' + @cols + ' from
            (
                select Motor
                        ,Component
                        ,1 as Installed
                from t
           ) x
            pivot
            (
                 sum(Installed)
                for Motor in (' + @cols + ')
            ) p '
            ;


execute(@query);

if object_id('dbo.t') is not null
drop table t;

Output:

+-----------+------+------+
| Component |  1   |  2   |
+-----------+------+------+
| Flange    | 1    | NULL |
| Shaft     | NULL | 1    |
+-----------+------+------+

answered Feb 09 '17 at 15:23

iamdave

12,023
3
24
53

I need something like that, but in one query (with sub queries) - it must be one main SELECT. Reason: if it will work I can give it to users who can perform scripts via UI, but only one select without permissions to create tmp tables or variable. – Matt Feb 10 '17 at 07:50
@Matt You should really have added that rather important information to your question. You cannot do this in one select without either changing how you are storing your data or pivoting the data in a second step. – iamdave Feb 10 '17 at 09:44
OK, understood. – Matt Feb 10 '17 at 11:17
I just checked and I can let users use variables, but I must avoid create temp table and inserting values to it. What do you think, is it possible to achieve? – Matt Feb 13 '17 at 08:26
@Matt My answer doesn't use a temp table. The `create table` that is there is just to create a table of dummy data to show how the query works. – iamdave Feb 13 '17 at 09:19

'Use in' Matrix in SQL

1 Answers1