Query to pivot the values.

Question

Post reply

Query to pivot the values.

Joy Smith San

SSC-Insane

Points: 24877
More actions
September 1, 2008 at 3:04 am

#197718

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.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 1

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[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 2

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[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

Joy Smith San SSC-Insane Points: 24877 More actions · Answer 3

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 SSC-Insane Points: 21098 More actions · Answer 4

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[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

Joy Smith San SSC-Insane Points: 24877 More actions · Answer 5

ITEM_CODE VP-LISO XP-CMO MP-COM

--------------------------------------------------------

1 3000 5000 1500

2 3000 0 0

3 0 5000 1500

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 6

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[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

Joy Smith San SSC-Insane Points: 24877 More actions · Answer 7

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 SSC-Insane Points: 21098 More actions · Answer 8

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[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

Joy Smith San SSC-Insane Points: 24877 More actions · Answer 9

Joy Smith San

SSC-Insane

Points: 24877

September 1, 2008 at 11:17 pm

#865831

Hi,

yeah am trying that...Thanks a lot.

Santhu.

Joy Smith San SSC-Insane Points: 24877 More actions · Answer 10

hi,

I did it using a dynamic sql.

create table #dplCodes (dplCode Varchar(21))

--------------------------------------------------------------

DECLARE @STR nVARCHAR(1000)

SET @STR = 'SELECTITEM_CODE'

declare curDPLCodes cursor for

select distinct(DPL_CODE) from DESCRITIONARY_MASTER order by DPL_CODE

declare @dplCode varChar(21)

declare @dplCodes varChar(1000)

set @dplCodes = ''

Open curDPLCodes

fetch next from curDPLCodes into @dplCode

while(@@fetch_status=0)

begin

SET @STR = @STR + ', ISNULL(['+@dplCode+'],0) AS ['+@dplCode+']'

-- insert into #dplCodes values(@dplCode)

if ltrim(rtrim(@dplCodes)) <> ''

begin

set @dplCodes = @dplCodes+','

end

set @dplCodes = @dplCodes+'['+@dplCode+']'

fetch next from curDPLCodes into @dplCode

end

close curDPLCodes

deallocate curDPLCodes

------------------------------------

SET @STR = @STR + ' FROM(

SELECTITEM_CODE, DPL_CODE, MAX_AMOUNT

FROMDESCRITIONARY_POWER_LIST

) AS p

PIVOT(

MIN(MAX_AMOUNT) FOR DPL_CODE IN ('+@dplCodes+')'

SET @STR = @STR + ') AS pvt'

print @STR

EXECUTE SP_EXECUTESQL @STR

drop table #dplCodes

thanks...

santhu..