May 26, 2015 at 6:12 pm
Hi,
I am using a crosstab query that uses an aggregate but is failing for some reason that I think is to do with the aggregate, but cannot find an example online that does not use an aggregate!
Here is table called TblData
PatientIDDrs Name RowNo
277Nurse Nxxxx Cxxxx1
277Registrar Dr Hxxxxx2
362Registrar Dr Hxxxxx1
372Registrar Dr Hxxxxx1
476Registrar Dr Hxxxxx1
606Registrar Dr Hxxxxx1
607Registrar Dr Hxxxxx1
1125Dr. xx Mxxxxxxxxxx1
1125Registrar Dr Hxxxxx2
1516Dr. xx Mxxxxxxxxxx1
1516Registrar Dr Hxxxxx2
1540Dr. xx Mxxxxxxxxxx1
1540Registrar Dr Hxxxxx2
1542Registrar Dr Hxxxxx1
1545Registrar Dr Hxxxxx1
1571Registrar Dr Hxxxxxx1
1707CLL xxxxxx Oxxxxe1
1707Registrar Dr Hxxxxx2
Does anyone solve how to solve the problem please?
SP Code is as follows;
WITH PTherapists
AS(
SELECT PT.PatientID, T.Name, DENSE_RANK()OVER(Partition BY PT.PatientId Order By T.Name) AS RowNo
FROM TblData PT LEFT OUTER JOIN TblTherapists T ON PT.TherapistID= T.ID
WHERE PT.PatientID IN (SELECT PatientID from TblPatientTherapists WHERE TherapistID = 4)
)
SELECT @SQLStr = COALESCE(@SQLStr + ',', '') + [a].[Column]
FROM (SELECT DISTINCT CAST(RowNo AS NVARCHAR) AS [Column] FROM PTherapists) AS a
SET @SQLStr = 'SELECT PatientID, ' + @SQLStr
+ ' FROM (Select PatientID, Name, RowNo FROM PTherapists) sq '
+ ' PIVOT ((Name) FOR RowNo IN ('+ @SQLStr + ')) AS pt'
EXEC sp_executesql @SQLStr;
May 27, 2015 at 9:20 am
To give you the best help we'll need sample data from the underlying tables and the expected results from the query. It looks like what you've posted here is the query and its current results (since RowNo is an expression from the query and appears in the data you posted).
If you add some sample data for the underlying tables in form of DDL statements (see http://www.sqlservercentral.com/articles/Best+Practices/61537/) along with the results you're expecting, we should be able to help you out more effectively.
Cheers!
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply