Concatenating in a group by - SQL Server Forums

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

Transact-SQL (2005)

Concatenating in a group by

Author

Topic

Trudye
Posting Yak Master

169 Posts

Posted - 2008-04-22 : 13:07:50

Hi Guys, I am converting Access SQL to SQL Server and I have two problems:
First of all I can’t get the city, state and zip to concatenate. It will not take the "As Location" and it will not allow me to concatenate the fields in the Group By area.
Second I ‘m trying to replace a First() function with a Top() function but I’m not having much success. If it is not the first statement I get an error (Incorrect syntax near the keyword 'TOP'.)

Any ideas how I can accomplish what it is I want to do?

Thanx much,
Trudye

BEGIN

SELECT Date AS DateEntered,
LTRIM(RTrim([ResultsCustName])) AS CustName,
Timestamp, LTRIM(RTrim([ResultsPH])) AS Phone,
TOP (1) tblEscalationData.Explain_followup AS FirstOfExplain_followup,
[ResultsAddr1] AS Address1, [ResultsAddr2] AS Address2,
[ResultsCity] AS City, [ResultsST] AS [State], [ResultsZip] AS Zip,
ERROR
INTO zTmp_Training
FROM tblEscalationData
LEFT JOIN qry_IssuesPrint
ON tblEData.ResultsPKey = qry_IssuesPrint.ResultsPKey
LEFT JOIN LOOKUP_DATA
ON tblEData.DSLOAN = LOOKUP_DATA.DSLOAN
LEFT JOIN qry_IssuesPrintDef
ON tblEData.ResultsPKey = qry_IssuesPrintDef.ResultsPKey
INNER JOIN qry_CurrentStatus
ON tblEData.ResultsPKey = qry_CurrentStatus.ResultsPKey
LEFT JOIN tblErrors
ON Error = SUBID
WHERE tblEData.CSRReporting= 1 AND
qry_CurrentStatus.Date
Between @Start_Date And @End_Date
AND Source = @CallCtr
AND ERROR Is Not Null
ORDER BY LTrim(RTrim([ResultsCustName]))

END
--qry_CSRTraining (Group BY Clause)
BEGIN
SELECT DateEntered, LTrim(RTrim([CustName])), [Timestamp],
LTrim(RTrim([Phone])),
[Address1] + ' ' [Address2],
[City] + ' ' + [State] + ' ' + [Zip] As Location,
[Name], ERROR
FROM zTmp_Training
GROUP BY DateEntered, [CustName], [Timestamp],
[Phone], [Address1], [Address2], Location,
[Name], ERROR
END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-22 : 13:23:25

You cant put TOP1 inbetween fields in SELECT list. you can select TOP 1 of entire list but you cannot apply it for only one of the fields. For getting top 1 of a particular field alone, you need to use a subquery and join main query with that.
You dont require aliases in GROUP BY. you just need to concatenate the fields in group by. So remove the AS alias part.
If you want complete solution, post your full requirement with business rules,DDL of tables and some sample data along with sample o/p you desire.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2008-04-25 : 04:22:45

instead of a TOP use MAX then add a group by...

remember to group by unique fields you've selected in your first query
otherwise do a subselect in your first query to retrieve the TOP1 explain_followUp

you can remove your 2nd query if it's not needed since you can concatenate the location details in the first query

--------------------
keeping it simple...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources