Group multiple Transaction Types in 1 Row by Count

Question

Post reply

Group multiple Transaction Types in 1 Row by Count

rjjh78

SSCrazy

Points: 2787

July 29, 2019 at 5:25 pm

#3666917

To all:

I am working in hopes to group records asked on Loc to collectively display transactions by Complete, Pending, Canceled, and Rejected. The change is that the data comes from individual rows. How can I pull this data together in one line?

Example data is below:

-- DROP TABLE #t
CREATE TABLE #t (Yr int, Pd int, Loc int, Complete int, Pending int, Canceled int, Rejected int)
INSERT INTO  #t (Yr, Pd, Loc, Complete, Pending, Canceled, Rejected) VALUES (2019, 1, 100, 5, 0, 0, 0)
INSERT INTO  #t (Yr, Pd, Loc, Complete, Pending, Canceled, Rejected) VALUES (2019, 1, 100, 0, 4, 0, 0)
INSERT INTO  #t (Yr, Pd, Loc, Complete, Pending, Canceled, Rejected) VALUES (2019, 1, 100, 0, 0, 9, 0)
INSERT INTO  #t (Yr, Pd, Loc, Complete, Pending, Canceled, Rejected) VALUES (2019, 1, 100, 0, 0, 0, 1)
INSERT INTO  #t (Yr, Pd, Loc, Complete, Pending, Canceled, Rejected) VALUES (2019, 1, 200, 4, 0, 0, 0)
INSERT INTO  #t (Yr, Pd, Loc, Complete, Pending, Canceled, Rejected) VALUES (2019, 1, 200, 0, 4, 0, 0)
INSERT INTO  #t (Yr, Pd, Loc, Complete, Pending, Canceled, Rejected) VALUES (2019, 1, 300, 0, 0, 1, 0)

 -- Desired Outcome
 
 Yr     PdLocComplete Pending Canceled Rejected
 201911005 4 9  1  
 20191200     4 4 0  0
 20191300     0        0       1        0

Viewing 4 posts - 1 through 4 (of 4 total)

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

Sue_H SSC Guru Points: 90860 More actions · Answer 1

You can sum those values ( Complete, Pending, Canceled, and Rejected) and group by Loc, Pd, Yr.

Sue

Michael L John One Orange Chip Points: 27079 More actions · Answer 2

You can read these 2 articles that will help you complete this

https://www.sqlservercentral.com/articles/cross-tabs-and-pivots-part-1-%e2%80%93-converting-rows-to-columns-1

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

ScottPletcher SSC Guru Points: 101200 More actions · Answer 3

SELECT
    Yr, Pd, Loc,
    SUM(Complete) AS Complete,
    SUM(Pending) AS Pending,
    SUM(Canceled) AS Canceled,
    SUM(Rejected) AS Rejected
FROM #t
GROUP BY Yr, Pd, Loc

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".