January 4, 2013 at 3:52 am
Hi, I've managed to build a Crosstab query in SQL 2008 (not doing it in the client as it needs to be automated for some reporting tools) and it works fine.
However, I'm trying to now convert and Access Crosstab to SQL that has text in the value section. ie Column header = Week Numbers, Row Header = Stockcode, Values = Supplier Reference (Text)
When I run it I should get around 5000 rows, however I actually get 6000 rows as where a Stockcode has results in more than one column it shows them as seperate rows, duplicating rows throughout the crosstab. I should only have one record per Stockcode.
The code is currently as follows:
SELECT
StockCode,
MIN(CASE WHEN [Week] = '1' THEN [Supplier] END) AS [1],
MIN(CASE WHEN [Week] = '2' THEN [Supplier] END) AS [2],
MIN(CASE WHEN [Week] = '3' THEN [Supplier] END) AS [3],
MIN(CASE WHEN [Week] = '4' THEN [Supplier] END) AS [4],
MIN(CASE WHEN [Week] = '5' THEN [Supplier] END) AS [5],
MIN(CASE WHEN [Week] = '6' THEN [Supplier] END) AS [6],
MIN(CASE WHEN [Week] = '7' THEN [Supplier] END) AS [7],
MIN(CASE WHEN [Week] = '8' THEN [Supplier] END) AS [8],
MIN(CASE WHEN [Week] = '9' THEN [Supplier] END) AS [9],
MIN(CASE WHEN [Week] = '10' THEN [Supplier] END) AS [10],
MIN(CASE WHEN [Week] = '11' THEN [Supplier] END) AS [11],
MIN(CASE WHEN [Week] = '12' THEN [Supplier] END) AS [12],
MIN(CASE WHEN [Week] = '13' THEN [Supplier] END) AS [13],
MIN(CASE WHEN [Week] = '14' THEN [Supplier] END) AS [14],
MIN(CASE WHEN [Week] = '15' THEN [Supplier] END) AS [15],
MIN(CASE WHEN [Week] = '16' THEN [Supplier] END) AS [16],
MIN(CASE WHEN [Week] = '17' THEN [Supplier] END) AS [17],
MIN(CASE WHEN [Week] = '18' THEN [Supplier] END) AS [18],
MIN(CASE WHEN [Week] = '19' THEN [Supplier] END) AS [19],
MIN(CASE WHEN [Week] = '20' THEN [Supplier] END) AS [20],
MIN(CASE WHEN [Week] = '21' THEN [Supplier] END) AS [21],
MIN(CASE WHEN [Week] = '22' THEN [Supplier] END) AS [22],
MIN(CASE WHEN [Week] = '23' THEN [Supplier] END) AS [23],
MIN(CASE WHEN [Week] = '24' THEN [Supplier] END) AS [24],
MIN(CASE WHEN [Week] = '25' THEN [Supplier] END) AS [25],
MIN(CASE WHEN [Week] = '26' THEN [Supplier] END) AS [26],
MIN(CASE WHEN [Week] = '27' THEN [Supplier] END) AS [27],
MIN(CASE WHEN [Week] = '28' THEN [Supplier] END) AS [28],
MIN(CASE WHEN [Week] = '29' THEN [Supplier] END) AS [29],
MIN(CASE WHEN [Week] = '30' THEN [Supplier] END) AS [30],
MIN(CASE WHEN [Week] = '31' THEN [Supplier] END) AS [31],
MIN(CASE WHEN [Week] = '32' THEN [Supplier] END) AS [32],
MIN(CASE WHEN [Week] = '33' THEN [Supplier] END) AS [33],
MIN(CASE WHEN [Week] = '34' THEN [Supplier] END) AS [34],
MIN(CASE WHEN [Week] = '35' THEN [Supplier] END) AS [35],
MIN(CASE WHEN [Week] = '36' THEN [Supplier] END) AS [36],
MIN(CASE WHEN [Week] = '37' THEN [Supplier] END) AS [37],
MIN(CASE WHEN [Week] = '38' THEN [Supplier] END) AS [38],
MIN(CASE WHEN [Week] = '39' THEN [Supplier] END) AS [39],
MIN(CASE WHEN [Week] = '40' THEN [Supplier] END) AS [40],
MIN(CASE WHEN [Week] = '41' THEN [Supplier] END) AS [41],
MIN(CASE WHEN [Week] = '42' THEN [Supplier] END) AS [42],
MIN(CASE WHEN [Week] = '43' THEN [Supplier] END) AS [43],
MIN(CASE WHEN [Week] = '44' THEN [Supplier] END) AS [44],
MIN(CASE WHEN [Week] = '45' THEN [Supplier] END) AS [45],
MIN(CASE WHEN [Week] = '46' THEN [Supplier] END) AS [46],
MIN(CASE WHEN [Week] = '47' THEN [Supplier] END) AS [47],
MIN(CASE WHEN [Week] = '48' THEN [Supplier] END) AS [48],
MIN(CASE WHEN [Week] = '49' THEN [Supplier] END) AS [49],
MIN(CASE WHEN [Week] = '50' THEN [Supplier] END) AS [50],
MIN(CASE WHEN [Week] = '51' THEN [Supplier] END) AS [51],
MIN(CASE WHEN [Week] = '52' THEN [Supplier] END) AS [52],
MIN(CASE WHEN [Week] = '53' THEN [Supplier] END) AS [53]
FROM
(
SELECT dbo.Products.StockCode, [Week], SPMCrosstabSupplierPrepare3.Supplier
FROM
(
SELECT StockCode, [Week], CountAccRef, CASE WHEN [CountAccRef] = 1 THEN FirstAccRef ELSE 'Multiple' END AS Supplier
FROM
(
SELECT StockCode, [Week], COUNT(AccRef) AS CountAccRef, MIN(AccRef) AS FirstAccRef
FROM
(
SELECT dbo.[Main Groups].MainGroup AS [Group], AccRef, dbo.[P/O Header].OrdNo, dbo.[P/O Details].StockCode, DATEPART(ww,dbo.[P/O Header].[Due Date]) AS [Week], dbo.[P/O Header].[Due Date], dbo.[P/O Header].[Complete ?]
FROM dbo.[P/O Header]
INNER JOIN dbo.[P/O Details] ON dbo.[P/O Header].OrdNo = dbo.[P/O Details].OrdNo
INNER JOIN dbo.[Main Groups] INNER JOIN dbo.Groups ON dbo.[Main Groups].MainGroup = dbo.Groups.Main
INNER JOIN dbo.Products ON dbo.Groups.GroupCode = dbo.Products.[Group] ON dbo.[P/O Details].StockCode = dbo.Products.StockCode
WHERE (dbo.[P/O Header].[Complete ?] = 0) AND (dbo.[P/O Header].[Due Date] IS NOT NULL)
GROUP BY MainGroup, dbo.[P/O Details].StockCode, AccRef, dbo.[P/O Header].OrdNo, [Due Date], [Complete ?]
) SPMCrosstabSupplierPrepare
GROUP BY SPMCrosstabSupplierPrepare.StockCode, [Week]
) SPMCrosstabSupplierPrepare2
GROUP BY StockCode, SPMCrosstabSupplierPrepare2.[Week], CountAccRef, FirstAccRef
) SPMCrosstabSupplierPrepare3
RIGHT OUTER JOIN Products
ON SPMCrosstabSupplierPrepare3.StockCode = dbo.Products.StockCode
) SPMCrosstabSupplierPrepare4
GROUP BY StockCode, [Week]
ORDER BY StockCode
January 4, 2013 at 4:04 am
Hi ,
this is because you are also grouping on WEEK
GROUP BY StockCode, [Week]
Remove that and you should be ok
Dave
January 4, 2013 at 4:55 am
Always the way, a fresh set of eyes gets it straight away!!
Thanks for your help, all working now.
January 4, 2013 at 8:48 am
It's easier to read, when rewritten like this:
SELECT
p.StockCode,
MIN(CASE WHEN orders.[Week] = 1 THEN orders.Supplier END) AS [1],
.
.
MIN(CASE WHEN orders.[Week] = 53 THEN orders.Supplier END) AS [53]
FROM dbo.Products p
INNER JOIN dbo.Groups g
ON g.GroupCode = p.[Group]
INNER JOIN dbo.[Main Groups] m
ON m.MainGroup = g.Main
LEFT JOIN (
SELECT
d.StockCode,
[Week] = DATEPART(ww,h.[Due Date]),
Supplier = CASE WHEN COUNT(h.AccRef) = 1 THEN MIN(h.AccRef) ELSE 'Multiple' END
FROM dbo.[P/O Header] h
INNER JOIN dbo.[P/O Details] d
ON d.OrdNo = h.OrdNo
WHERE (h.[Complete ?] = 0)
AND (h.[Due Date] IS NOT NULL)
GROUP BY d.StockCode, DATEPART(ww,h.[Due Date])
) orders
ON orders.StockCode = p.StockCode
GROUP BY p.StockCode
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply