Pivoting Data then grouping by max values 1

[WalterHeisenberg]

Hello,

I am trying to write a crystal report and working in a very goofy database. The issue I've run into is that I need to pivot out the results of rows into distinct columns.

So the field I'm working with, WDLINESEQ has values of 1,2,3,4 etc. If it is a 1 it needs to be in column web, if it is 2 it needs to be in column tv etc.

The problem is table A has 1 row and has a FK that exists in every row of table 2. I don't see any other way to join these tables so I'm stuck with if table B has 6 results it shows up 6 times in the detail section with 1 column having a value and the rest being null.

In SQL I would solve this by pivoting out the data in a subquery and using that as my from in new query that selected the standard rows and max() of the pivoted fields grouping on the standard rows. Not sure how to do this in Crystal.

I've attached a sample of a SQL query that achieves the desired result.

SELECT TXSITE, TXCFOV, NMCFOC, MAX(sev) as Web, MAX(twlv) as TV
FROM
(
select s.TXSITE, s.TXCFOV, s.NMCFOC
,case  when WDLINESEQ = 7 then '1' end as sev
,case  when WDLINESEQ = 12 then '1' end as twlv
from VMSITE s
join VMCFOC c on c.TXCFOV = s.TXCFOV and s.NMCFOC = c.NMCFOC
where s.TXSITE = '1100') X

Group by TXSITE, TXCFOV, NMCFOC

The result returned by this query is:

 TXSITE,TXCFOV,NMCFOC,Web,TV
1100        ,CF1   ,1059,1,1

I can't use something like SUM(CASE because these conversions will only have 1 field that is populated with an actual text value I will be returning. All 6 fields will be populated. I hope I explained this well. Thanks in advance.

 

[lbass]

You should be able to create a query like this in database->add command.

-LB

 

[WalterHeisenberg]

I am using CR 11 PRO and did not have an option to add a command under database. Closest thing was SHOW SQL QUERY but I could not edit it. Thanks - A

 

[lbass]

I should have said: database->database expert->your datasource->add command (above the list of tables).

-LB

 

[HowardHammerman]

You can also do this without using SQL.
Simply suppress the details section and x number of running totals, one for every possible value of your field. When creating the running totals, use the option to accumulate on a formula and enter the logic for each column. EG: if {table.field}="1" then <<value>>

Substitute the field you want to show for <<value>>

Howard Hammerman,
Crystal Training and Crystal Material
On-site classes and one-on-one coaching
Low-cost telephone/email support
FREE independent Crystal newsletter
howard@hammerman.com

http://www.hammerman.com

800-783-2269

 

[WalterHeisenberg]

I've been using Crystal for awhile now but over the past 3 years had transitioned to SQL for my reporting needs whenever possible. The fact you literally can add a SQL command and have it appear as a joinable table in Crystal has just made my life SO much easier.

Thank you very very much LB!