CODEWITHSUNDEEP
sqlsql-serverpivot
Arjun Arun
Arjun Arun

Reputation: 313

SQL Server - Rows to column based on third column value

thank you so much for your help. I am working with a table with two columns: EmployeeID and Color. I pull these two columns using the following (very simple) code:

Select 
   EmployeeID,
   Color
From 
     dbo.EmployeeID E 
join dbo.Color C on C.EmployeeID = E.EmployeeID

The results come back in the following format. There are several hundred rows with different employeeID, color combinations.:

EmployeeID  Color
123         Blue
123         Green
123         Yellow
234         Blue
234         Green

I would like to pivot the data so that the result looks like this:

EmployeeID  Color1  Color2  Color3
123         Blue    Green   Yellow
234         Blue    Green

I have used the pivot function in the past to aggregate data, but am not able to figure it out this time. I think not needing an aggregate function is throwing me off. If I use either MIN or MAX as the function with PIVOT I don't know how to get all the colors per EmployeeID.

Any help is appreciated, I run into this same situation constantly and would love to know how to navigate.

Thank you!

Upvotes: 1

Views: 173

Answers (3)

Anusha Subashini
Anusha Subashini

Reputation: 397

You can use below query for get the expected result 

select  [EmployeeID], [Blue] as  column1,[Green] as column2, [Yellow] as column2 from 
(

 Select 
   e.EmployeeID,
   Color
From 
     dbo.EmployeeID E 
join dbo.Color C on C.EmployeeID = E.EmployeeID


) a
pivot 
(
max(color)
 for color in ([Blue],[Green] , [Yellow])
) as pv;

Upvotes: 0

ca9163d9
ca9163d9

Reputation: 29179

with
    t as ( select   E.EmployeeID ,
                    Color
           from     E
                    join C on C.EmployeeID = E.EmployeeID
         )
select  EmployeeID ,
        Blue ,
        Green ,
        Yellow
from    t pivot ( min(Color) for Color in ( Blue, Green, Yellow ) ) pvt

Upvotes: 0

BJones
BJones

Reputation: 2460

If you know the number of columns you'll want to PIVOT, you can pivot in conjunction with ROW_NUMBER()

DECLARE @t TABLE (EmployeeID INT, Color VARCHAR(10))
INSERT @t
VALUES(123,'Blue'),
      (123,'Green'),
      (123,'Yellow'),
      (234,'Blue'),
      (234,'Green'),
      (456,'Yellow'),
      (456,'Blue')

;WITH c AS (
SELECT *, ROW_NUMBER() OVER(PARTITION BY EmployeeId ORDER BY Color) rn
FROM @t
)

SELECT EmployeeId, [1], [2], [3]
FROM c
PIVOT(
MAX(Color) FOR rn IN ([1], [2], [3])
) pvt

Upvotes: 4

Related Questions