addcommmand to inverse data from vertical to horizontal

[hlmnsbuggy]

crystal 11 and 2008. odbc
I have a policy table data layout as following

policy premium coverage
pol1 300 dwelling
pol1 100 content
pol1 100 liability

What sql I can use in addcommand to inverse the data from vertical to horizontal

policy dwelling content liability
pol1 300 100 100

I can't use crosstab due to row limits. Please be detailed with your SQL codes since I am a beginner for this

Thank you for your help!!

Ann

 

[lbass]

Please explain

I can't use crosstab due to row limits.

A crosstab is the logical solution, whether inserted or manual.

-LB

 

[IanWaterman]

Not sure of the SQL syntax for 2008.odbc (is that a database type?)

Provided you know all the Coverage types, you can use a case statement.

Select PolicyNo,
Sum(case when coverage = 'dwelling' then premium else 0 end) Dwelling,
Sum(case when coverage = 'content' then premium else 0 end) Content,
Sum(case when coverage = 'Liability' then premium else 0 end) Liability
From Table

Ian