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)
 How to merge two fields into one
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mgreen84
Yak Posting Veteran

62 Posts

Posted - 03/27/2013 :  18:56:33  Show Profile  Reply with Quote
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  CreatedDate
10	  2	      D	        71	   833	       NE	 2	 3	 1	9838011	   2013-03-27 00:00:00.000
10	  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  CreatedDate
10	 2	    D	       71	  833,1028     NE	 2	 3	 1	9838011	   2013-03-27 00:00:00.000


Any help would be greatly appreciated.
Thanks



My 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
Flowing Fount of Yak Knowledge

3562 Posts

Posted - 03/27/2013 :  20:08:03  Show Profile  Reply with Quote
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, 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
group by
	AlertID, AlertType, Datetype, MetricId,
	al.AreaID, value1, value2, al.Active, CreatedBy, CreatedDate
Go to Top of Page

mgreen84
Yak Posting Veteran

62 Posts

Posted - 03/28/2013 :  11:31:17  Show Profile  Reply with Quote
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, 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
group by
	AlertID, AlertType, Datetype, MetricId,
	al.AreaID, value1, value2, al.Active, CreatedBy, CreatedDate


Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 03/28/2013 :  12:17:28  Show Profile  Reply with Quote

;With CTE
AS
(
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
)

SELECT c.*,STUFF((SELECT ',' + CAST(PAGroupID AS varchar(10)) 
FROM CTE 
WHERE AlertID = c.AlertID 
AND AlertType = c.AlertType
AND Datetype = c.datetype
AND  MetricId = c.MetricId
AND AreaID = c.AreaID
AND value1 = c.value1
AND value2 = c.value2
AND Active = c.Active
AND  CreatedBy = c.CreatedBy 
AND CreatedDate = c.CreatedDate 
ORDER BY PAGroupID 
FOR XML PATH('')),1,1,'') AS PAGroupID
FROM (SELECT DISTINCT AlertID, AlertType, Datetype, MetricId, AreaID, value1, value2, Active, CreatedBy, CreatedDate
FROM CTE) c


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

mgreen84
Yak Posting Veteran

62 Posts

Posted - 03/28/2013 :  13:21:34  Show Profile  Reply with Quote
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 format

quote:
Originally posted by visakh16


;With CTE
AS
(
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
)

SELECT c.*,STUFF((SELECT ',' + CAST(PAGroupID AS varchar(10)) 
FROM CTE 
WHERE AlertID = c.AlertID 
AND AlertType = c.AlertType
AND Datetype = c.datetype
AND  MetricId = c.MetricId
AND AreaID = c.AreaID
AND value1 = c.value1
AND value2 = c.value2
AND Active = c.Active
AND  CreatedBy = c.CreatedBy 
AND CreatedDate = c.CreatedDate 
ORDER BY PAGroupID 
FOR XML PATH('')),1,1,'') AS PAGroupID
FROM (SELECT DISTINCT AlertID, AlertType, Datetype, MetricId, AreaID, value1, value2, Active, CreatedBy, CreatedDate
FROM CTE) c


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/



Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3562 Posts

Posted - 03/28/2013 :  14:20:05  Show Profile  Reply with Quote
You can use the same pattern as what Visakh used for the PAGroupID
;With CTE
AS
(
SELECT AlertID, AlertType, Datetype, MetricId, PAGroupID, al.AreaID, value1, value2, al.Active, CreatedBy, CreatedDate,
pagroupname

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
)

SELECT c.*,STUFF((SELECT ',' + CAST(PAGroupID AS varchar(10)) 
FROM CTE 
WHERE AlertID = c.AlertID 
AND AlertType = c.AlertType
AND Datetype = c.datetype
AND  MetricId = c.MetricId
AND AreaID = c.AreaID
AND value1 = c.value1
AND value2 = c.value2
AND Active = c.Active
AND  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.AlertType
AND Datetype = c.datetype
AND  MetricId = c.MetricId
AND AreaID = c.AreaID
AND value1 = c.value1
AND value2 = c.value2
AND Active = c.Active
AND  CreatedBy = c.CreatedBy 
AND CreatedDate = c.CreatedDate 
ORDER BY pagroupname 
FOR XML PATH('')),1,1,'') AS pagroupname
FROM (SELECT DISTINCT AlertID, AlertType, Datetype, MetricId, AreaID, value1, value2, Active, CreatedBy, CreatedDate
FROM CTE) c
Go to Top of Page

mgreen84
Yak Posting Veteran

62 Posts

Posted - 03/28/2013 :  14:31:42  Show Profile  Reply with Quote
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.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3562 Posts

Posted - 03/28/2013 :  15:33:36  Show Profile  Reply with Quote
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).
Go to Top of Page

mgreen84
Yak Posting Veteran

62 Posts

Posted - 04/02/2013 :  12:00:39  Show Profile  Reply with Quote
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).

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.08 seconds. Powered By: Snitz Forums 2000