Append an ID to fields on a SQL table joined multiple times?

Question

I have two tables

People

PersonId | Device1 | Device2 | Device3
---------+---------+---------+--------
    1         10       20         30

Device

DeviceId | Name | Description | PicklingCoefficient
---------+------+-------------+--------------------
   10       AA     Whatever             1
   20       BB     Who knows           -1
   30       CC     Blah blah           100

And a query that joins the first to the second three times:

select p.PersonId, d1.*, d2.*, d3.* from People p     
  left join Device d1 on d1.DeviceId = p.Device1
  left join Device d2 on d2.DeviceId = p.Device2
  left join Device d3 on d3.DeviceId = p.Device3

That produces the table headers in the format (people + device + device + device) where the Device table fields are just repeated three times:

PersonId | Device1 | Device2 | Device3 | DeviceId | Name | Description | PicklingCoefficient | DeviceId | Name | Description | PicklingCoefficient | DeviceId | Name | Description | PicklingCoefficient

I can technically build a string generator that would produce some unique names for each of the d(n) tables before I submit my query, but is there a way to simplify it?

Possibly by appending something to the first line of the query to give me the format (people + device1 + device2 + device3):

PersonId | Device1 | Device2 | Device3 | DeviceId1 | Name1 | Description1 | PicklingCoefficient1 | DeviceId2 | Name2 | Description2 | PicklingCoefficient2 | DeviceId3 | Name3 | Description3 | PicklingCoefficient3

I was hoping something like this would work:

select p.PersonId, d1.* + '1', d2.* + '2', d3.* + '3' from People p

You can use alias by replace d1.* as d1.DeviceId as DeviceId1, d1.Name as Name1 ... — hoangnh, Aug 13 '20 at 10:21
That's what I wanted to avoid, having to manually type out all of the column names because a) there's a lot of columns and b) the # of joins is dynamic — jamheadart, Aug 13 '20 at 10:22
The above statement *isn't* dynamic though, @jamheadart . If it is, you should be showing us same dynamic statement, as you may well be able to then achieve what you want dynamically. Though then the question is, why is your statement dynamic? — Thom A, Aug 13 '20 at 10:25
Also, the fact that your `People` table has columns like `Device1`, `Device2`, etc, etc is a design flaw unto itself. Perhaps the *real* solution is to normalise your design. — Thom A, Aug 13 '20 at 10:28
Why not one row per device (using UNIONs) ? Then you don't have duplicate column names. — Wouter, Aug 13 '20 at 10:29
@Larnu the situation is very much more complicated than I can fit into the question, I've simply provided a minimal example of what I want to achieve — jamheadart, Aug 13 '20 at 10:31
p.s. it's not my design, it's not even actually designed like that, it's all part of a view that I can't alter and I can't have multiple rows for the same Person. I just wanted to know if there was a quick way to append an ID to each field without naming each field in particular. — jamheadart, Aug 13 '20 at 10:36

score 1 · Answer 1 · answered Aug 13 '20 at 10:40

The real problem here is your design, it's denormalised. You appear to have a many to many relationship here, which means what you actually need to do is normalise your design.

Instead of having a have people with 3 different columns for a device, you should have another table, that handles to many-to-many relationship. In simple terms your tables would be like this:

CREATE TABLE dbo.People (PersonID int IDENTITY PRIMARY KEY,
                         [Name] nvarchar(50)); --Overly simplified
GO
CREATE TABLE dbo.Device (DeviceID int IDENTITY PRIMARY KEY,
                         [Name] char(2),
                         [Description] varchar (50),
                         PicklingCoefficient smallint);
GO
CREATE TABLE dbo.PersonDevice (PersonID int,
                               DeviceID int);

ALTER TABLE dbo.PersonDevice ADD CONSTARINT PersonDevice_PK PRIMARY KEY(PersonID, DeviceID);

ALTER TABLE dbo.PersonDevice ADD CONSTRAINT PersonDevice_PersonFK FOREIGN KEY (PersonID) REFERENCES dbo.People (PersonID);
ALTER TABLE dbo.PersonDevice ADD CONSTRAINT PersonDevice_DeviceFK FOREIGN KEY (DeviceID) REFERENCES dbo.Device (DeviceID);
GO

Then you would INSERT the data like so:

INSERT INTO dbo.People ([Name])
VALUES(N'Bob'),(N'Jayne');

INSERT INTO dbo.Device ([Name],[Description],PicklingCoefficient)
VALUES('AA','Whatever',1),
      ('BB','Who knows',-1),
      ('CC','Blah blah',100);
GO
--Then the device relationship
INSERT INTO dbo.PersonDevice(PersonID,DeviceID)
VALUES(1,1),(1,2),(1,3),(2,3);

Then you don't need multiple joins, as you just have 2, and there are no duplicate columns:

SELECT *
FROM dbo.People P
     JOIN dbo.PersonDevice PD ON P.PersonID = PD.PersonID
     JOIN dbo.Device D ON PD.DeviceID = D.DeviceID;

The data is originally set out like that. A view has been created that pivots the data and dynamically appends a single indicator column for however many instances of `Device` table is needed. It is then my job to join on to this view using those indicators and replace each indicator with a full instance of the table. — jamheadart, Aug 13 '20 at 10:44
Then what you are showing us isn't the real picture here, @jamheadart . You state, at the start of your question *"I have two tables"*, and then show denormalised data. Clearly, thetrefore, you **don't** have 2 tables, nor are they denomarlised (?). Show us the *real* problem... — Thom A, Aug 13 '20 at 10:53
I know, I couldn't possibly. I actually should have ignored the multiple joins and just asked if it were possible to append a string to all of a table's fields in the select line. I was trying to provide a bit of context, but now I'm getting answers for a question I wasn't asking! — jamheadart, Aug 13 '20 at 10:59

score 0 · Answer 2 · answered Aug 13 '20 at 10:41

0

I would advise you to unpivot and put the data on separate rows:

select p.PersonId, v.device_number, d.*
from People p  cross apply
     (values (1, p.Device1), (2, p.Device2), (3, p.Device3)
     ) v(device_number, device) left join
     Device d
     on d.DeviceId = v.device_number
order by p.PersonId, v.device_number;

answered Aug 13 '20 at 10:41

Gordon Linoff

1,242,037
58
646
786

It's unpivoted in the first place, a view pivots it and I'm joining to the view. My hands are tied somewhat. I'm just going to build a string on the front end that names each column individually. – jamheadart Aug 13 '20 at 10:47
@jamheadart . . . The view may not be doing you any favors. – Gordon Linoff Aug 13 '20 at 10:59
It'll take me ages to pick it apart and minimise it for here, I'll see what I can do towards the end of the day. – jamheadart Aug 13 '20 at 11:05
https://stackoverflow.com/questions/329931/sql-select-join-is-it-possible-to-prefix-all-columns-as-prefix – Mani Aug 13 '20 at 13:07

Append an ID to fields on a SQL table joined multiple times?

2 Answers2