February 29, 2016 at 2:52 am
hi ,
multiple pivot query MONTH price + amount
EXAMPLE
Ocak ŞUBAT MART
PRİCE - AMOUNT PRİCE - AMOUNT PRİCE - AMOUNT
12,00 25,04 25,04 30,05 etc
SET LANGUAGE Turkish;
SELECT *
FROM ( SELECT ISNULL(st.AMOUNT, 0) AS miktar ,
DATENAME(mm, DATE_) AS ay ,
ITM.NAME ,
lc.DEFINITION_
FROM LG_001_01_STLINE AS st
INNER JOIN LG_001_CLCARD lc ON lc.LOGICALREF = st.CLIENTREF
INNER JOIN dbo.LG_001_ITEMS AS ITM ON ITM.LOGICALREF = st.STOCKREF
WHERE st.TRCODE <> 1
) tll PIVOT
( SUM(tll.AMOUNT) [highlight="#ffff11"]FOR month IN multiple ? [/highlight] ( [Ocak], [Şubat], [Mart], [Nisan], [Mayıs],
[Haziran], [Temmuz], [Ağustos], [Eylül], [Ekim],
[Kasım], [Aralık] ) ) p;
February 29, 2016 at 11:17 am
You can only pivot on one field. What you may need is a "crosstab". The code you presented would function:
SET LANGUAGE Turkish;
SELECT *
FROM (
SELECT ISNULL(st.AMOUNT, 0) AS miktar,
DATENAME(mm, DATE_) AS ay,
ITM.NAME ,
lc.DEFINITION_
FROM LG_001_01_STLINE AS st
INNER JOIN LG_001_CLCARD AS lc
ON lc.LOGICALREF = st.CLIENTREF
INNER JOIN dbo.LG_001_ITEMS AS ITM
ON ITM.LOGICALREF = st.STOCKREF
WHERE st.TRCODE <> 1
) AS tll
PIVOT (SUM(tll.AMOUNT) FOR month IN ([Ocak], [Subat], [Mart], [Nisan], [Mayis], [Haziran], [Temmuz],
[Agustos], [Eylül], [Ekim], [Kasim], [Aralik])) AS p;
I just removed the word multiple and the question mark, and then "prettified" the code by using indentation. It just may not give you the results you seek. What it will do is provide each months SUM value for the AMOUNT field, in fields named for each month. However, it's not likely to look right, as you are also selecting AMOUNT in the query, and I'm not sure what that will look like, as I don't have access to your data. It should probably be removed from the SELECT.
If you need to categorize by month and by other fields, I'd need sample data and a description of how the grouping needs to work in order to write crosstab code.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 1, 2016 at 12:52 am
hi steve ,
for example
SET LANGUAGE Turkish;
SELECT '2016' Company , [Ocak],[1],[Subat],[2]
FROM (
SELECT ISNULL(st.AMOUNT, 0) AS miktar,
DATENAME(mm, DATE_) AS ay,
st.PRICE ,
ITM.NAME ,
lc.DEFINITION_
FROM LG_001_01_STLINE AS st
INNER JOIN LG_001_CLCARD AS lc
ON lc.LOGICALREF = st.CLIENTREF
INNER JOIN dbo.LG_001_ITEMS AS ITM
ON ITM.LOGICALREF = st.STOCKREF
WHERE st.TRCODE <> 1
) AS tll
PIVOT (SUM(tll.AMOUNT) FOR month IN ([Ocak], [Subat], [Mart], [Nisan], [Mayis], [Haziran], [Temmuz],
[Agustos], [Eylül], [Ekim], [Kasim], [Aralik])) AS p
PIVOT (SUM(tll.PRICE) FOR month IN ([1], [2], [3])) AS p2
example table print
month month
amount - price amount - price
March 1, 2016 at 7:56 am
TeknikServis27 (3/1/2016)
hi steve ,for example
SET LANGUAGE Turkish;
SELECT '2016' Company , [Ocak],[1],[Subat],[2]
FROM (
SELECT ISNULL(st.AMOUNT, 0) AS miktar,
DATENAME(mm, DATE_) AS ay,
st.PRICE ,
ITM.NAME ,
lc.DEFINITION_
FROM LG_001_01_STLINE AS st
INNER JOIN LG_001_CLCARD AS lc
ON lc.LOGICALREF = st.CLIENTREF
INNER JOIN dbo.LG_001_ITEMS AS ITM
ON ITM.LOGICALREF = st.STOCKREF
WHERE st.TRCODE <> 1
) AS tll
PIVOT (SUM(tll.AMOUNT) FOR month IN ([Ocak], [Subat], [Mart], [Nisan], [Mayis], [Haziran], [Temmuz],
[Agustos], [Eylül], [Ekim], [Kasim], [Aralik])) AS p
PIVOT (SUM(tll.PRICE) FOR month IN ([1], [2], [3])) AS p2
example table print
month month
amount - price amount - price
Just as a suggestion... PIVOTs are generally slower than CROSSTABs to begin with. Having two PIVOTS is generally twice as bad. For performance information and information on how to do CROSSTABs, please see the following article.
http://www.sqlservercentral.com/articles/T-SQL/63681/
To go for the gold in the area of dynamic temporally based CROSSTABs that you don't even have to supply reporting range parameters for (but could), please see the following article.
http://www.sqlservercentral.com/articles/Crosstab/65048/
As a bit of a sidebar, you'll also find that CROSSTABs lend themselves better to doing things like row totals than PIVOTs do.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply