SQL Pivot query help

Question

Post reply

SQL Pivot query help

rocky_498

SSCertifiable

Points: 6532

January 30, 2024 at 7:50 am

#4369978

Hi everyone,

I have data to which I want to build a report, In one category how many records are generated to different types?

Here is the sample code:-

DECLARE @TEMP table
(
ID INT,
next_id int,
d_no int,
n_type varchar(50),
d_type varchar(50)

)
INSERT INTO @TEMP
(
    ID,
    next_id,
    d_no,
    n_type,
    d_type
)
SELECT 1,2345,8755,'a_type','a_type'
union ALL
SELECT 1,2345,8744,'a_type','b_type'
union ALL
SELECT 1,564,897,'a_type','b_type'
union ALL
SELECT 1,765,88,'z_type','a_type'
union ALL
SELECT 1,99,8,'z_type','x_type'
union ALL
SELECT 1,66,77,'x_type','x_type'
union ALL
SELECT 1,44,555,'x_type','a_type'

SELECT * FROM @TEMP;

Here I want the result:- Vertical and total data coming from "n_type" and horizontal data coming from "d_type"

Appreciate your help!

Viewing 6 posts - 1 through 5 (of 5 total)

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

Ken McKelvey SSCoach Points: 18856 More actions · Answer 1

SELECT n_type
,COUNT(1) AS Total
,SUM(IIF(d_type = 'a_type', 1, 0)) AS a_type
,SUM(IIF(d_type = 'b_type', 1, 0)) AS b_type
,SUM(IIF(d_type = 'x_type', 1, 0)) AS x_type
FROM @temp
GROUP BY n_type
ORDER BY n_type;

Phil Parkin SSC Guru Points: 246985 More actions · Answer 2

Like this?

SELECT t.n_type
      ,Total = COUNT (1)
      ,a_type = COUNT (IIF(t.d_type = 'a_type', 1, NULL))
      ,b_type = COUNT (IIF(t.d_type = 'b_type', 1, NULL))
      ,x_type = COUNT (IIF(t.d_type = 'x_type', 1, NULL))
FROM @TEMP t
GROUP BY t.n_type
ORDER BY t.n_type;

This reply was modified 10 months, 3 weeks 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

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

The method that both Ken and Phil are using is an arcane method known as a CROSSTAB and is a much more flexible manifestation than a PIVOT.

You can read more about it in the old be still very pertinent article at the following link...

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

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

rocky_498 SSCertifiable Points: 6532 More actions · Answer 4

Thank you, Ken and Phil. This is exactly what I am looking for. I ran it to my real data and it works perfectly.

Thank you, SSC. I will check out that link.

cocotecom Newbie Points: 7 More actions · Answer 5

Having same issue, this post help me a lot. thank you

{https://fr.cocote.com/}[/code]