Need to Pivot String values in SQL server

Question

I have table with values, described as:

Occupation String	Name String
Developer	A
Developer	B
Designer	X
Coder	Y
Coder	Z

I need values in pivot format as:

Designer	Developer	Coder
X	A	Y
Null	B	Z

Can anyone help on this ?

Thanks in advance

How does your data know, that Y is bound to X and A while Z is bound to B and does not have a Designer? — Gottfried Lesigang
– Gottfried Lesigang, Commented Sep 15, 2016 at 7:27
I would guess you want to use a row number function to order the values (partitioned by the operation string) then pivot the results... — ZLK
– ZLK, Commented Sep 15, 2016 at 7:30
That relation is not there.. data is just sorted alphabeticaly.. after pivot — Ashok Gupta
– Ashok Gupta, Commented Sep 15, 2016 at 7:30
This design is - uhm - weak... Your table does not have any implicit order. The relation between your values will be near to random (at least unpredictable...) — Gottfried Lesigang
– Gottfried Lesigang, Commented Sep 15, 2016 at 7:42

gofr1 · Accepted Answer · 2021-07-12 05:35:52Z

8

The basic PIVOT with ROW_NUMBER() will do things for you:

SELECT  [Developer],
        [Designer],
        [Coder]
FROM (
    SELECT  *,
            ROW_NUMBER() OVER (PARTITION BY Occupation ORDER BY (SELECT NULL)) RN
    FROM #temp
) as t
PIVOT (
    MAX(Name) FOR Occupation IN ([Developer],[Designer],[Coder])
) as pvt

Output:

Developer   Designer    Coder
A           X           Y
B           NULL        Z

If the number of Occupations may vary then you need dynamic SQL:

DECLARE @columns nvarchar(max),
        @sql nvarchar(max)

SELECT @columns = (
    SELECT DISTINCT ','+QUOTENAME(Occupation)
    FROM #temp
    FOR XML PATH('')
)

SELECT @sql = N'
SELECT  '+STUFF(@columns,1,1,'')+'
FROM (
    SELECT  *,
            ROW_NUMBER() OVER (PARTITION BY Occupation ORDER BY (SELECT NULL)) RN
    FROM #temp
) as t
PIVOT (
    MAX(Name) FOR Occupation IN ('+STUFF(@columns,1,1,'')+')
) as pvt'

EXEC sp_executesql @sql

Note: I have used ORDER BY (SELECT NULL) just to get some random ordering. Better use some actual field for this purpose.

edited Jul 12, 2021 at 5:35

answered Sep 15, 2016 at 7:29

gofr1

16k11 gold badges47 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Gottfried Lesigang Over a year ago

Your ROW_NUMBER() OVER (PARTITION BY Occupation ORDER BY (SELECT NULL)) will define some order, but this is not predictable. I'd say, that this approach - even if it works! - is something one should not do...

gofr1 Over a year ago

@Shnugo instead of (SELECT NULL) OP can use ANY field he need/can. It is just an example. Personally I use such ordering if I don't have any field to do sort with and this is rare thing in our (company) DBs.

Ashok Gupta Over a year ago

Thanks.. U r a savier ! I invested hours in it.. :(

Gottfried Lesigang Over a year ago

Yes, but people don't know about this... You test this, it works, it works a hundred times and suddenly you get something else... As long as you are aware of the fact, that the order you imply is unpredictable, you might do this, but I would not...

gofr1 Over a year ago

My pleasure! As Shnugo commented, better use some other field instead of (SELECT NULL) that gives some random ordering.

Collectives™ on Stack Overflow

Need to Pivot String values in SQL server

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related