unique last name - 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

General SQL Server Forums

New to SQL Server Programming

unique last name

Author

Topic

TalleyOuro
Starting Member

1 Post

Posted - 2014-03-18 : 15:24:51

I have this view that i use for report.The view definition is below.
SELECT ct.LastName, ih.InspectionID, er.Enrollment AS HcStatus, ISNULL(ih.Compliant, 'No') AS Compliant, ih.Year, ih.Season, CAST(ih.Season + CAST(ih.Year AS varchar(20))
AS varchar(20)) AS Combined, CONVERT(varchar(20), ih.InspectionDate, 101) AS InspectionDate, ih.OperationID AS AnimalBusinessID
FROM dbo.InspectionsHistory AS ih INNER JOIN
(SELECT OperationID, Year, Season, MAX(InspectionDate) AS InspectionDate
FROM dbo.InspectionsHistory
GROUP BY OperationID, Year, Season) AS f ON ih.OperationID = f.OperationID AND ih.Year = f.Year AND ih.Season = f.Season AND
ih.InspectionDate = f.InspectionDate INNER JOIN
dbo.AnimalBusinesses AS ab ON ih.OperationID = ab.AnimalBusinessID INNER JOIN
dbo.Enrollments AS er ON ab.AnimalBusinessID = er.AnimalBusinessID INNER JOIN
dbo.Contacts AS ct ON ab.ContactID = ct.ContactID
WHERE (ab.AnimalClassification = 'CERVID')

I wrote this store procedure the generate a report
DECLARE @cols AS NVARCHAR(MAX),@cols2 AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)

select @cols = stuff(( select '],[' + Combined
from vCervidInspections group by Combined
for xml path('')), 1, 2, '') + ']'

select @cols2 = stuff(( select ', ISnull(Cast([' + Combined +'] as Varchar(20)),'+''''+ 'N/A'+''''+') '+Combined+', (select IsNull(max(Compliant),'+''''+'N/A'+''''+ ') Compliant from vCervidInspections where Combined=' +''''+ Combined + '''' + ' and InspectionDate='+Combined + ' and HcStatus=pvt.HcStatus and LastName=pvt.LastName) Compliant'
from vCervidInspections group by Combined
for xml path('')), 1, 2, '')

set @query = 'SELECT LastName,HcStatus, '
+ @cols2 +'
from
(
Select LastName,HcStatus,Compliant, Combined,
InspectionDate,
cast(InspectionDate as dateTIME) dateTIME,
row_number() over(partition by LastName, Combined, cast(InspectionDate as dateTIME)
order by InspectionDate) rn
from vCervidInspections
group by LastName,HcStatus,Compliant, Combined,InspectionDate
)p
pivot
(
min(InspectionDate)
For Combined in (' + @cols + ')
)pvt order by LastName asc'

execute(@query)

I am getting this result showing multiples last name per row.
http://imageshack.com/a/img208/2444/bdho.png

But my goal is to have only 1 last name per row .For ALMOND i want something like this:
http://imageshack.com/a/img834/2082/6e5f.png

Talley Ouro

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources