• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 220
  • Last Modified:

SQL Pivot query problem

Hi Experts,
    I'm working on a query that will join up a few tables which eventually will display a very detailed product information.  currently i'm working on only join two tables as a start, which is main table 'Products' and another table 'PSales‘.
I have worked out a testing query like this  :
select top 5 periodno
INTO #tmp_period
FROM PSALES
group by PeriodNo
order by PeriodNo desc;

with PeriodNumbers As
(Select  row_number() over (order by periodno desc) as rowno from #tmp_period)

SELECT uniqueid, ProductCode, lastcost, PictureImage,Notes, [1] as monthq0, [2] as monthq1, [3] as monthq2, [4] as monthq3, [5] as monthq4
FROM (select p.periodno, PeriodSales, t.uniqueid, t.ProductCode, t.Category, t.Producttitle, t.groupno, t.taxcategory, 
t.leadtime, t.minquantity, t.Zbin1, t.AlternateItem, t.Zbin2, t.Salesunit1, t.purchaseunit, 
t.weight, t.EUOrigin, t.EUCommodity, t.RatioUnit1, t.price11, t.price12, t.price13, t.price14,
t.price15, t.price16, t.price17, t.price18, t.standardcost, t.LastInQuantity, CASE When t.LastinQuantity = 0 Then 0 Else t.LastInTotalCost/t.Lastinquantity END as lastcost, 
t.CustOrders, t.SuppOrders, t.OpeningQty, t.QtyOnHand, t.OpeningValue, t.OnHandValue, t.PTDSales, t.PTDProfit, t.YTDSales, t.YTDProfit, 
t.DueDate, t.DueQuantity, Cast(t.Notes as varchar(500)) as Notes, t.supplierpartno, t.supplierID, t.LastEditDateTime, 
CAST(t.PictureImage as Varchar(100)) as PictureImage, t.WETApplies, t.LCTApplies, t.SalesUnit2, t.RatioUnit2, t.salesunit3, t.ratiounit3, t.salesunit4, t.ratiounit4, 
t.Status, t.WebSiteURL, CAST(t.SalesInfo as Varchar(50)) as saleinfo, t.Barcode, t.DocumentFolder, t.ZUSEREDIT, t.zcomcost, t.zrate, t.zmargin1, 
t.zmargin2, t.zmargin3, t.zmargin4, t.zmargin5, t.zmargin6, t.zmargin7, t.zmargin8, t.zstandard  from PSALES p 
Right Outer join PRODUCTS t ON p.ProductID = t.UniqueID Inner join PeriodNumbers n ON p.periodno = n.rowno) as s 
pivot (max(periodsales) for rowno in ([1],[2],[3],[4],[5])) AS PVT;

drop table #tmp_period

Open in new window


when i run it, i got this error: 'Invalid column name 'rowno'. at line 22.
as this is the first day i ever come across PIVOT in sql could anybody help me with the syntax here?

I actually modified the query simply because i didn't want the actual period number to be the column header, instead i want to name it myself, to 'monthq0,monthq1....'
My original query which worked was:
DECLARE @listCol VARCHAR(2000)
DECLARE @query VARCHAR(4000)
SELECT  @listCol = STUFF(( 
SELECT DISTINCT Top 5 '],[' + ltrim(periodno) FROM PSALES Group By periodno ORDER BY '],[' + ltrim(periodno)desc 
FOR XML PATH('')), 1, 2,'') + ']'

set @query = 
'SELECT uniqueid, ProductCode, lastcost, PictureImage,Notes, ' + @listcol + ' 
FROM (select periodno, PeriodSales, t.uniqueid, t.ProductCode, t.Category, t.Producttitle, t.groupno, t.taxcategory, 
t.leadtime, t.minquantity, t.Zbin1, t.AlternateItem, t.Zbin2, t.Salesunit1, t.purchaseunit, 
t.weight, t.EUOrigin, t.EUCommodity, t.RatioUnit1, t.price11, t.price12, t.price13, t.price14,
t.price15, t.price16, t.price17, t.price18, t.standardcost, t.LastInQuantity, CASE When t.LastinQuantity = 0 Then 0 Else t.LastInTotalCost/t.Lastinquantity END as lastcost, 
t.CustOrders, t.SuppOrders, t.OpeningQty, t.QtyOnHand, t.OpeningValue, t.OnHandValue, t.PTDSales, t.PTDProfit, t.YTDSales, t.YTDProfit, 
t.DueDate, t.DueQuantity, Cast(t.Notes as varchar(500)) as Notes, t.supplierpartno, t.supplierID, t.LastEditDateTime, 
CAST(t.PictureImage as Varchar(100)) as PictureImage, t.WETApplies, t.LCTApplies, t.SalesUnit2, t.RatioUnit2, t.salesunit3, t.ratiounit3, t.salesunit4, t.ratiounit4, 
t.Status, t.WebSiteURL, CAST(t.SalesInfo as Varchar(50)) as saleinfo, t.Barcode, t.DocumentFolder, t.ZUSEREDIT, t.zcomcost, t.zrate, t.zmargin1, 
t.zmargin2, t.zmargin3, t.zmargin4, t.zmargin5, t.zmargin6, t.zmargin7, t.zmargin8, t.zstandard  from PSALES p Right Outer join PRODUCTS t ON p.ProductID = t.UniqueID) as s 
pivot (max(periodsales) for periodno in (' +@listcol + ')) 
AS PivotTable'

execute (@query)

Open in new window


my original question was posted here:http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_26988160.html
0
miketonny
Asked:
miketonny
  • 5
  • 3
1 Solution
 
SharathData EngineerCommented:
You have to include the rowno column in the SELECT clause of inner query.
select top 5 periodno
INTO #tmp_period
FROM PSALES
group by PeriodNo
order by PeriodNo desc;

with PeriodNumbers As
(Select  row_number() over (order by periodno desc) as rowno from #tmp_period)

SELECT uniqueid, ProductCode, lastcost, PictureImage,Notes, [1] as monthq0, [2] as monthq1, [3] as monthq2, [4] as monthq3, [5] as monthq4
FROM (select p.periodno, PeriodSales, t.uniqueid, t.ProductCode, t.Category, t.Producttitle, t.groupno, t.taxcategory, 
t.leadtime, t.minquantity, t.Zbin1, t.AlternateItem, t.Zbin2, t.Salesunit1, t.purchaseunit, 
t.weight, t.EUOrigin, t.EUCommodity, t.RatioUnit1, t.price11, t.price12, t.price13, t.price14,
t.price15, t.price16, t.price17, t.price18, t.standardcost, t.LastInQuantity, CASE When t.LastinQuantity = 0 Then 0 Else t.LastInTotalCost/t.Lastinquantity END as lastcost, 
t.CustOrders, t.SuppOrders, t.OpeningQty, t.QtyOnHand, t.OpeningValue, t.OnHandValue, t.PTDSales, t.PTDProfit, t.YTDSales, t.YTDProfit, 
t.DueDate, t.DueQuantity, Cast(t.Notes as varchar(500)) as Notes, t.supplierpartno, t.supplierID, t.LastEditDateTime, 
CAST(t.PictureImage as Varchar(100)) as PictureImage, t.WETApplies, t.LCTApplies, t.SalesUnit2, t.RatioUnit2, t.salesunit3, t.ratiounit3, t.salesunit4, t.ratiounit4, 
t.Status, t.WebSiteURL, CAST(t.SalesInfo as Varchar(50)) as saleinfo, t.Barcode, t.DocumentFolder, t.ZUSEREDIT, t.zcomcost, t.zrate, t.zmargin1, 
t.zmargin2, t.zmargin3, t.zmargin4, t.zmargin5, t.zmargin6, t.zmargin7, t.zmargin8, t.zstandard,n.rowno  from PSALES p 
Right Outer join PRODUCTS t ON p.ProductID = t.UniqueID Inner join PeriodNumbers n ON p.periodno = n.rowno) as s 
pivot (max(periodsales) for rowno in ([1],[2],[3],[4],[5])) AS PVT;

drop table #tmp_period

Open in new window

0
 
Walter RitzelSenior Software EngineerCommented:
try this:

 SELECT top 5 periodno
   INTO #tmp_period
   FROM PSALES
GROUP BY PeriodNo
ORDER BY PeriodNo DESC;
WITH PeriodNumbers AS
    (SELECT row_number() over (order by periodno DESC) AS rowno FROM #tmp_period
    )
 SELECT uniqueid
  , ProductCode
  , lastcost
  , PictureImage
  ,Notes
  , [1] AS monthq0
  , [2] AS monthq1
  , [3] AS monthq2
  , [4] AS monthq3
  , [5] AS monthq4
   FROM
    (SELECT p.periodno
      , PeriodSales
      , t.uniqueid
      , t.ProductCode
      , t.Category
      , t.Producttitle
      , t.groupno
      , t.taxcategory
      , t.leadtime
      , t.minquantity
      , t.Zbin1
      , t.AlternateItem
      , t.Zbin2
      , t.Salesunit1
      , t.purchaseunit
      , t.weight
      , t.EUOrigin
      , t.EUCommodity
      , t.RatioUnit1
      , t.price11
      , t.price12
      , t.price13
      , t.price14
      , t.price15
      , t.price16
      , t.price17
      , t.price18
      , t.standardcost
      , t.LastInQuantity
      , CASE
            WHEN t.LastinQuantity = 0
            THEN 0
            ELSE t.LastInTotalCost/t.Lastinquantity
        END AS lastcost
      , t.CustOrders
      , t.SuppOrders
      , t.OpeningQty
      , t.QtyOnHand
      , t.OpeningValue
      , t.OnHandValue
      , t.PTDSales
      , t.PTDProfit
      , t.YTDSales
      , t.YTDProfit
      , t.DueDate
      , t.DueQuantity
      , CAST(t.Notes AS VARCHAR(500)) AS Notes
      , t.supplierpartno
      , t.supplierID
      , t.LastEditDateTime
      , CAST(t.PictureImage AS VARCHAR(100)) AS PictureImage
      , t.WETApplies
      , t.LCTApplies
      , t.SalesUnit2
      , t.RatioUnit2
      , t.salesunit3
      , t.ratiounit3
      , t.salesunit4
      , t.ratiounit4
      , t.Status
      , t.WebSiteURL
      , CAST(t.SalesInfo AS VARCHAR(50)) AS saleinfo
      , t.Barcode
      , t.DocumentFolder
      , t.ZUSEREDIT
      , t.zcomcost
      , t.zrate
      , t.zmargin1
      , t.zmargin2
      , t.zmargin3
      , t.zmargin4
      , t.zmargin5
      , t.zmargin6
      , t.zmargin7
      , t.zmargin8
      , t.zstandard
      , n.rowno
       FROM PSALES p
    RIGHT OUTER JOIN PRODUCTS t
         ON p.ProductID = t.UniqueID
    INNER JOIN PeriodNumbers n
         ON p.periodno                        = n.rowno
    ) AS s pivot (MAX(periodsales) FOR rowno IN ([1],[2],[3],[4],[5])) AS PVT;
DROP TABLE #tmp_period

Open in new window

0
 
miketonnyAuthor Commented:
that fixed the error thanks, but the query doesn't return any results, so i assume i must did something wrong in here. could that be the CTE was wrong? can i see what row_number returns?
my intention for getting from PeriodNumbers are the latest 5 period numbers dynamically
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
miketonnyAuthor Commented:
i think it was the CTE problem, i changed it to :
with PeriodNumbers As
(Select  periodno, row_number() over (order by periodno desc) as rowno from #tmp_period)

Open in new window


and below:
from PSALES p inner join PeriodNumbers n ON p.periodno = n.PeriodNo
Right join PRODUCTS t ON p.ProductID = t.UniqueID ) as s 
pivot (max(periodsales) for rowno in ([1],[2],[3],[4],[5])) AS PVT order by UniqueID;

Open in new window


it runs fine,
but another question then raised, the result shows some of the products like this:
UniqueID                       ProductCode            monthq0    monthq1  monthq2   monthq3   monthq4
PBAGBELXXXX            BAGBEL1087333            NULL      NULL      NULL      NULL      3.0000
PBAGBELXXXX            BAGBEL1087333            NULL      NULL      NULL      5.0000      NULL
PBAGBELXXXX            BAGBEL1087333            NULL      NULL      12.0000      NULL      NULL

should pivot grouping the rows already? how do i do to fix this? anything to do with my joins?
0
 
miketonnyAuthor Commented:
any help?
0
 
SharathData EngineerCommented:
try this.
SELECT TOP 5 periodno 
  INTO #tmp_period 
  FROM PSALES 
 GROUP BY PeriodNo 
 ORDER BY PeriodNo DESC; 

WITH PeriodNumbers 
     AS (SELECT ROW_NUMBER() OVER (ORDER BY periodno DESC) AS rowno 
           FROM #tmp_period) 
SELECT uniqueid, 
       ProductCode, 
       lastcost, 
       PictureImage, 
       Notes, 
       [1] AS monthq0, 
       [2] AS monthq1, 
       [3] AS monthq2, 
       [4] AS monthq3, 
       [5] AS monthq4 
  FROM (SELECT uniqueid, 
               ProductCode, 
               lastcost, 
               Notes, 
               PictureImage 
          FROM (SELECT t.uniqueid, 
                       t.ProductCode, 
                       CAST(t.Notes AS VARCHAR(500))        AS Notes, 
                       CASE 
                         WHEN t.LastinQuantity = 0 THEN 0 
                         ELSE t.LastInTotalCost / t.Lastinquantity 
                       END                                  AS lastcost, 
                       CAST(t.PictureImage AS VARCHAR(100)) AS PictureImage, 
                       n.rowno 
                  FROM PSALES p 
                       RIGHT OUTER JOIN PRODUCTS t 
                         ON p.ProductID = t.UniqueID 
                       INNER JOIN PeriodNumbers n 
                         ON p.periodno = n.rowno)t1) AS s PIVOT (MAX(periodsales
        ) FOR 
       rowno IN ( [1], [2], [3], [4], [5])) AS PVT; 

DROP TABLE #tmp_period  

Open in new window

0
 
miketonnyAuthor Commented:
it says invalid column name "periodsales" and "rowno" on
                   
  ON p.periodno = n.rowno)t1) AS s PIVOT (MAX(periodsales) FOR rowno IN ( [1], [2], [3], [4], [5])) AS PVT;

Open in new window

0
 
SharathData EngineerCommented:
Can you check this?
SELECT TOP 5 periodno 
  INTO #tmp_period 
  FROM PSALES 
 GROUP BY PeriodNo 
 ORDER BY PeriodNo DESC; 

WITH PeriodNumbers 
     AS (SELECT ROW_NUMBER() OVER (ORDER BY periodno DESC) AS rowno 
           FROM #tmp_period) 
SELECT uniqueid, 
       ProductCode, 
       lastcost, 
       PictureImage, 
       Notes, 
       [1] AS monthq0, 
       [2] AS monthq1, 
       [3] AS monthq2, 
       [4] AS monthq3, 
       [5] AS monthq4 
  FROM (SELECT t.uniqueid, 
               periodsales, 
               t.ProductCode, 
               CAST(t.Notes AS VARCHAR(500))        AS Notes, 
               CASE 
                 WHEN t.LastinQuantity = 0 THEN 0 
                 ELSE t.LastInTotalCost / t.Lastinquantity 
               END                                  AS lastcost, 
               CAST(t.PictureImage AS VARCHAR(100)) AS PictureImage, 
               n.rowno 
          FROM PSALES p 
               RIGHT OUTER JOIN PRODUCTS t 
                 ON p.ProductID = t.UniqueID 
               INNER JOIN PeriodNumbers n 
                 ON p.periodno = n.rowno) AS s PIVOT (MAX(periodsales ) FOR 
       rowno IN ( 
       [1], [2], [3], [4], [5])) AS PVT; 

DROP TABLE #tmp_period  

Open in new window

0
 
miketonnyAuthor Commented:
thanks,i located the problem

in my query i used "(SELECT p.periodno, PeriodSales, t.uniqueid.....) AS s
if i took out p.periodno then it returns me the correct results, whereas if i leave it in the select, it returns me with duplicates.

Do you have a clue why this happens?
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now