I have some data in x2 tables (d and i) that I want to join and split 1 table's data (i) in to 2 columns depending on data in another column of table i.

Example of the data I have is (I have cut out multiple fields as both tables hold a lot more data than I require):

Table: INFO

Table: DEVICE

What I want to do is join the 2 tables and depending on the value of i.det determines which column header i.data goes in to. So my query stands as:

SELECT d.dname as Unit_name, i.idata as Unit_Data FROM DEVICE d 
LEFT JOIN INFO i 
    ON d.dsite = i.isite AND d.dnum = i.inum 
WHERE (i.idet = 20 OR i.idet = 22)
AND d.dsite = 332;

Which will give me the data listing the dname twice with each piece of data as follows:

My desired output though would be as follows:

So basically when idet = 20 the idata is joined under Unit_MAC for the matching isite and inum, and when idet = 22 the idata is joined under the Unit_Manufacturer for the matching isite and inum.

Hopefully what I have put has made sense, I'm sure what I want is probably very simple but I can't wrap my head round what my query needs to be at the moment! Thanks for any pointers in advance!!

CodePudding user response：

Use conditional aggregation in your query:

SELECT d.dname AS Unit_name, 
       MAX(CASE WHEN i.idet = 20 THEN i.idata END) AS Unit_MAC,
       MAX(CASE WHEN i.idet = 22 THEN i.idata END) AS Unit_Manufacturer
FROM DEVICE d LEFT JOIN INFO i 
ON d.dsite = i.isite AND d.dnum = i.inum 
WHERE d.dsite = 332 AND (i.idet = 20 OR i.idet = 22)
GROUP BY d.dname;

I assume that dname is unique in DEVICE.

See the demo.

CodePudding user response：

SQL Fiddle (Assuming MSSQL)

DML Query ( a bit long-winded using CTES):

/*Resolve the mac address of devices:
infonumbers => CTE*/
WITH infonumbers AS (
  SELECT
    isite,
    inum, 
    idet,
    idata AS unit_mac
  FROM
    INFO
  WHERE
    ISNUMERIC(idata) = 1
 ),

/*Resolve the device manufacturers devices:
infostrings => CTE*/
 infostrings AS (
  SELECT
    isite,
    inum, 
    idet,
    idata AS unit_manufacturer
  FROM
    INFO
  WHERE
    ISNUMERIC(idata) = 0
 )

/*Join together with devices to resolve device name,
mac address, and manufacturer: TABLE => stdout(console)*/
  SELECT 
    d.dname,
    ino.unit_mac,
    istr.unit_manufacturer
   FROM 
     infonumbers ino
   INNER JOIN
     infostrings istr
   ON
     ino.isite = istr.isite
   AND
     ino.inum = istr.inum
   LEFT OUTER JOIN
     DEVICE d
   ON 
     ino.isite = d.dsite 
   AND 
     ino.inum = d.dnum 

(DDL) Data:

CREATE TABLE INFO (
  isite INTEGER NOT NULL,
  inum INTEGER NOT NULL,
  idet INTEGER NOT NULL,
  idata NVARCHAR(MAX) NOT NULL
);

INSERT INTO INFO 
VALUES 
  (332, 1,  20, '001122334455'),
  (332, 2,  20, '112233445566'),
  (332, 3,  20, '223344556677'),
  (332, 1,  22, 'Samsung'),
  (332, 2,  22, 'Sony'),
  (332, 3, 22, 'LG')
  
CREATE TABLE DEVICE (
  dsite INTEGER NOT NULL,
  dnum INTEGER NOT NULL,
  dname NVARCHAR(MAX) NOT NULL,
);

INSERT INTO DEVICE
VALUES
  (332, 1, 'abc123'),
  (332, 2, 'def456'),
  (332, 3, 'ghi789')