A different kind of pivot?

1

I have this select output:
lastname, firstname, middle, customfieldlabel, customfieldvalue, access
Smith, Robert, A, Union, SA, 649 ALWAYS
Smith, Robert, A, Building ID, 649, 649 ALWAYS
Smith, Robert, A, EMPLOYEE #, 20526, 649 ALWAYS
Smith, Robert, A, Employment Status, Active, 649 ALWAYS
Smith, Robert, A, Position, Caretaker, 649 ALWAYS
Berry, Leon, L, EMPLOYEE #, 25632, 685 RS
Berry, Leon, L, EMPLOYEE #, 25632, 819 EX
Berry, Leon, L, Union, SA, 685 RS
Berry, Leon, L, Union, SA, 819 EX

These came from third party tables and cannot be changed. I want the value of the customfieldlabel as the column name and the customfieldvalue as the values for the corresponding customfieldlabel. If there is more than one access value then create more than 1 record.

I want this output to look like this after some manipulation.
lastname, firstname, middle, union, building id, employee #, employment status, position, access
Smith, Robert, A, SA, 649, 20526, Active, Caretaker, 649 ALWAYS
Berry, Leon, L, SA, , 25632, , , 685 RS
Berry, Leon, L, SA, , 25632, , , 819 EX

3

create table Test (
lastname varchar(255),
firstname varchar(255),
middle varchar(255),
customfieldlabel varchar(255),
customfieldvalue varchar(255),
access varchar(255)
)

insert into Test values
('Smith', 'Robert', 'A', 'Union', 'SA', '649 ALWAYS')
,('Smith', 'Robert', 'A', 'Building ID', '649', '649 ALWAYS')
,('Smith', 'Robert', 'A', 'EMPLOYEE #', '20526', '649 ALWAYS')
,('Smith', 'Robert', 'A', 'Employment Status', 'Active', '649 ALWAYS')
,('Smith', 'Robert', 'A', 'Position', 'Caretaker', '649 ALWAYS')
,('Berry', 'Leon' , 'L', 'EMPLOYEE #', '25632', '685 RS')
,('Berry', 'Leon' , 'L', 'EMPLOYEE #', '25632', '819 EX')
,('Berry', 'Leon' , 'L', 'Union', 'SA', '685 RS')
,('Berry', 'Leon' , 'L', 'Union', 'SA', '819 EX')

--Your original table
select * from Test

--what you want
select
lastname,
firstname,
middle,
[Union],
[Building ID],
[EMPLOYEE #],
[Employment Status],
[Position],
[Access]
from test
pivot(max(customfieldvalue) for customfieldlabel in(
[Building ID]
,[EMPLOYEE #]
,[Employment Status]
,[Position]
,[Union])) ptt

1 Like
4

dynamic pivot

create table Test (
lastname varchar(255),
firstname varchar(255),
middle varchar(255),
customfieldlabel varchar(255),
customfieldvalue varchar(255),
access varchar(255)
)

insert into Test values
('Smith', 'Robert', 'A', 'Union', 'SA', '649 ALWAYS')
,('Smith', 'Robert', 'A', 'Building ID', '649', '649 ALWAYS')
,('Smith', 'Robert', 'A', 'EMPLOYEE #', '20526', '649 ALWAYS')
,('Smith', 'Robert', 'A', 'Employment Status', 'Active', '649 ALWAYS')
,('Smith', 'Robert', 'A', 'Position', 'Caretaker', '649 ALWAYS')
,('Berry', 'Leon' , 'L', 'EMPLOYEE #', '25632', '685 RS')
,('Berry', 'Leon' , 'L', 'EMPLOYEE #', '25632', '819 EX')
,('Berry', 'Leon' , 'L', 'Union', 'SA', '685 RS')
,('Berry', 'Leon' , 'L', 'Union', 'SA', '819 EX')

--Your original table
select * from Test

--what you want

declare @column varchar(max)
set @column =
stuff(

(select ','+quotename(y) from (

select distinct customfieldlabel as y from test
) t
order by y
for xml path ('')),1,1,'')

--select @T

declare @sql varchar(max)
set @sql = (

'
select
lastname,
firstname,
middle,
[Union],
[Building ID],
[EMPLOYEE #],
[Employment Status],
[Position],
[Access]
from test
pivot(max(customfieldvalue) for customfieldlabel in(
'+@column+')) ptt'
)

exec (@sql)