SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Values from a single column to two colums
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

reidkell
Starting Member

USA
15 Posts

Posted - 11/08/2013 :  18:01:50  Show Profile  Reply with Quote
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

547 Posts

Posted - 11/08/2013 :  20:15:25  Show Profile  Reply with Quote
something like this:



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;
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/09/2013 :  11:51:00  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000