updatable pivot table? - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

updatable pivot table?

Author

Topic

cidr2
Starting Member

28 Posts

Posted - 2009-02-27 : 05:56:50

Hi there,

OK, I believe there probably wont be a way to do this. I have a database with a table and two of the columns have only a set amount of data. one row has 4 consistant values (for Brevity, I'll just make them fruit - Oranges, Apples, Pears and Bananas) the other column, again, has a consistant amont of values (SuperMart, JollyGrocers, FruitMans)

There is another column that holds costs so the table would look like this in the db

Fruit-----------Market---------Cost
Apples--------SuperMart---------$1
Bananas-----SuperMart---------$2
Oranges------SuperMart---------$3
Pears----------SuperMart---------$1
Apples--------JollyGrocers------$3
Bananas-----JollyGrocers------$2
Oranges-----JollyGrocers------$2

I'd like to be able to produce the results like below. The reason for this is so that it facilitates the frontend design(which is InfoPath). Below is how I need it to be laid out in the forms.

The frontend forms have to be able to submit values to the database, therefore it has to be updatable in anywhich way. :

Fruit------SuperMart-----JollyGrocers-----FruitMans
Apples--------$1----------------$2-----------------$3
Banans-------$2----------------$3-----------------$3
Oranges------$3----------------$1-----------------$7
Pears----------$1----------------$3-----------------£2

The fruit and the Market values are consistant and will hardly(if ever) expand. So really it's the cost column values that are always being added to.

I've been advised that SQL analysis services could do this?

Is this possible? is an updatable Pivot table possible?

If anyone has a solution, I'd be gratful.

Cheers

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-27 : 07:20:07

see this sample example


CREATE TABLE Pivot_SampleEx
(
	YEAR      SMALLINT,
	QUARTER    TINYINT,
	AMOUNT	  DECIMAL(2,1)
)

INSERT INTO Pivot_SampleEx VALUES (1990,1,1.1)
INSERT INTO Pivot_SampleEx VALUES (1990,2,1.2)
INSERT INTO Pivot_SampleEx VALUES (1990,3,1.3)
INSERT INTO Pivot_SampleEx VALUES (1990,4,1.4)
INSERT INTO Pivot_SampleEx VALUES (1991,1,2.1)
INSERT INTO Pivot_SampleEx VALUES (1991,2,2.2)
INSERT INTO Pivot_SampleEx VALUES (1991,3,2.3)
INSERT INTO Pivot_SampleEx VALUES (1991,4,2.4)
INSERT INTO PIVOT_SampleEx VALUES (2002,3,5.2)
INSERT INTO PIVOT_SampleEx VALUES (2002,4,5.4)

-- using pivot function
SELECT YEAR,[1] AS Q1,[2] AS Q2,[3] AS Q3,[4] AS Q4
FROM
	(SELECT * FROM Pivot_SampleEx) AS P
PIVOT ( SUM(AMOUNT) FOR QUARTER  IN ([1],[2],[3],[4]))AS PVT

--dynamic cross tab
SELECT YEAR,
		SUM(CASE QUARTER WHEN 1 THEN AMOUNT ELSE 0 END) AS Q1,
		SUM(CASE QUARTER WHEN 2 THEN AMOUNT ELSE 0 END) AS Q2,
		SUM(CASE QUARTER WHEN 3 THEN AMOUNT ELSE 0 END) AS Q3,
		SUM(CASE QUARTER WHEN 4 THEN AMOUNT ELSE 0 END) AS Q4
FROM 
	Pivot_SampleEx
GROUP BY YEAR

Mangal Pardeshi
Posting Yak Master

110 Posts

Posted - 2009-02-27 : 07:44:41

Try this


--Create sampletable
CREATE TABLE Fruites (Fruit varchar(100),Market varchar(100),Cost int)
-- Load sample data
INSERT INTO Fruites SELECT
'Apples', 'SuperMart', 1 UNION ALL SELECT 
'Bananas', 'SuperMart',2UNION ALL SELECT
'Oranges', 'SuperMart',3UNION ALL SELECT
'Pears', 'SuperMart', 1UNION ALL SELECT
'Apples', 'JollyGrocers',3UNION ALL SELECT
'Bananas', 'JollyGrocers',2UNION ALL SELECT
'Oranges', 'JollyGrocers',2
-- Query
SELECT Fruit, [SuperMart],[JollyGrocers], [FruitMans]
FROM Fruites
PIVOT
(
MAX(COST)
FOR Market IN([SuperMart],[JollyGrocers], [FruitMans])
)PVT

Mangal Pardeshi
http://mangalpardeshi.blogspot.com

cidr2
Starting Member

28 Posts

Posted - 2009-02-27 : 10:12:42

Thanks both for your input on this.

When I creat a data connection from the frontend to the database, and, subsequently the Pivot table, I wont be able to update them. Even in directly from SQL this doesn't seeem to be possible.

Do you know if there's any way of making the Pivot or Cross Tab updatable?

Thanks again

Ifor
Aged Yak Warrior

700 Posts

Posted - 2009-02-27 : 11:05:08

You should really use a stored procedure to update the base table.

If you still want to do this then create a view and use an INSTEAD OF trigger.

CREATE TABLE Fruits
(
    Fruit varchar(20) NOT NULL
    ,Market varchar(20) NOT NULL
    ,Cost money NOT NULL
)
INSERT INTO Fruits
SELECT 'Apples', 'SuperMart', 1 UNION ALL
SELECT 'Apples', 'JollyGrocers', 2 UNION ALL
SELECT 'Apples', 'FruitMans', 3 UNION ALL
SELECT 'Bananas', 'SuperMart', 2 UNION ALL
SELECT 'Bananas', 'JollyGrocers', 3 UNION ALL
SELECT 'Bananas', 'FruitMans', 3 UNION ALL
SELECT 'Oranges', 'SuperMart', 3 UNION ALL
SELECT 'Oranges', 'JollyGrocers', 1 UNION ALL
SELECT 'Oranges', 'FruitMans', 7 UNION ALL
SELECT 'Pears', 'SuperMart', 1 UNION ALL
SELECT 'Pears', 'JollyGrocers', 3 UNION ALL
SELECT 'Pears', 'FruitMans', 2
GO
CREATE VIEW vFruits
AS
SELECT Fruit, SuperMart, JollyGrocers, FruitMans
FROM Fruits
    PIVOT
    (
        MAX(COST)
        FOR Market IN(SuperMart, JollyGrocers, FruitMans)
    ) P
GO
CREATE TRIGGER vFruits_tu
ON vFruits
INSTEAD OF UPDATE
AS

SET NOCOUNT ON

UPDATE F
SET Cost = X.Cost
FROM Fruits F
    JOIN
    (
        SELECT I.Fruit
            ,CASE N.N
                WHEN 1 THEN 'SuperMart'
                WHEN 2 THEN 'JollyGrocers'
                WHEN 3 THEN 'FruitMans'
            END AS Market
            ,CASE N.N
                WHEN 1 THEN I.SuperMart
                WHEN 2 THEN I.JollyGrocers
                WHEN 3 THEN I.FruitMans
            END AS Cost
        FROM inserted I
            JOIN deleted D
                ON I.Fruit = D.Fruit
            CROSS JOIN
            (
                SELECT 1 UNION ALL
                SELECT 2 UNION ALL
                SELECT 3
            ) N(N)
    ) X
        ON F.Fruit = X.Fruit
            AND F.Market = X.Market
WHERE F.Cost <> X.Cost
GO

SELECT *
FROM vFruits

UPDATE vFruits
SET SuperMart = 5
WHERE Fruit = 'Apples'

SELECT *
FROM vFruits

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources