SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Query to pivot the values.


Query to pivot the values.

Author
Message
Joy Smith San
Joy Smith San
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3184 Visits: 3200
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.
Christopher Stobbs
Christopher Stobbs
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2418 Visits: 2233
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

w00t
Posting Best Practices
Numbers / Tally Tables

SQL-4-Life
Christopher Stobbs
Christopher Stobbs
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2418 Visits: 2233
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

w00t
Posting Best Practices
Numbers / Tally Tables

SQL-4-Life
Joy Smith San
Joy Smith San
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3184 Visits: 3200
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.
Christopher Stobbs
Christopher Stobbs
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2418 Visits: 2233
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

w00t
Posting Best Practices
Numbers / Tally Tables

SQL-4-Life
Joy Smith San
Joy Smith San
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3184 Visits: 3200
ITEM_CODE VP-LISO XP-CMO MP-COM
--------------------------------------------------------
1 3000 5000 1500
2 3000 0 0
3 0 5000 1500
Christopher Stobbs
Christopher Stobbs
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2418 Visits: 2233
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

w00t
Posting Best Practices
Numbers / Tally Tables

SQL-4-Life
Joy Smith San
Joy Smith San
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3184 Visits: 3200
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.
Christopher Stobbs
Christopher Stobbs
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2418 Visits: 2233
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

w00t
Posting Best Practices
Numbers / Tally Tables

SQL-4-Life
Joy Smith San
Joy Smith San
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3184 Visits: 3200
Hi,
yeah am trying that...Thanks a lot.


Santhu.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search