December 22, 2016 at 12:27 am
Hi
IDKEYQuantityContainerPRODUCTGRPNAME
120161125111 A
120161125111 A
120161125111 NULL
120161125111 C
120161125112 P
120161125111 P
120161125113 C
120161125111 NULL
120161125112 A
Required Output
IDKEY ACOunt CCountPCountUnCount AQtyPQty CQty UnQty
120161125 2 2 2 1 3 2 2 2
ACount/CCount/PCount== Distinct Count of Container Against ProductGroup name
UnCount==Distinct Count of Container Against ProductGroup name=NULL
AQty/PQty/CQty ==Count of Quantity Against ProductGroup name
UnQty==Count of Quantity Against ProductGroup name=NULL
Please help me out
December 22, 2016 at 3:20 am
Let's tidy up that statement of your (there wasn't a question), and actually give people a table they can read (your expected data is terribly misaligned):
USE DevTestDB;
GO
-- I have guessed your data types here, as I don't have any DDL. Depending on what they really are, this could affect the solution I post later and cause it to not work.
CREATE TABLE #Sample ([ID] INT, --If this is an "ID", why do they all have an ID of 1? This isn't an ID.
INT, --Try to avoid keywords as column names, you'll have to always use square brackets to refer to this field
Quantity INT,
Container INT,
ProductGrpName CHAR(2));--EDIT changed to VARCHAR(2) to handle 'Un' (if you do have it as CHAR(1), then you will need to change my solution in my below post.
GO
INSERT INTO #Sample
VALUES (1, 20161125, 1, 11, 'A'),
(1, 20161125, 1, 11, 'A'),
(1, 20161125, 1, 11, NULL),
(1, 20161125, 1, 11, 'C'),
(1, 20161125, 1, 12, 'P'),
(1, 20161125, 1, 11, 'P'),
(1, 20161125, 1, 13, 'C'),
(1, 20161125, 1, 11, NULL),
(1, 20161125, 1, 12, 'A');
SELECT *
FROM #Sample;
--Expected Output:
SELECT 1 AS ID,
20161125 AS ,
2 AS ACount,
2 AS CCount,
2 AS PCount,
1 AS UnCount,
3 AS AQty,
2 AS PQty,
2 As CQty,
2 AS UnQty;
DROP TABLE #Sample;
Expected results:
ID KEY ACount CCount PCount UnCount AQty PQty CQty UnQty
----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
1 20161125 2 2 2 1 3 2 2 2
I also, didn't notice until after starting to wonder what on earth is going on with the maths, but your post does have the formulas for your fields, but it doesn't really explain it. you simply pasted a formula and "help me". This isn't really helpful.
Please actually ask a question, rather than just giving us data and we have to guess at. perhaps a better attempt would be something along the lines of:
I need to create a query on the above data set, which will provide the expected result set.
The Qty and Count fields are formula derived, rather than simple counts and sums, and this needs to be reflected. The formulas ar as follows:
Count Columns are a Distinct Count of Container Against ProductGroup name (the Un prefix denotes a NULL value)
Qty Columns are a Count of Quantity Against ProductGroup name (the Un prefix denotes a NULL value)
I am not sure what the SQL would be to get these results, otherwise I would have supplied this
I am looking to use a PIVOT, as I have more Product Groups than in my sample data, is anyone able to provide me with a SQL statement to get the expected results from my sample data?
I've "bolded" your Qty logic, as I assume you mean you want a sum. If you're going to count the Quantity Rows, then it's going to be the same as your Count Column. For example, if you were to count a column with the values 1, 2, 4 then you're COUNT is 3, as there's 3 rows. Your SUM, however, would be 7.
I'll give you an answer in a separate post once I'm done. 😎
EDIT: Fixed sample insert. This is why a SQL statement is so much better...
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
December 22, 2016 at 4:18 am
This is not dynamic, if you have more than just products A, C, P and NULL, but as I said above, I have little information to go on. if you do have a dynamic amount of products, you will need to likely convert this to D-SQL to get your applicable Columns out (or add them all in if you always want them displayed).
I wouldn't really say this is pretty either, so if others have a better answer, go for it.
SELECT C.ID, C.,
C.ACount, C.CCount, C.PCount, C.UnCount,
Q.AQty, Q.CQty, Q.PQty, Q.UnQty
FROM (SELECT PvtC.ID, PvtC., A AS ACount, C AS CCount, P AS PCount, Un AS UnCount
FROM (SELECT DISTINCT ID, , Container, ISNULL(ProductGrpName, 'Un') AS ProductGroupName
FROM #Sample ) S
PIVOT (COUNT (Container)
FOR ProductGroupName IN ([A], [C], [P], [Un])
) AS PvtC) C
JOIN (SELECT PvtQ.ID, PvtQ., A AS AQty, C AS CQty, P AS PQty, Un AS UnQty
FROM (SELECT [ID], [Key], ISNULL(ProductGrpName, 'Un') AS ProductGroupName, Quantity
FROM #Sample) S
PIVOT (SUM(Quantity)
FOR ProductGroupName IN ([A], [C], [P], [Un])
) AS PvtQ) Q ON C.ID = Q.ID AND C. = Q.[Key];
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
December 23, 2016 at 1:26 am
@thom-2 a:Thank you for formatting my question .
That is what I exactly want . Could pls help me out
Actually My data will be huge So When I use the query which You have posted it might have performance bits .
Is their any alternative way without joins /pivots.
Thank you
December 23, 2016 at 1:53 am
greeshatu (12/23/2016)
@Thom a:Thank you for formatting my question .That is what I exactly want . Could pls help me out
Actually My data will be huge So When I use the query which You have posted it might have performance bits .
Is their any alternative way without joins /pivots.
Thank you
Depends, are you likely to have other options than A/C/P and NULL? I went with PIVOT, as that is what you asked for in your topic header.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
December 23, 2016 at 4:39 am
greeshatu (12/23/2016)
@Thom a:Thank you for formatting my question .That is what I exactly want . Could pls help me out
Actually My data will be huge So When I use the query which You have posted it might have performance bits .
Is their any alternative way without joins /pivots.
Thank you
A preaggregate step may help performance:
;WITH PreAggregatedData AS (
SELECT
ID,
,
ProductGrpName,
ProductGrpCnt = COUNT(*),
ProductGrpCSum = SUM(Quantity)
FROM #Sample
GROUP BY ID, , ProductGrpName
)
SELECT
ID,
,
ACount = SUM(CASE WHEN ProductGrpName = 'A' THEN ProductGrpCnt ELSE 0 END),
CCount = SUM(CASE WHEN ProductGrpName = 'C' THEN ProductGrpCnt ELSE 0 END),
PCount = SUM(CASE WHEN ProductGrpName = 'P' THEN ProductGrpCnt ELSE 0 END),
UnCount = SUM(CASE WHEN ProductGrpName IS NULL THEN ProductGrpCnt ELSE 0 END),
AQty = SUM(CASE WHEN ProductGrpName = 'A' THEN ProductGrpCSum ELSE 0 END),
PQty = SUM(CASE WHEN ProductGrpName = 'C' THEN ProductGrpCSum ELSE 0 END),
CQty = SUM(CASE WHEN ProductGrpName = 'P' THEN ProductGrpCSum ELSE 0 END),
UnQty = SUM(CASE WHEN ProductGrpName IS NULL THEN ProductGrpCSum ELSE 0 END)
FROM PreAggregatedData
GROUP BY ID, ;
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 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply