Pivot Table Without Numbers 1

[grnzbra]

Using code for a pivot table, I can set up a pivot table to turn the first table into the second table in the first pair below.
What I need is to turn the first table of the second pair into the second table of the second pair. Can it be done with a pivot table or is there another way of doing it directly without resorting to the use of a cursor to loop through all the records.

 

[SkipVought]

Hi,

Plz post data we can copy n paste rather than a picture.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[grnzbra]

How do I do that. I tried copying and pasting from the Excel file and, while it appeared usable in the edit window, in the preview window all the fields collapsed to one space between.
Is there any kind of table insert functionality that I can use?

 

[SkipVought]

I went ahead and did the grunt work to set up your pivot table and the second table of SalesAccounts.

If you can generate this...
[Pre]
SalesPerson Shop WK AN SA BW
AirConditioner HVAC
WaterPump HVAC
Elevator HVAC
[/pre]

...then I have the formula to produce this
[Pre]
SalesPerson Shop WK AN SA BW
AirConditioner HVAC AC10WK AC10AN
WaterPump HVAC WP4SA
Elevator HVAC EL1BW
[/pre]

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[Andrzejek]

grnzbra,

You can either do what Skip did:[blue]
[ignore][PRE][/ignore][/blue]
[PRE]
SalesPerson Shop WK AN SA BW
AirConditioner HVAC
WaterPump HVAC
Elevator HVAC [/PRE][blue][ignore][/PRE][/ignore][/blue]

You can get the same outcome by selecting this tag:

or attach en Excel file to your post/reply



---- Andy

There is a great need for a sarcasm font.

 

[SkipVought]

Actually, you don't need a Pivot Table. Just one table from your database for DISTINCT occurrences of...
[tt]
SalesPerson
Shop
Product
SalesAcct
[/tt]

I'm assuming that the values you want filled in are some sort of account id.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[SkipVought]

Here's the formula.
[tt]
=IFERROR(INDEX(Table2[SalesAcct],SUMPRODUCT((Table2[SalesPerson]=$F2)*(Table2[Shop]=$G2)*(Table2[Product]=H$1)*(ROW(Table2[SalesAcct])))-1),"")
[/tt]

[pre]
=IFERROR(
INDEX(
Table2[SalesAcct],
SUMPRODUCT(
(Table2[SalesPerson]=$F2)*
(Table2[Shop]=$G2)*
(Table2[Product]=H$1)*
(ROW(Table2[SalesAcct]))
)-1
),
""
)
[/pre]

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[grnzbra]

Thank you very much for your assistance. However I am trying to do this in a SQL Server stored procedure.

Each record in the first table is already unique. What needs to happen is exemplified in the Air Conditioner records. We have two time frames (Product) weekly and annually. In the starting table these appear under the SalesAmount column. These two records must appear as one record in the result with the annual code in the "AN" column and the weekly code in the "WK" column. Also, the placement has nothing to do with the code itself other than the last two characters - we could just as easily have a third air conditioner value of RAC3BW that would have to be included in the one resulting record.
I'm beginning to think that this can't be done using a crosstab query (Google results for a search on crosstab seems to refer to crosstab query and pivot table interchangably). If there is another way to do it other than looping through the records it would be great but I am trying to avoid that.

 

[SkipVought]

So you're saying that in the SalesAmount column you have both numeric and text values?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[grnzbra]

No. The field names here are based on the first pair of tables, which were based on an example of a crosstab query I found at mssqltips.com. I added the SHOP column because I wanted to see if it presented any problems. When I used numbers instead of the codes the query worked. I then continued with the same headers but added the codes that are actually appearing. The actual tables with correct headers would be:

[pre]TemplateName Freq ChklistCode
AirConditioner WK AC10WK
AirConditioner AN AC10AN
WaterPump SA WP5SA
Elevator BW EL1BW
HeatPump AN AC10AN

TemplateName BW SA AN WK
AirConditioner AC10AN AC10WK
Elevator EL1BW
HeatPump AC10AN
WaterPump WP5SA
[/pre]
For simplicity, I've removed the Shop field (if necessary it could be easily added back later) and used blank spaces instead of NULL in the result.

 

[SkipVought]

So this seems to describe maintenance frequencies of various systems: weekly, bi-weekly, monthly, quarterly, semi-annually, annually, bi-annually....

Is this something that changes often, so that you would need to run this query periodically as systems are added to the database?

Otherwise, this is something that you may need to run once or twice a year or less. So why not in Excel.

You do know that you can query your SQL Server db right from Excel, yes? The frequencies don't change, just the systems to maintain, yes?

Heck, it's a solution that can work with a single query to your db, generating a variable number of rows and the formula would automatically adjust to greater or fewer rows!

Acts just like a stored procedure.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[grnzbra]

Unfortunately, it must be available on demand to load a SQL Server and be entirely contained within SQL Server. The schema doesn't change, but the data can. I guess I'll just have to loop through the records.
Again, thank you very much for the help.

 

[SkipVought]

Fair enough.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein