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.
| Author |
Topic |
|
nic
Posting Yak Master
209 Posts |
 Posted - 2003-03-27 : 19:44:27
|
| Is there a way you can create a pivot table with a varchar? Normally in a pivot table you sum the values to get the total but what happens if that value is a varchar? In the below example, CoverageItem is a varchar. Any ideas?? SELECT Coverage AS 'Coverage' ,SUM(CASE WHEN ItemNumber =1 THEN CoverageItem ELSE null END) AS 'item1' ,SUM(CASE WHEN ItemNumber =2 THEN CoverageItem ELSE null END) AS 'item2' FROM QuotePremiums GROUP BY CoverageNic |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-03-27 : 20:48:25
|
| Just use the MAX() function ...YOu'll get a warning, NULL ignored in aggregate, but all will be well. If you want to avoid that warning, just set the alternate value to something guaranteed to be the MIN, such as ' '.SELECT Coverage AS 'Coverage' ,MAX(CASE WHEN ItemNumber =1 THEN CoverageItem ELSE ' ' END) AS 'item1' ,MAX(CASE WHEN ItemNumber =2 THEN CoverageItem ELSE ' ' END) AS 'item2' FROM QuotePremiums GROUP BY Coverage- Jeff |
 |
|
|
|
|
|
create a pivot table with a varchar?