Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Values from a single column to two colums

Author  Topic 

reidkell
Starting Member

16 Posts

Posted - 2013-11-08 : 18:01:50
Hi, all. As a simplified example, I have two tables. Table1 has a list of devices. Table2 contains two pieces of information per device, but in the same column. Here it is in a nutshell:

tblDevice: DeviceID, DeviceName

tblDevInfo: DeviceID, InfoType, InfoValue

Out of all the tblDevInfo.InfoTypes, I only need two, [IPAddress] and [SerialNum]. But, the output needs to be one row per device, as follows:

DeviceID | DeviceName | IPAddress | SerialNum
1 | ABC | 192.168.5.177 | A9723UJ
2 | DEF | 192.168.5.108 | JF98U2O
3 | USD | 192.168.5.116 | XO8F92

How do I obtain MULTIPLE columns from values in a SINGLE column?

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-11-08 : 20:15:25
something like this:
[CODE]


DECLARE @tblDevice TABLE(DeviceID INT, DeviceName VARCHAR(20));
INSERT INTO @tblDevice VALUES
(1, 'ABC'),
(2, 'DEF'),
(3, 'USD');

DECLARE @tblDeviceInfo TABLE(DeviceID INT, InfoType VARCHAR(100), InfoVal INT);
INSERT INTO @tblDeviceInfo VALUES
(1, 'ABC-192.168.5.177-A9723UJ-XYZ', 100),
(2, 'DEF-192.168.5.108-JF98U2O-XYZ', 100),
(3, 'USD-192.168.5.116-JF98U2O-XYZ', 100);



SELECT a.DeviceID, a.DeviceName, SUBSTRING(InfoType, 5,13) as IPAddress,
SUBSTRING(InfoType, 19, 7) as SerialNum from
@tblDevice a inner join @tblDeviceInfo b on a.DeviceID = b.DeviceID;
[/CODE]
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-09 : 11:51:00
I think this as per Ops description


SELECT d.DeviceID,
d.DeviceName,
di.IPAddress,
di.SerialNum
FROM tblDevice d
INNER JOIN (
SELECT DeviceID,
MAX(CASE WHEN InfoType = 'IPAddress' THEN InfoValue END) AS [IPAddress],
MAX(CASE WHEN InfoType = 'SerialNum' THEN InfoValue END) AS [SerialNum]
FROM tblDevInfo
GROUP BY DeviceID
)di
ON di.DeviceID = d.DeviceID


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -