SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 updatable pivot table?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

cidr2
Starting Member

United Kingdom
28 Posts

Posted - 02/27/2009 :  05:56:50  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
1693 Posts

Posted - 02/27/2009 :  07:20:07  Show Profile  Reply with Quote
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
Go to Top of Page

Mangal Pardeshi
Posting Yak Master

India
110 Posts

Posted - 02/27/2009 :  07:44:41  Show Profile  Visit Mangal Pardeshi's Homepage  Reply with Quote
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
Go to Top of Page

cidr2
Starting Member

United Kingdom
28 Posts

Posted - 02/27/2009 :  10:12:42  Show Profile  Reply with Quote
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
Go to Top of Page

Ifor
Aged Yak Warrior

583 Posts

Posted - 02/27/2009 :  11:05:08  Show Profile  Reply with Quote
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



Edited by - Ifor on 02/27/2009 11:10:37
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.1 seconds. Powered By: Snitz Forums 2000