Hello all,
I have the following temporary table structure which I already
populated with a regular query to the main database:
CREATE TABLE #MESVENPAR2 (
CONTAID BIGINT, -- ID of Partner
CONNAME VARCHAR(100), -- Name of Partner
CONZONE VARCHAR(50), -- Geaographical Zone of Partner
PRODCAT BIGINT, -- Product Category Code
TOTAL BIGINT -- Total Products
)
Basically, the table contains the total of sales per product category
for each partner. Were there an index, it would be a composite one
between CONTAID and PRODCAT
Now...
I need to query this data into a matrix. The structure would be:
IDPART | PARTNAME | PARTZONE VARCHAR(50) | CAT_1 | CAT_2 | ...
Where CAT_1, CAT_2, and so forth, refer to the product category code
prefixed by 'CAT_'. Each line would thus contain the partner id, its
name and zone, and either 0 or the value of TOTAL for each product
category.
How can I make this?
TIAhttp://www.aspfaq.com/show.asp?id=2462
David Portas
SQL Server MVP
--|||On 7 Mar 2005 02:57:42 -0800, Marfig wrote:
(snip)
>Basically, the table contains the total of sales per product category
>for each partner. Were there an index, it would be a composite one
>between CONTAID and PRODCAT
Hi Marfig,
What's the reason for not actually declaring this combination as the
primary key in the tebporary table? Inquiring mind wants to know...
>Now...
>I need to query this data into a matrix. The structure would be:
>IDPART | PARTNAME | PARTZONE VARCHAR(50) | CAT_1 | CAT_2 | ...
This is called a crosstab (or pivot) operation. Several useful links can
be found here: http://www.aspfaq.com/show.asp?id=2462
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks both.
It's a somewhat small table, Hugo. Not worth at all of an index. In
fact, I will rather prefer to have it done without a temporary table.
With your help, I'll be able to come up with a more elegant solution.
Thanks again.
Friday, March 9, 2012
Matrix building difficulties
Labels:
alreadypopulated,
building,
database,
databasecreate,
difficulties,
following,
matrix,
mesvenpar2,
microsoft,
mysql,
oracle,
query,
regular,
server,
sql,
structure,
table,
temporary
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment