Pivot Query

Question

Post reply

Pivot Query

stephen.aa

SSC Eights!

Points: 815
More actions
November 3, 2021 at 4:44 pm

#3947579
Hello:
I am trying to get a count of columns for values within a given date range. I am using this example, but unable to get results.
https://docs.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-2017

There are three transaction types, Receipt (value of 1), Issue (Value of 2) and Adjustment (Value of 3). How many are valued at 1, 2 and 3 in respective columns is the goal, at least for now. Ultimately, I am looking to divide this out by Month as well, so that within the given date range, how many of each type are in each month?
This is what I have so far. Any help would be greatly appreciated.
```
SELECT TOP (1000000)
[PartTransactions].[imtPartID]
,[1] AS Receipt
,[2] AS Issue
,[3] AS Adjustment
FROM
(SELECT [M1_SU].[dbo].[PartTransactions].[imtTransactionDate], [M1_SU].[dbo].[PartTransactions].[imtTransactionType], [M1_SU].[dbo].[PartTransactions].[imtPartID]
FROM [M1_SU].[dbo].[PartTransactions]) P
PIVOT
COUNT ([M1_SU].[dbo].[PartTransactions].[imtTransactionDate])
FOR impPartID IN
( [1], [2], [3] )
) AS pvt
WHERE [imtPartID] = '7000917' AND imtTransactionDate BETWEEN '5/1/2019 3:13:52 PM' AND '10/29/2021 3:13:52 PM'
ORDER BY pvt.impTransactionDate DESC
```
- This topic was modified 3 years, 1 month ago by stephen.aa.
Steve Anderson

Viewing 15 posts - 1 through 15 (of 25 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246988 More actions · Answer 1

What results do you get when you run that?

This reply was modified 3 years, 1 month ago by Phil Parkin.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

stephen.aa SSC Eights! Points: 815 More actions · Answer 2

stephen.aa

SSC Eights!

Points: 815

November 3, 2021 at 5:22 pm

#3947586

Pivot Query

Steve Anderson

stephen.aa SSC Eights! Points: 815 More actions · Answer 3

I added a pic. Unfortunately, lots of red underlines means I cannot get results.

Steve Anderson

Jonathan AC Roberts SSCoach Points: 18377 More actions · Answer 4

I can't see that your query will work?

You are missing a bracket after PIVOT.

If you could supply your input data and the output expected it would be easier to understand.

Jonathan AC Roberts SSCoach Points: 18377 More actions · Answer 5

I think this might be what you want:

SELECT pt.[imtPartID],
       COUNT(CASE WHEN pt.[imtPartID] = 1 THEN 'x' ELSE NULL END) AS Receipt,
       COUNT(CASE WHEN pt.[imtPartID] = 2 THEN 'x' ELSE NULL END) AS Issue,
       COUNT(CASE WHEN pt.[imtPartID] = 3 THEN 'x' ELSE NULL END) AS Adjustment
  FROM [M1_SU].[dbo].[PartTransactions] pt
 WHERE pt.[imtPartID] = '7000917' 
   AND pt.imtTransactionDate BETWEEN '5/1/2019 3:13:52 PM' AND '10/29/2021 3:13:52 PM'
 GROUP BY pt.[imtPartID];

stephen.aa SSC Eights! Points: 815 More actions · Answer 6

This is good, except that all the values are zero. I know for certain there are nine records with valid data here. Am I supposed to do something with the 'x'?

Thank you!

Steve Anderson

Jonathan AC Roberts SSCoach Points: 18377 More actions · Answer 7

the 'x' was just there so it's included in the count. You could put anything that's not null.

This is also the equivalent:

SELECT pt.[imtPartID],
       SUM(CASE WHEN pt.[imtPartID] = 1 THEN 1 ELSE 0 END) AS Receipt,
       SUM(CASE WHEN pt.[imtPartID] = 2 THEN 1 ELSE 0 END) AS Issue,
       SUM(CASE WHEN pt.[imtPartID] = 3 THEN 1 ELSE 0 END) AS Adjustment
  FROM [M1_SU].[dbo].[PartTransactions] pt
 WHERE pt.[imtPartID] = '7000917' 
   AND pt.imtTransactionDate BETWEEN '20190501 15:13:52' AND '20212910 15:13:52'
 GROUP BY pt.[imtPartID];

Unless you supply some data there is no way for me to see why it is not working.

stephen.aa SSC Eights! Points: 815 More actions · Answer 8

Jonathan, Thanks again!

SELECT TOP (1000000) [PartTransactions].[imtTransactionDate]
,[PartTransactions].[imtTransactionType]
,[PartTransactions].[imtPartID]
FROM [M1_SU].[dbo].[PartTransactions]
WHERE [imtPartID] = '7000917' AND imtTransactionDate BETWEEN '5/1/2019 3:13:52 PM' AND '10/29/2021 3:13:52 PM'
ORDER BY imtTransactionDate DESC

Ok, here's some data for the above query:

Select Query

This reply was modified 3 years, 1 month ago by stephen.aa.

Steve Anderson

stephen.aa SSC Eights! Points: 815 More actions · Answer 9

I think this does it.

SELECT pt.[imtPartID],
SUM(CASE WHEN pt.[imtTransactionType] = 1 THEN 1 ELSE 0 END) AS Receipt,
SUM(CASE WHEN pt.[imtTransactionType] = 2 THEN 1 ELSE 0 END) AS Issue,
SUM(CASE WHEN pt.[imtTransactionType] = 3 THEN 1 ELSE 0 END) AS Adjustment
FROM [M1_SU].[dbo].[PartTransactions] pt
WHERE pt.[imtPartID] = '7000917'
AND pt.imtTransactionDate BETWEEN '5/1/2019 3:13:52 PM' AND '10/29/2021 3:13:52 PM'
GROUP BY pt.[imtPartID];

This reply was modified 3 years, 1 month ago by stephen.aa.

Steve Anderson

Phil Parkin SSC Guru Points: 246988 More actions · Answer 10

You've been here long enough to know about

a) Putting T-SQL code in a code block and

b) Providing data in a consumable format (which can be pasted into SSMS)

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

stephen.aa SSC Eights! Points: 815 More actions · Answer 11

Just one more thing... The example above spans 29 months. Is there a good way to separate it out to place three columns for each month within the date range?

Steve Anderson

stephen.aa SSC Eights! Points: 815 More actions · Answer 12

Phil:

Happy to comply, but I'm not sure I know how to do those things. I will try to figure it out.

Other sites have controls for these things, which I do not see here.

This reply was modified 3 years, 1 month ago by stephen.aa.

Steve Anderson

Phil Parkin SSC Guru Points: 246988 More actions · Answer 13

stephen.aa wrote:

Phil:
Happy to comply, but I'm not sure I know how to do those things. I will try to figure it out.
Other sites have controls for these things, which I do not see here.

Here is a random post which does both things:

https://www.sqlservercentral.com/forums/topic/datediff-with-getweek-worth-of-data#post-3942774

Click on Insert/edit code sample to format your code.

This reply was modified 3 years, 1 month ago by Phil Parkin.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

ratbak Ten Centuries Points: 1382 More actions · Answer 14

a) Putting T-SQL code in a code block

InsertCodeSample

b) Providing data in a consumable format (which can be pasted into SSMS)

This just means (again using code sample control) copying & pasting SQL statements that insert data into the tables in the DDL statements -- e.g.,

INSERT INTO [M1_SU].[dbo].[PartTransactions] 
    (imtTransactionDate, imtTransactionType, imtPartType)
VALUES ('2020-12,31 19:00:09',3,7000917),
       ('2020-09-29 12:05:25',1,700917),
       ('2020-09-28 06:56:42',2,7000917,
       ('2019-12-31 23:59:30',3,7000917),
       ('2019-09-07 17:38:34',3,7000917),
       ('2019-09-07 17:38:34',3,7000917),
       ('2019-09-07 17:16:44',3,7000917);