SQL Pivot?

Question

Post reply

SQL Pivot?

ErwinB

SSC Enthusiast

Points: 180
More actions
November 2, 2020 at 5:17 pm

#3803606

Hi,
I have this special requirement as a query result, and I wonder if SQL PIVOT can be used to achieve this.
Let's say that we have the following table:
Is it possible to to create a SQL query that creates a table like below?
Note that the number of departments as well as the number of genders is not fixed, and the number of people is each combination of department/gender can vary as in the example above.
Thanks for any suggestion on how to achieve this.
Erwin

Viewing 11 posts - 1 through 10 (of 10 total)

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

Steve Collins SSCrazy Points: 2159 More actions · Answer 1

Are the names within departments in any particular order?

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

ErwinB SSC Enthusiast Points: 180 More actions · Answer 2

In the "raw data" they can be in any order, but in the final result they have to be in alphabetical order.

ScottPletcher SSC Guru Points: 101103 More actions · Answer 3

Since PIVOT requires that you put specific values in the PIVOT, you'd at least have to use dynamic SQL to make a PIVOT work.

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".

ErwinB SSC Enthusiast Points: 180 More actions · Answer 4

Thanks for the input Scott. I'm not hung up on using PIVOT, so if there is another way, I'd be happy to hear. What I'm basically looking for is a VIEW that returns the second table using the first as input.

Michael L John One Orange Chip Points: 27056 More actions · Answer 5

These articles may help:

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

https://www.sqlservercentral.com/articles/cross-tabs-and-pivots-part-2-dynamic-cross-tabs

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/

Steve Collins SSCrazy Points: 2159 More actions · Answer 6

A non-dynamic crosstab query to produce the output above could be something like this

with names_cte as (
    select *, row_number() over (partition by department, gender
                                 order by [Name]) rn
    from YourTable)
select department, gender,
       max(case when rn=1 then [Name] else null end) name1,
       max(case when rn=2 then [Name] else null end) name2,
       max(case when rn=3 then [Name] else null end) name3
from names_cte
group by department, gender
order by department, gender;

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

ErwinB SSC Enthusiast Points: 180 More actions · Answer 7

That is great Steve! Gives me exactly what I'm looking for. Thanks!

One more question though: I limited the maximum number of names per Department/Gender combi to 3 in the example. In reality I don't know up front how many there can be. Is it possible to make that part flexible using the above approach?

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 8

Either continue the 'max' out to the maximum number of possible members in a department - or you have to use dynamic SQL to determine the number of maximum members.

with names_cte as (
    select *, row_number() over (partition by department, gender
                                 order by [Name]) rn
    from YourTable)
select department, gender,
       max(case when rn=1 then [Name] else null end) name1,
       max(case when rn=2 then [Name] else null end) name2,
       max(case when rn=3 then [Name] else null end) name3,
       ...
       max(case when rn-50 then [Name] else null end) name50
from names_cte
group by department, gender
order by department, gender;

A dynamic solution would only be useful if you are filtering by department, or a subset of departments.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

ErwinB SSC Enthusiast Points: 180 More actions · Answer 9

I will go with the "hardcoded" approach as suggested by Jeffrey for now. 40 or 50 columns should work fine, but will investigate the dynamic option for ultimate flexibility at a later stage.

Thanks to all for your help!

frederico_fonseca SSCoach Points: 16156 More actions · Answer 10

I would note that if the purpose of this query is just to output to a file for consumption by other system then using a different method would probably be better and would not require dynamic sql