Values from a single column to two colums

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]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources