Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Query to pivot the values. Expand / Collapse
Author
Message
Posted Monday, September 1, 2008 3:04 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 11:40 PM
Points: 1,520, Visits: 2,551
Dear All,
I have the following table .

ITEM_CODE DPL_CODE MAX_AMT
----------------------------------------------------------
1 VP-LISO 3000 1 XP-CMO 5000 1 MP-COM 1500

Now I want to write a query which will get the records in following format.

ITEM_CODE VP-LISO XP-CMO MP-COM
--------------------------------------------------------
1 3000 5000 1500


How can I do this..? Please help..



Regards,
Santhosh.










Post #561903
Posted Monday, September 1, 2008 3:08 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, February 24, 2010 4:10 AM
Points: 1,553, Visits: 2,232
Hi there,

Try looking up the PIVOT funtion in BOL.

If you still having problems let us know and we help you with your code.

Thanks
Chris


----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley


Posting Best Practices
Numbers / Tally Tables

SQL-4-Life
Post #561905
Posted Monday, September 1, 2008 3:17 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, February 24, 2010 4:10 AM
Points: 1,553, Visits: 2,232
Sorry also,

How do you want to Aggregate MAX_AMT?

For Example if you have you Multiple rows with the same DPL_CODE , should the query add the MAX_AMT's together? or take the lowest or highest?

Thanks
Chris


----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley


Posting Best Practices
Numbers / Tally Tables

SQL-4-Life
Post #561908
Posted Monday, September 1, 2008 3:27 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 11:40 PM
Points: 1,520, Visits: 2,551

Amount is DPL_CODE wise. For same code amount will be same. Dont wan't AVG, SUM or MIN/MAX...Just distinct AMOUNT will do....



Thanks,
Santhosh.
Post #561911
Posted Monday, September 1, 2008 3:30 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, February 24, 2010 4:10 AM
Points: 1,553, Visits: 2,232
Could you give me an example please, of what the result will look like if you have the same Code?

Thanks
CHris


----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley


Posting Best Practices
Numbers / Tally Tables

SQL-4-Life
Post #561915
Posted Monday, September 1, 2008 3:54 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 11:40 PM
Points: 1,520, Visits: 2,551
ITEM_CODE VP-LISO XP-CMO MP-COM
--------------------------------------------------------
1 3000 5000 1500
2 3000 0 0
3 0 5000 1500
Post #561920
Posted Monday, September 1, 2008 4:22 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, February 24, 2010 4:10 AM
Points: 1,553, Visits: 2,232
Hi there,

OK I am assume this is what you want.

DECLARE @myTable TABLE
(ITEM_CODE INT,
DPL_CODE VARCHAR(10),
MAX_AMT INT)

INSERT INTO @myTable
SELECT 1,'VP-LISO',3000 UNION ALL
SELECT 1,'XP-CMO',5000 UNION ALL
SELECT 1,'MP-COM',1500 UNION ALL
SELECT 2,'VP-LISO',3000 UNION ALL
SELECT 3,'XP-CMO',5000 UNION ALL
SELECT 3,'MP-COM',1500

SELECT
ITEM_CODE,
ISNULL([VP-LISO],0) as [VP-LISO],
ISNULL([XP-CMO],0) as [XP-CMO],
ISNULL([MP-COM],0) as [MP-COM]
FROM
(SELECT ITEM_CODE,DPL_CODE,MAX_AMT
FROM @myTable) as p
PIVOT (MIN(MAX_AMT) FOR DPL_CODE IN ([VP-LISO],[XP-CMO],[MP-COM])
) as pvt



----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley


Posting Best Practices
Numbers / Tally Tables

SQL-4-Life
Post #561931
Posted Monday, September 1, 2008 9:00 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 11:40 PM
Points: 1,520, Visits: 2,551
Except one thing.
Number of 'DPL_CODE' will vary..So the name..Client may put as many as they want with wotever the names they like..
So we can't hard code it like following

ISNULL([VP-LISO],0) as [VP-LISO],
ISNULL([XP-CMO],0) as [XP-CMO],
ISNULL([MP-COM],0) as [MP-COM]

Otherwise everything is perfect...exactly wot I want..

Thanks,
Santhosh Nair.
Post #562025
Posted Monday, September 1, 2008 9:04 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, February 24, 2010 4:10 AM
Points: 1,553, Visits: 2,232
HI there,

Try and see what you can come up with by using a dynamic pivot query


----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley


Posting Best Practices
Numbers / Tally Tables

SQL-4-Life
Post #562027
Posted Monday, September 1, 2008 11:17 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 11:40 PM
Points: 1,520, Visits: 2,551
Hi,
yeah am trying that...Thanks a lot.


Santhu.
Post #562186
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse