Post reply

Crosstab/pivot not working

  • May 26, 2015 at 6:12 pm

    #317743

    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

    PatientID Drs Name RowNo

    277 Nurse Nxxxx Cxxxx 1

    277 Registrar Dr Hxxxxx 2

    362 Registrar Dr Hxxxxx 1

    372 Registrar Dr Hxxxxx 1

    476 Registrar Dr Hxxxxx 1

    606 Registrar Dr Hxxxxx 1

    607 Registrar Dr Hxxxxx 1

    1125 Dr. xx Mxxxxxxxxxx 1

    1125 Registrar Dr Hxxxxx 2

    1516 Dr. xx Mxxxxxxxxxx 1

    1516 Registrar Dr Hxxxxx 2

    1540 Dr. xx Mxxxxxxxxxx 1

    1540 Registrar Dr Hxxxxx 2

    1542 Registrar Dr Hxxxxx 1

    1545 Registrar Dr Hxxxxx 1

    1571 Registrar Dr Hxxxxxx 1

    1707 CLL xxxxxx Oxxxxe 1

    1707 Registrar Dr Hxxxxx 2

    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

    #1800981

    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://qa.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