I have been struggling with a MSSQL Server query for the past 4 days. Whatever I tried I failed. If its possible can I get hand here.
This is the report I want to take from query:
Playstation Laptop Phone Macbook Total
-------------------------------------------------------------
England 1x 257$ 0x 0$ 0x 0$ 0x 0$ 1x 257$
Sweden 0x 0$ 1x 433$ 1x 424$ 0x 0$ 2x 857$
Russia 2x 472$ 0x 0$ 0x 0$ 0x 0$ 2x 472$
Italy 0x 0$ 0x 0$ 0x 0$ 0x 0$ 0x 0$
Total 3x 729$ 1x 433$ 1x 424$ 0x 0$ 5x 1586$
These are my tables:
Country table:
Id Name
----------------------------------------
1 England
2 Sweden
3 Russia
4 Italy
Items table:
Id Name
----------------------------------------
1 Laptop
2 Phone
3 Playstation
4 Macbook
Pre-Request table:
Id countryId ItemId blablabla
----------------------------------------
1 1 3 blablabla
2 2 1 blablabla
3 2 2 blablabla
4 3 3 blablabla
5 3 3 blablabla
6 2 3 blablabla
Offers table:
Id Price Blablabla
----------------------------------------
18 257$ Blablabla
19 151$ Blablabla
20 424$ Blablabla
21 433$ Blablabla
22 321$ Blablabla
Request table:
Id preReqId requestStatus winOfferId
---------------------------------------------
44 1 3 18
11 2 4 21
53 3 4 20
87 4 3 22
43 5 3 19
45 6 2 Null
Blablabla columns and rows don't matter. Only catch with reporting table query is I must use requestStatus > 2 to take legit report and even there is no order from "Italy" or no order for "Macbook" they will be in report anyway. Thank you
I am inserting into a temporary table to make the query more readable. I have written the logic inside.
;WITH CTE AS
(
SELECT DISTINCT I.NAME ITEMNAME,C.NAME COUNTRYNAME
,CAST(REPLACE(TAB.PRICE,'$','')AS INT)PRICE
,COUNT(CASE WHEN TAB.PRICE IS NOT NULL THEN I.NAME END) OVER(PARTITION BY C.NAME,I.NAME) CNTITEM
FROM [#Pre-Request] PR
LEFT JOIN #Items I ON PR.ITEMID=I.ID
LEFT JOIN #COUNTRY C ON PR.COUNTRYID = C.ID
OUTER APPLY
(
SELECT R.preReqId,R.winOfferId,O.PRICE
FROM #Request R
JOIN #Offers O ON R.winOfferId=O.Id
WHERE PR.ID=R.preReqId
)TAB
UNION
-- Used to select Item name and country that are not in Pre-request table and other tables
SELECT I.NAME ,C.NAME ,NULL,0
FROM #Items I
CROSS JOIN #COUNTRY C
)
,CTE2 AS
(
-- Find the sum for number of items
SELECT DISTINCT ISNULL(ITEMNAME,'TOTAL')ITEMNAME,ISNULL(COUNTRYNAME,'TOTAL')COUNTRYNAME,
SUM(PRICE)PRICE
FROM CTE
GROUP BY ITEMNAME,COUNTRYNAME
WITH CUBE
)
,CTE3 AS
(
-- Find the sum of PRICE
SELECT DISTINCT ISNULL(ITEMNAME,'TOTAL')ITEMNAME,ISNULL(COUNTRYNAME,'TOTAL')COUNTRYNAME--,CNTITEM
,SUM(CNTITEM)CNTITEM
FROM
(
SELECT DISTINCT ITEMNAME,COUNTRYNAME,CNTITEM
FROM CTE
)TAB
GROUP BY ITEMNAME,COUNTRYNAME
WITH CUBE
)
SELECT C2.*,C3.CNTITEM,
CAST(C3.CNTITEM AS VARCHAR(20))+'x'+' ' + CAST(C2.PRICE AS VARCHAR(20))+'$' NEWCOL
INTO #NEWTABLE
FROM CTE2 C2
JOIN CTE3 C3 ON C2.COUNTRYNAME=C3.COUNTRYNAME AND C2.ITEMNAME=C3.ITEMNAME
Get columns for dynamic pivot
DECLARE @cols NVARCHAR (MAX)
SELECT @cols = COALESCE (@cols + ',[' + ITEMNAME + ']', '[' + ITEMNAME + ']')
FROM (SELECT DISTINCT ITEMNAME FROM #NEWTABLE WHERE ITEMNAME<>'TOTAL') PV
ORDER BY ITEMNAME
-- Since we need Total in last column, we append it at last
SELECT @cols += ',[Total]'
Now pivot the query
DECLARE @query NVARCHAR(MAX)
SET @query = 'SELECT COUNTRYNAME,' + @cols + ' FROM
(
SELECT DISTINCT ITEMNAME,COUNTRYNAME,ISNULL(NEWCOL,''0x 0$'')NEWCOL
FROM #NEWTABLE
) x
PIVOT
(
MIN(NEWCOL)
FOR ITEMNAME IN (' + @cols + ')
) p
ORDER BY CASE WHEN (COUNTRYNAME=''Total'') THEN 1 ELSE 0 END,COUNTRYNAME'
EXEC SP_EXECUTESQL @query