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.
Author |
Topic |
mgreen84
Yak Posting Veteran
94 Posts |
Posted - 2013-03-27 : 18:56:33
|
I have a select statement that returns to records, that are pretty much the exact same record except one field. I hope to combine these two records into one.the two records appear as: AlertID AlertType Datetype MetricID PAGroupID AreaID value1 value2 Active CreatedBy CreatedDate10 2 D 71 833 NE 2 3 1 9838011 2013-03-27 00:00:00.00010 2 D 71 1028 NE 2 3 1 9838011 2013-03-27 00:00:00.000 But what I want to return is: AlertID AlertType Datetype MetricID PAGroupID AreaID value1 value2 Active CreatedBy CreatedDate10 2 D 71 833,1028 NE 2 3 1 9838011 2013-03-27 00:00:00.000 Any help would be greatly appreciated.ThanksMy current select: SELECT AlertID, AlertType, Datetype, MetricId, PAGroupID, al.AreaID, value1, value2, al.Active, CreatedBy, CreatedDate FROM Config.tblAlerts al INNER JOIN Config.tblAlertsPagroups alpa ON al.ID = alpa.AlertID INNER JOIN Config.tblPAGroup pa ON alpa.PAGroup = pa.PAGroupID WHERE al.ID = 10 |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-03-27 : 20:08:03
|
If there will always be two (no more, no less), then you can use the following:SELECT AlertID, AlertType, Datetype, MetricId, CAST(MIN(PAGroupID) as varchar(32)) + ',' + CAST(MAX(PAGroupID) as varchar(32)) as PAGroupID, al.AreaID, value1, value2, al.Active, CreatedBy, CreatedDateFROM Config.tblAlerts al INNER JOIN Config.tblAlertsPagroups alpa ON al.ID = alpa.AlertID INNER JOIN Config.tblPAGroup pa ON alpa.PAGroup = pa.PAGroupID WHERE al.ID = 10group by AlertID, AlertType, Datetype, MetricId, al.AreaID, value1, value2, al.Active, CreatedBy, CreatedDate |
|
|
mgreen84
Yak Posting Veteran
94 Posts |
Posted - 2013-03-28 : 11:31:17
|
Does this method go out the window if there are more than 2 in my PAGroup field? Can this method work for, lets say a PAGroup has 5 records, and the max-length of a PAGroup is (4) and the min-length is (1). Is it possible to use this concept for that?quote: Originally posted by James K If there will always be two (no more, no less), then you can use the following:SELECT AlertID, AlertType, Datetype, MetricId, CAST(MIN(PAGroupID) as varchar(32)) + ',' + CAST(MAX(PAGroupID) as varchar(32)) as PAGroupID, al.AreaID, value1, value2, al.Active, CreatedBy, CreatedDateFROM Config.tblAlerts al INNER JOIN Config.tblAlertsPagroups alpa ON al.ID = alpa.AlertID INNER JOIN Config.tblPAGroup pa ON alpa.PAGroup = pa.PAGroupID WHERE al.ID = 10group by AlertID, AlertType, Datetype, MetricId, al.AreaID, value1, value2, al.Active, CreatedBy, CreatedDate
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-28 : 12:17:28
|
[code];With CTEAS(SELECT AlertID, AlertType, Datetype, MetricId, PAGroupID, al.AreaID, value1, value2, al.Active, CreatedBy, CreatedDateFROM Config.tblAlerts al INNER JOIN Config.tblAlertsPagroups alpa ON al.ID = alpa.AlertID INNER JOIN Config.tblPAGroup pa ON alpa.PAGroup = pa.PAGroupID WHERE al.ID = 10)SELECT c.*,STUFF((SELECT ',' + CAST(PAGroupID AS varchar(10)) FROM CTE WHERE AlertID = c.AlertID AND AlertType = c.AlertTypeAND Datetype = c.datetypeAND MetricId = c.MetricIdAND AreaID = c.AreaIDAND value1 = c.value1AND value2 = c.value2AND Active = c.ActiveAND CreatedBy = c.CreatedBy AND CreatedDate = c.CreatedDate ORDER BY PAGroupID FOR XML PATH('')),1,1,'') AS PAGroupIDFROM (SELECT DISTINCT AlertID, AlertType, Datetype, MetricId, AreaID, value1, value2, Active, CreatedBy, CreatedDateFROM CTE) c[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
mgreen84
Yak Posting Veteran
94 Posts |
Posted - 2013-03-28 : 13:21:34
|
Wow, thanks I didn't think to use a common table expression. works perfectly. Much appreciated, I do have a question though. If I wanted to add a field (pagroupname), would I beable to add to this current script? Because I forgot also needed to while having the PAGrouID field together like that, I also need to see if I can get the PAGroupName in the same formatquote: Originally posted by visakh16
;With CTEAS(SELECT AlertID, AlertType, Datetype, MetricId, PAGroupID, al.AreaID, value1, value2, al.Active, CreatedBy, CreatedDateFROM Config.tblAlerts al INNER JOIN Config.tblAlertsPagroups alpa ON al.ID = alpa.AlertID INNER JOIN Config.tblPAGroup pa ON alpa.PAGroup = pa.PAGroupID WHERE al.ID = 10)SELECT c.*,STUFF((SELECT ',' + CAST(PAGroupID AS varchar(10)) FROM CTE WHERE AlertID = c.AlertID AND AlertType = c.AlertTypeAND Datetype = c.datetypeAND MetricId = c.MetricIdAND AreaID = c.AreaIDAND value1 = c.value1AND value2 = c.value2AND Active = c.ActiveAND CreatedBy = c.CreatedBy AND CreatedDate = c.CreatedDate ORDER BY PAGroupID FOR XML PATH('')),1,1,'') AS PAGroupIDFROM (SELECT DISTINCT AlertID, AlertType, Datetype, MetricId, AreaID, value1, value2, Active, CreatedBy, CreatedDateFROM CTE) c ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-03-28 : 14:20:05
|
You can use the same pattern as what Visakh used for the PAGroupID;With CTEAS(SELECT AlertID, AlertType, Datetype, MetricId, PAGroupID, al.AreaID, value1, value2, al.Active, CreatedBy, CreatedDate,pagroupnameFROM Config.tblAlerts al INNER JOIN Config.tblAlertsPagroups alpa ON al.ID = alpa.AlertID INNER JOIN Config.tblPAGroup pa ON alpa.PAGroup = pa.PAGroupID WHERE al.ID = 10)SELECT c.*,STUFF((SELECT ',' + CAST(PAGroupID AS varchar(10)) FROM CTE WHERE AlertID = c.AlertID AND AlertType = c.AlertTypeAND Datetype = c.datetypeAND MetricId = c.MetricIdAND AreaID = c.AreaIDAND value1 = c.value1AND value2 = c.value2AND Active = c.ActiveAND CreatedBy = c.CreatedBy AND CreatedDate = c.CreatedDate ORDER BY PAGroupID FOR XML PATH('')),1,1,'') AS PAGroupID,STUFF((SELECT ',' + CAST(pagroupname AS varchar(10)) FROM CTE WHERE AlertID = c.AlertID AND AlertType = c.AlertTypeAND Datetype = c.datetypeAND MetricId = c.MetricIdAND AreaID = c.AreaIDAND value1 = c.value1AND value2 = c.value2AND Active = c.ActiveAND CreatedBy = c.CreatedBy AND CreatedDate = c.CreatedDate ORDER BY pagroupname FOR XML PATH('')),1,1,'') AS pagroupnameFROM (SELECT DISTINCT AlertID, AlertType, Datetype, MetricId, AreaID, value1, value2, Active, CreatedBy, CreatedDateFROM CTE) c |
|
|
mgreen84
Yak Posting Veteran
94 Posts |
Posted - 2013-03-28 : 14:31:42
|
Thanks James K and visakh16, I appreciate the help and learning. I'll admit I have not used CTE's much, but have used derived tables on several occasions. Just never really grasped the concept of CTE's, always got confusing when I tried using. But this gives me something to play around with now and practice with. Once again many thanks. |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-03-28 : 15:33:36
|
You are quite welcome.In this context, you can think of the cte as a view or subquery (aliased to "cte").There are also recursive CTEs, which are somewhat more interesting and are very useful - there is a description and a nice example here: http://msdn.microsoft.com/en-us/library/ms186243(v=sql.105).aspx Before you delve into that, read about the ordinary kind of CTE's (which is what was used in the query that Visakh posted). |
|
|
mgreen84
Yak Posting Veteran
94 Posts |
Posted - 2013-04-02 : 12:00:39
|
Thanks! I will definitely make use of this.quote: Originally posted by James K You are quite welcome.In this context, you can think of the cte as a view or subquery (aliased to "cte").There are also recursive CTEs, which are somewhat more interesting and are very useful - there is a description and a nice example here: http://msdn.microsoft.com/en-us/library/ms186243(v=sql.105).aspx Before you delve into that, read about the ordinary kind of CTE's (which is what was used in the query that Visakh posted).
|
|
|
|
|
|
|
|