SQL Pivot code

I get asked all the time if I know how to PIVOT SQL data. I do, but I've never used the PIVOT keyword. The problem is that usually the query needs to be put in a report or a SmartList, and PIVOT will return a varying amount of columns. We need a fixed number of columns in reporting. 

The technique below will achieve that.

This is not 'fringe' code... you'll use this all the time. Take the time to read and understand. It's short. 

The sample will run as-is, cut and paste into SQL

 

Related Articles

... and you 'll find more on the SQL (General) Menu

declare @PA41302 table (PAPROJECTNUMBER VARCHAR(20), SGMTNUMB INT, SGMTNAME VARCHAR(20),SGMNTID INT)
INSERT INTO @PA41302(PAPROJECTNUMBER, SGMTNUMB, SGMTNAME, SGMNTID) VALUES ('PROJ01',1 ,'Main','5000')
INSERT INTO @PA41302(PAPROJECTNUMBER, SGMTNUMB, SGMTNAME, SGMNTID) VALUES ('PROJ01',2,'Cost Centre','400')
INSERT INTO @PA41302(PAPROJECTNUMBER, SGMTNUMB, SGMTNAME, SGMNTID) VALUES ('PROJ01',3,'Product','110')
INSERT INTO @PA41302(PAPROJECTNUMBER, SGMTNUMB, SGMTNAME, SGMNTID) VALUES ('PROJ01',4,'Region','100')
INSERT INTO @PA41302(PAPROJECTNUMBER, SGMTNUMB, SGMTNAME, SGMNTID) VALUES ('PROJ02',1,'Main','5000')
INSERT INTO @PA41302(PAPROJECTNUMBER, SGMTNUMB, SGMTNAME, SGMNTID) VALUES ('PROJ02',2,'Cost Centre','400')
INSERT INTO @PA41302(PAPROJECTNUMBER, SGMTNUMB, SGMTNAME, SGMNTID) VALUES ('PROJ02',3,'Product','110')
INSERT INTO @PA41302(PAPROJECTNUMBER, SGMTNUMB, SGMTNAME, SGMNTID) VALUES ('PROJ02',4,'Region','100')
INSERT INTO @PA41302(PAPROJECTNUMBER, SGMTNUMB, SGMTNAME, SGMNTID) VALUES ('PROJ03',1,'Main','5000')
INSERT INTO @PA41302(PAPROJECTNUMBER, SGMTNUMB, SGMTNAME, SGMNTID) VALUES ('PROJ03',2,'Cost Centre','400')
INSERT INTO @PA41302(PAPROJECTNUMBER, SGMTNUMB, SGMTNAME, SGMNTID) VALUES ('PROJ03',3,'Product','610')
INSERT INTO @PA41302(PAPROJECTNUMBER, SGMTNUMB, SGMTNAME, SGMNTID) VALUES ('PROJ03',4,'Region','100')
 
 
select
        p.PAPROJECTNUMBER,
        max(case when SGMTNUMB = 1 then SGMNTID else null end) as Main,
        max(case when SGMTNUMB = 2 then SGMNTID else null end) as CostCentre,
        max(case when SGMTNUMB = 3 then SGMNTID else null end) as Product,
        max(case when SGMTNUMB = 4 then SGMNTID else null end) as Region
    from @PA41302 p
    group by p.PAPROJECTNUMBER

Here's another that comes up all the time, PIVOTing pay codes. Ideally, there should not be anything in the 'Other' column. If you get data there, you need to add data columns. The 'Other' is just there so that you'll know if someone has added new data (in this case, pay codes) that you didn't know about. 

This code will run against the DYNAMICS GP TWO database

select employid,
        sum(case when payrolcd = 'holi' then uprtrxam else 0 end) as holi,
        sum(case when payrolcd = 'SALY   ' then uprtrxam else 0 end) as SALY,
        sum(case when payrolcd = 'INS2   ' then uprtrxam else 0 end) as INS2,
        sum(case when payrolcd = 'MED    ' then uprtrxam else 0 end) as MED,
        sum(case when payrolcd = 'INS    ' then uprtrxam else 0 end) as INS,
        sum(case when payrolcd = 'HOUR    ' then uprtrxam else 0 end) as [HOUR],
        sum(case when not payrolcd in ('INS','HOUR','MED','INS2','SALY','holi') then uprtrxam else 0 end) as Other
    from upr30300
    group by employid  

 

 

RealWorldCode gives developers practical, real‑world solutions with clean, working code — no fluff, no theory, just answers.
Links
Home
Knowledge Areas
Sitemap
Contact
Et cetera
Privacy Policy
Terms and Conditions
Cookie Preferences