Crosstab query - 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

General SQL Server Forums

New to SQL Server Programming

Crosstab query

Author

Topic

OWSQL
Starting Member

27 Posts

Posted - 2011-11-03 : 05:48:17

Hi
I have a table containing priceindex, pricedate and price. it looks somehow like this:

Priceindex Pricedate Price
A 02-11-2011 50
B 02-11-2011 60
C 02-11-2011 51
A 01-11-2011 45
B 01-11-2011 53
C 01-11-2011 55

I would like to have this transformed into:

Pricedate A B C
01-11-2011 45 53 55
02-11-2011 50 60 51

I order to get the data I am joining to tables:
FROM pricevalue, priceindex
WHERE priceindex.priceindex=pricevalue.priceindex

and I have the following clausul:
AND ((priceindex.indextype = 'CURRENT' or priceindex.indextype = 'POSTING' or
priceindex.indextype = 'QUARTERLY' or priceindex.indextype = 'MONTHLY'))

I would also like to retrive only the 20 previous prices from a specified pricedate.

Is this somehow possible?
Thanks!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-03 : 07:36:45

20 previous prices for each indextype you mean or 20 prices altogether?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

OWSQL
Starting Member

27 Posts

Posted - 2011-11-03 : 07:48:05

Hi

Yes, I mean 20 previous prices for each indextype.

based on this I will do some average calculations.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-03 : 07:53:47

sounds like below


SELECT Pricedate ,
MAX(CASE WHEN  Priceindex ='A' then Price else null end) as [A],
MAX(CASE WHEN  Priceindex ='B' then Price else null end) as [B],
MAX(CASE WHEN  Priceindex ='C' then Price else null end) as [C]
FROM
(
SELECT ROW_NUMBER() OVER(PARTITION BY pricevalue.indextype ORDER BY pricevalue.Pricedate DESC) AS Rn,pricevalue.indextype ,Pricedate, Price
FROM pricevalue
inner join priceindex 
on priceindex.priceindex=pricevalue.priceindex 
where pricedate< @yourpricedatevalue
)t
WHERE Rn<=20
AND (priceindex.indextype = 'CURRENT' or priceindex.indextype = 'POSTING' or 
priceindex.indextype = 'QUARTERLY' or priceindex.indextype = 'MONTHLY')
GROUP BY Pricedate

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

OWSQL
Starting Member

27 Posts

Posted - 2011-11-03 : 10:30:16

what does the "....OVER(Partition by pricevalue.indextype ORDER BY..." do?
I have applied the code below, but get this error msg:

"Msg 207, Level 16, State 1, Line 11
Invalid column name 'indextype'.
Msg 4104, Level 16, State 1, Line 11
The multi-part identifier "priceindex.indextype" could not be bound.
Msg 4104, Level 16, State 1, Line 11
The multi-part identifier "priceindex.indextype" could not be bound."

use AllegroPROD
Declare @yourpricedatevalue datetime;
Set @yourpricedatevalue = '2011-10-21 00:00:00.000';

SELECT pricevalue.pricedate,
MAX(CASE WHEN pricevalue.priceindex ='CAL HS Rtdm 3.5% FOB Barges' then pricevalue.price else null end) as [CAL HS Rtdm 3.5% FOB Barges],
MAX(CASE WHEN pricevalue.priceindex ='CAL HS Rtdm 3.5% CIF Cargoes' then pricevalue.price else null end) as [CAL HS Rtdm 3.5% CIF Cargoes],
MAX(CASE WHEN pricevalue.priceindex ='CAL LS Rtdm 1% FOB Barges' then pricevalue.price else null end) as [CAL LS Rtdm 1% FOB Barges]
FROM
(
SELECT ROW_NUMBER() OVER(PARTITION BY pricevalue.indextype ORDER BY pricevalue.pricedate DESC) AS Rn,priceindex.indextype ,pricevalue.pricedate, pricevalue.price
FROM pricevalue
inner join priceindex
on priceindex.priceindex=pricevalue.priceindex
where pricevalue.pricedate< @yourpricedatevalue
)t
WHERE Rn<=20
AND (priceindex.indextype = 'CURRENT' or priceindex.indextype = 'POSTING' or
priceindex.indextype = 'QUARTERLY' or priceindex.indextype = 'MONTHLY')
GROUP BY pricevalue.pricedate

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-03 : 10:37:38

is there a column called indextype in your table?
over partition by divides data to groups and numbers the data within it based on given order

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

OWSQL
Starting Member

27 Posts

Posted - 2011-11-03 : 10:44:54

yes, in the table priceindex. But I do not need to see it just as long as the condition "Current" is applied

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-03 : 10:59:17

but you've lots of other conditions also

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

OWSQL
Starting Member

27 Posts

Posted - 2011-11-03 : 11:05:26

yes sorry. The 4 below.
AND (priceindex.indextype = 'CURRENT' or priceindex.indextype = 'POSTING' or
priceindex.indextype = 'QUARTERLY' or priceindex.indextype = 'MONTHLY')

OWSQL
Starting Member

27 Posts

Posted - 2011-11-03 : 11:13:44

Sorry! I do not need the condition on indextype anyways. I just checked the table again. It is overkill.
does this makes it easier?
Again many thanks!

OWSQL
Starting Member

27 Posts

Posted - 2011-11-03 : 18:27:55

Below code gives me 4000 rows of pricedates with 3 columns with priceindex and prices in the rows. It is on this tabke I would like to apply OVER PARTITION BY. I have tried several ways with the suggested @yourpricedate setup as above but I get "jumping" data. I think the partition is on the wrong table? Thanks.

use AllegroPROD
SELECT pricevalue.pricedate,
MAX(CASE WHEN pricevalue.priceindex ='CAL HS Rtdm 3.5% FOB Barges' then pricevalue.price else null end) as [CAL HS Rtdm 3.5% FOB Barges],
MAX(CASE WHEN pricevalue.priceindex ='CAL HS Rtdm 3.5% CIF Cargoes' then pricevalue.price else null end) as [CAL HS Rtdm 3.5% CIF Cargoes],
MAX(CASE WHEN pricevalue.priceindex ='CAL LS Rtdm 1% FOB Barges' then pricevalue.price else null end) as [CAL LS Rtdm 1% FOB Barges]
FROM pricevalue
group BY pricevalue.pricedate
order BY pricevalue.pricedate desc

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-04 : 04:33:56

you mean this?


use AllegroPROD
select *
from
(
SELECT pricevalue.pricedate,
ROW_NUMBER() OVER (ORDER BY pricevalue.pricedate DESC) AS Rn,
MAX(CASE WHEN pricevalue.priceindex ='CAL HS Rtdm 3.5% FOB Barges' then pricevalue.price else null end) as [CAL HS Rtdm 3.5% FOB Barges],
MAX(CASE WHEN pricevalue.priceindex ='CAL HS Rtdm 3.5% CIF Cargoes' then pricevalue.price else null end) as [CAL HS Rtdm 3.5% CIF Cargoes],
MAX(CASE WHEN pricevalue.priceindex ='CAL LS Rtdm 1% FOB Barges' then pricevalue.price else null end) as [CAL LS Rtdm 1% FOB Barges]
FROM pricevalue
group BY pricevalue.pricedate 
)r
where Rn<=20
order BY pricedate desc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

OWSQL
Starting Member

27 Posts

Posted - 2011-11-04 : 04:58:43

Perfect!!! Thanks!
Is there a way then to skip dates where pricevale is NULL? As shown in case below.

use AllegroPROD
Declare @yourpricedatevalue datetime;
Set @yourpricedatevalue = '2011-10-03 00:00:00.000';
select *
from
(
SELECT pricevalue.pricedate,
ROW_NUMBER() OVER (ORDER BY pricevalue.pricedate DESC) AS Rn,
MAX(CASE WHEN pricevalue.priceindex ='CAL HS Rtdm 3.5% FOB Barges' then pricevalue.price else null end) as [CAL HS Rtdm 3.5% FOB Barges],
MAX(CASE WHEN pricevalue.priceindex ='CAL HS Rtdm 3.5% CIF Cargoes' then pricevalue.price else null end) as [CAL HS Rtdm 3.5% CIF Cargoes],
MAX(CASE WHEN pricevalue.priceindex ='CAL LS Rtdm 1% FOB Barges' then pricevalue.price else null end) as [CAL LS Rtdm 1% FOB Barges]
FROM pricevalue
where pricedate< @yourpricedatevalue
group BY pricevalue.pricedate
)r
where Rn<=20
order BY pricedate desc

pricedate Rn CAL HS Rtdm 3.5% FOB Barges CAL HS Rtdm 3.5% CIF Cargoes CAL LS Rtdm 1% FOB Barges
2011-10-01 00:00:00.000 1 NULL NULL NULL
2011-09-30 00:00:00.000 2 600.250000 596.250000 618.500000
2011-09-29 00:00:00.000 3 603.500000 599.500000 626.250000
2011-09-28 00:00:00.000 4 609.250000 605.250000 632.000000
2011-09-27 00:00:00.000 5 607.500000 603.500000 628.250000
2011-09-26 00:00:00.000 6 598.250000 594.250000 618.000000
2011-09-23 00:00:00.000 7 604.500000 600.500000 626.250000
2011-09-22 00:00:00.000 8 607.250000 603.250000 626.000000
2011-09-21 00:00:00.000 9 640.500000 636.500000 659.000000
2011-09-20 00:00:00.000 10 638.000000 634.000000 654.250000
2011-09-19 00:00:00.000 11 629.250000 625.250000 646.000000
2011-09-16 00:00:00.000 12 637.500000 633.750000 654.000000
2011-09-15 00:00:00.000 13 643.750000 640.000000 656.500000
2011-09-14 00:00:00.000 14 621.500000 617.500000 638.000000
2011-09-13 00:00:00.000 15 620.000000 616.000000 640.000000
2011-09-12 00:00:00.000 16 624.750000 620.500000 641.250000
2011-09-09 00:00:00.000 17 629.250000 625.000000 647.500000
2011-09-08 00:00:00.000 18 653.000000 648.500000 670.250000
2011-09-07 00:00:00.000 19 649.750000 645.250000 668.000000
2011-09-06 00:00:00.000 20 625.250000 620.750000 641.250000

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-04 : 05:28:28

[code]
use AllegroPROD
Declare @yourpricedatevalue datetime;
Set @yourpricedatevalue = '2011-10-03 00:00:00.000';
select *
from
(
SELECT pricevalue.pricedate,
ROW_NUMBER() OVER (ORDER BY pricevalue.pricedate DESC) AS Rn,
MAX(CASE WHEN pricevalue.priceindex ='CAL HS Rtdm 3.5% FOB Barges' then pricevalue.price else null end) as [CAL HS Rtdm 3.5% FOB Barges],
MAX(CASE WHEN pricevalue.priceindex ='CAL HS Rtdm 3.5% CIF Cargoes' then pricevalue.price else null end) as [CAL HS Rtdm 3.5% CIF Cargoes],
MAX(CASE WHEN pricevalue.priceindex ='CAL LS Rtdm 1% FOB Barges' then pricevalue.price else null end) as [CAL LS Rtdm 1% FOB Barges]
FROM pricevalue
where pricedate< @yourpricedatevalue
AND pricevalue.price IS NOT NULL
group BY pricevalue.pricedate
)r
where Rn<=20
order BY pricedate desc

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

OWSQL
Starting Member

27 Posts

Posted - 2011-11-04 : 05:43:31

I tried that but it does not work since the row already have a number I think.

Perhaps it would be possible make a condition on the Row_number that it should only give a number to a row that has a pricevalue different from NULL?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-04 : 05:46:38

[code]
use AllegroPROD
Declare @yourpricedatevalue datetime;
Set @yourpricedatevalue = '2011-10-03 00:00:00.000';
select *
from
(
SELECT pricevalue.pricedate,
ROW_NUMBER() OVER (ORDER BY pricevalue.pricedate DESC) AS Rn,
MAX(CASE WHEN pricevalue.priceindex ='CAL HS Rtdm 3.5% FOB Barges' then pricevalue.price else null end) as [CAL HS Rtdm 3.5% FOB Barges],
MAX(CASE WHEN pricevalue.priceindex ='CAL HS Rtdm 3.5% CIF Cargoes' then pricevalue.price else null end) as [CAL HS Rtdm 3.5% CIF Cargoes],
MAX(CASE WHEN pricevalue.priceindex ='CAL LS Rtdm 1% FOB Barges' then pricevalue.price else null end) as [CAL LS Rtdm 1% FOB Barges]
FROM pricevalue
where pricedate< @yourpricedatevalue
AND pricevalue.price IN ('CAL HS Rtdm 3.5% FOB Barges',
'CAL HS Rtdm 3.5% CIF Cargoes',
'CAL LS Rtdm 1% FOB Barges' )
group BY pricevalue.pricedate
)r
where Rn<=20
order BY pricedate desc

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jassi.singh
Posting Yak Master

122 Posts

Posted - 2011-11-04 : 08:26:12

Hi,

I think you should use pivot
Refer:
http://msdn.microsoft.com/en-us/library/ms177410.aspx
http://blog.sqlauthority.com/2008/06/07/sql-server-pivot-and-unpivot-table-examples/

Please mark answer as accepted if it helped you.

Thanks,
Jassi Singh

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-04 : 12:45:33

quote:
Originally posted by jassi.singh

Hi,

I think you should use pivot
Refer:
http://msdn.microsoft.com/en-us/library/ms177410.aspx
http://blog.sqlauthority.com/2008/06/07/sql-server-pivot-and-unpivot-table-examples/

Please mark answer as accepted if it helped you.

Thanks,
Jassi Singh

no need of using pivot
even posted query gives OP expected result
it even has flexibility of multiple pivoting

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

OWSQL
Starting Member

27 Posts

Posted - 2011-11-04 : 13:14:37

Visakh16, thank you very much for your help. I now have a running solution. :-)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-05 : 13:11:59

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources