| Computers Forum Index » Computer - Databases - MS SQL Server » Selecting multiple columns into single rows... |
|
Page 1 of 1 |
|
| Author |
Message |
| circuit_breaker... |
 Posted: Mon Oct 26, 2009 1:52 pm |
|
|
|
Guest
|
Hi,
The query below produces a table like this:
NodeName Engine RulesDate RulesVersion
--------------------------------------------------------
SRVN300 NULL NULL 3141
SRVN300 NULL 2008-11-05T15:14:47 NULL
SRVN300 7793 NULL NULL
SRVN301 NULL NULL 3142
SRVN301 NULL 2008-11-06T15:00:11 NULL
SRVN301 7794 NULL NULL
But instead, I'd like to get this format:
NodeName Engine RulesDate RulesVersion
--------------------------------------------------------
SRVN300 7793 2008-11-05T15:14:47 3141
SRVN301 7794 2008-11-06T15:00:11 3142
I suspect a UNION type of query would be the solution for it but I'd
never use it.
Thanks for your help.
Query:
--------
SELECT dbo.LeafNode.NodeName,
case dbo.ProductSettings.SettingName
when 'Anti-Spam Engine Version' then dbo.ProductSettings.[Value]
end as Engine,
case dbo.ProductSettings.SettingName
when 'Anti-Spam Rules Date' then dbo.ProductSettings.[Value]
end as RulesDate,
case dbo.ProductSettings.SettingName
when 'Anti-Spam Rules Version' then dbo.ProductSettings.[Value]
end as RulesVersion
FROM dbo.ProductProperties INNER JOIN
dbo.LeafNode ON dbo.ProductProperties.ParentID =
dbo.LeafNode.AutoID INNER JOIN
dbo.ProductSettings ON dbo.ProductProperties.AutoID =
dbo.ProductSettings.ParentID
GROUP by NodeName, SettingName,ProductCode, dbo.ProductSettings.
[Value]
having (dbo.ProductSettings.SettingName LIKE N'%Anti-Spam%') AND
(dbo.ProductProperties.ProductCode LIKE N'%GROUPSH%')
order by NodeName,SettingName |
|
|
| Back to top |
|
|
|
| circuit_breaker... |
| Posted: Mon Oct 26, 2009 2:11 pm |
|
|
|
Guest
|
Brilliant! Thanks very much  |
|
|
| Back to top |
|
|
|
| Plamen Ratchev... |
| Posted: Mon Oct 26, 2009 6:03 pm |
|
|
|
Guest
|
Try this:
SELECT dbo.LeafNode.NodeName,
MAX(CASE dbo.ProductSettings.SettingName
WHEN 'Anti-Spam Engine Version'
THEN dbo.ProductSettings.[Value]
END) AS Engine,
MAX(CASE dbo.ProductSettings.SettingName
WHEN 'Anti-Spam Rules Date'
THEN dbo.ProductSettings.[Value]
END) AS RulesDate,
MAX(CASE dbo.ProductSettings.SettingName
WHEN 'Anti-Spam Rules Version'
THEN dbo.ProductSettings.[Value]
END) AS RulesVersion
FROM dbo.ProductProperties
INNER JOIN dbo.LeafNode
ON dbo.ProductProperties.ParentID = dbo.LeafNode.AutoID
INNER JOIN dbo.ProductSettings
ON dbo.ProductProperties.AutoID = dbo.ProductSettings.ParentID
WHERE dbo.ProductSettings.SettingName LIKE N'%Anti-Spam%'
AND dbo.ProductProperties.ProductCode LIKE N'%GROUPSH%'
GROUP BY NodeName
ORDER BY NodeName;
--
Plamen Ratchev
http://www.SQLStudio.com |
|
|
| Back to top |
|
|
|
|
