SQL Pivot table

Mauro Cazabonnet
Mauro Cazabonnet used Ask the Experts™
on
I'm trying to convert the following table

id      data
1      2
2      ServerABC
3      ClientA
4      ServerXYZ
5      ServerFGH
6      Duplicate

to look like this

DupeCnt  Server          InitialCatalog   DirectoryServer   IIS
2                ServerABC  ClientA              ServerXYZ             ServerFGH

I need to exclude 6 duplicate

Any help much appreciated
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Software Team Lead
Commented:
try this:

declare @yourTable table
(
	id int,
	data varchar(100)
);
insert into @yourTable 
values
(1, '2'),
(2, 'ServerABC'),
(3, 'ClientA'),
(4, 'ServerXYZ'),
(5, 'ServerFGH'),
(6, 'Duplicate');


select
max(case when id = 1 then data else null end) DupeCnt,
max(case when id = 2 then data else null end) Server,
max(case when id = 3 then data else null end) InitialCatalog,
max(case when id = 4 then data else null end) DirectoryServer,
max(case when id = 5 then data else null end) IIS
from @yourTable

Open in new window

Mauro CazabonnetSenior .NET Software Engineer
Top Expert 2015

Author

Commented:
Thx!!!!!!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial