SQL表1列数据根据另一列拆分为2列

Question

我有一些数据在x2表(d和I)，我想加入和分割1表的数据(I)在2列取决于数据在表I的另一列

我所拥有的数据示例是(我已经删除了多个字段，因为两个表都包含了比我需要的更多的数据):

表:信息

<表类> isite inum idet idata tbody><<tr>332120001122334455332220112233445566332320223344556677332122道明>332222索尼332322LG

在查询中使用条件聚合:

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;

我假设dname在DEVICE中是唯一的。

SQL Fiddle(假设为MSSQL)

DML查询(使用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)数据:

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')