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)
 How to merge two fields into one

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 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
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, 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

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, 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

52326 Posts

Posted - 2013-03-28 : 12:17:28
[code]
;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
[/code]

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

Go to Top of Page

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 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
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 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

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

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

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).

Go to Top of Page
   

- Advertisement -