How To Do A Case-Sensitive Pivot Table
Hi all,
I've been able to do a pivot table just fine. But some of the data I'm pivoting on is case-sensitive. For example, "A" and "a" are 2 different pieces of data for my purposes. I just cannot seem to get this pivot table to break that out.
In the PIVOT statement in the SQL below, I'm using "for Disposition in ([A], [S], [T], [N], [Y])" If I use the commented-out line above it instead, "for Disposition in ([A], [a], [S], [s], [T], [t], [N], [Y], [y]) " which breaks out the pivot values into their case-sensitive counterparts, I get an error "Msg 8156, Level 16, State 1, Line 25 The column 'a' was specified multiple times for 'PivotTable'."
I know how to search on case-sensitive data using a different collation name like "SQL_Latin1_General_Cp1_CS
In my example data, one patient can have many packages. Each patient + package can have many "dispositions." Think of it as a status field. We have a number of different status values (A, a, S, s, N, etc).
In my SQL code, there is a reference to a third table called Customer.MedicationPackage
Can anyone point me in the right direction?
Thanks,
John
I've been able to do a pivot table just fine. But some of the data I'm pivoting on is case-sensitive. For example, "A" and "a" are 2 different pieces of data for my purposes. I just cannot seem to get this pivot table to break that out.
In the PIVOT statement in the SQL below, I'm using "for Disposition in ([A], [S], [T], [N], [Y])" If I use the commented-out line above it instead, "for Disposition in ([A], [a], [S], [s], [T], [t], [N], [Y], [y]) " which breaks out the pivot values into their case-sensitive counterparts, I get an error "Msg 8156, Level 16, State 1, Line 25 The column 'a' was specified multiple times for 'PivotTable'."
I know how to search on case-sensitive data using a different collation name like "SQL_Latin1_General_Cp1_CS
In my example data, one patient can have many packages. Each patient + package can have many "dispositions." Think of it as a status field. We have a number of different status values (A, a, S, s, N, etc).
In my SQL code, there is a reference to a third table called Customer.MedicationPackage
Can anyone point me in the right direction?
Thanks,
John
Data Example:
PatientID PackageID Disposition DispCount
10065 236 A 1
10065 236 a 1
10065 236 a 1
10065 236 A 1
10065 236 a 1
10065 236 a 1
10065 236 a 1
10065 238 a 1
10065 238 a 1
10065 238 A 1
10065 238 N 1
10065 238 a 1
10065 238 s 1
10065 238 a 1
10065 238 a 1
SQL:
declare @TrialID as int
set @TrialID = 14;
WITH TempMedEventResultsTable(PatientID, PackageID, Disposition, DispCount)
AS
(
SELECT v.ePatientId,
sepr.PackageID,
sepr.disposition,
1
FROM Customer.MedicationPackage INNER JOIN
Device.ScheduledEventPackageResults AS sepr ON Customer.MedicationPackage.PackageId = sepr.PackageID RIGHT OUTER JOIN
Customer.vw_PatientsByTrial AS v ON sepr.PatientId = v.ePatientId
WHERE (v.eTrialId = @TrialID)
)
select *
from TempMedEventResultsTable
PIVOT
(
Count(DispCount)
--for Disposition in ([A], [a], [S], [s], [T], [t], [N], [Y], [y])
for Disposition in ([A], [S], [T], [N], [Y])
) as PivotTable
Order By PatientID, PackageID
You should be able to use the COLLATE in-line as you would normally. Don't believe you need to change the database, but if you truly are storing data in different case and want it to mean different things then you probably do want that column at a minimum to be in that COLLATION and maybe changing the database won't hurt. Just giving my opinion if you need the database to remain CI but the pivot to be CS.
Hi Kevin, I thought about just changing the collation of the column, but I'm not sure how SQL will handle then a column name "A" and a column name "a" if the database is case insensitive. That's why I suggested changing the whole database collation. Hope you agree with me.
Maybe the table collation is all that needs to be changed.
alter table collate ....
But again, I would be play safe and turn the whole database case sensitive.
alter table collate ....
But again, I would be play safe and turn the whole database case sensitive.
You are probably right. Thought could use the COLLATE SQL_Latin1_General_Cp1_CS_
And just woke up. Pivoting will turn the values into column names so it doesn't matter if the table data is case sensitive or not, it will be if "A" and "a" will be treated as two different column names which case sensitivity at least of the table / column won't matter there. Try ralmada's alter of database, but suspecting it will fail since you have duplicate column names.
Thanks Kevin for testing!
Hope the alter database do the trick.
Hope the alter database do the trick.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You are most welcome. Should have figured you to be right all along. :)
ASKER
Thanks for the quick responses!
But one thing that didn't dawn on me until I saw it in a few replies...I'm not going to be changing the collation setting on the entire DB.
This is a working, production DB so I don't want to change anything globally about it at this time.
I'm trying to solve this in the scope of this script/proc if at all possible.
But one thing that didn't dawn on me until I saw it in a few replies...I'm not going to be changing the collation setting on the entire DB.
This is a working, production DB so I don't want to change anything globally about it at this time.
I'm trying to solve this in the scope of this script/proc if at all possible.
Then see my post here -- http:#a25874399 -- sounds like you will need the second approach as it doesn't alter your table structure / collation either. It would mean manually pivoting.
You could basically name the columns by what their full description is.
On that thought, if you wanted to use the neatness of PIVOT, then you could add a JOIN to your query that is to a lookup table that has the status and converts it to the long description. You could do the JOIN using the COLLATE SQL_Latin1_General_Cp1_CS_
Kevin
You could basically name the columns by what their full description is.
On that thought, if you wanted to use the neatness of PIVOT, then you could add a JOIN to your query that is to a lookup table that has the status and converts it to the long description. You could do the JOIN using the COLLATE SQL_Latin1_General_Cp1_CS_
Kevin
trzesniakj, If you can't change the database collation, then you will have to go with Kevin's approach. As you can see you will definitively need to change the column name. So you can't have a column called "A" and another one called "a". Now I have a question for you since you are already changing the column name, will this work for you? (I'm using the ascii value of the characters instead)
WITH TempMedEventResultsTable(PatientID, PackageID, Disposition, DispCount)
AS
(
SELECT v.ePatientId,
sepr.PackageID,
ascii(sepr.disposition COLLATE SQL_Latin1_General_Cp1_CS_AS) disp1,
1
FROM Customer.MedicationPackage INNER JOIN
Device.ScheduledEventPackageResults AS sepr ON Customer.MedicationPackage.PackageId = sepr.PackageID RIGHT OUTER JOIN
Customer.vw_PatientsByTrial AS v ON sepr.PatientId = v.ePatientId
WHERE (v.eTrialId = @TrialID)
)
select *
from TempMedEventResultsTable
PIVOT
(
Count(DispCount)
for disp1 in ([65], [97], ....)
) as PivotTable
Order By PatientID, PackageID
ASKER
I tried Kevin's method and it worked relatively well. Would still like to do it as a pivot table though.
One thing I did not think of which you touched on here ralmada was the possibility of changing the collation of only the Disposition column on the table? What would the effect be? Next week when I'm back in the office, I think I'm going to try that on a test DB I have and see what it does, if anything.
But I have to hand it to you...a very creative solution using ASCII values of the dispositions to pivot on!
Thanks to all for all the brain power applied to this problem!
One thing I did not think of which you touched on here ralmada was the possibility of changing the collation of only the Disposition column on the table? What would the effect be? Next week when I'm back in the office, I think I'm going to try that on a test DB I have and see what it does, if anything.
But I have to hand it to you...a very creative solution using ASCII values of the dispositions to pivot on!
Thanks to all for all the brain power applied to this problem!
Note that changing from CI to CS separate from the SQL Server itself can cause issues if you have multiple databases on the server.
I ran into an issue when I did a consulting gig. The acctg db was CS, the server was CI. The upgrade failed the first time after a 13 hour run. Changing the DB to CI, the upgrade finished in 3.5 hours.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
KOK. I was able to use the solution from mwvisa1 for my needs. Our disposition column has a small set of distinct values so this will work for our needs even though I know the better and more generic solution is to change the DB's collation as was suggested and tested very thoroughly by ralmada.
So I thank you both for the thorough and quick job you did on the issue. Since mwvisa1 gave me the solution for my issue, I'm awarding 350 points to him and the rest to ralmada.
So I thank you both for the thorough and quick job you did on the issue. Since mwvisa1 gave me the solution for my issue, I'm awarding 350 points to him and the rest to ralmada.
Open in new window