How to Transpose this Data

I have this problem, I need to transpose data as you would do it in Microsoft Excel, but via SQL...

Herewith is some sample data:

if object_id('tempdb..#Temp') is not null
    drop table #Temp

create  table #Temp (id int, Field varchar(100), [Value] varchar(max),DCLink int)

insert into #Temp (id,Field,[Value],DCLink) values 

(1,'Name','ARABELLA',1800)
,(2,'Contact_Person','GRANGE',1800)
,(3,'Physical1','COUNTRY ESTATE',1800)
,(4,'Physical2','R67 DURBAN STR',1800)
,(5,'Physical3','CAPE TOWN',1800)
,(6,'Physical4','',1800)
,(7,'Physical5','',1800)
,(8,'PhysicalPC','',1800)
,(9,'Addressee','',1800)
,(10,'Post1','',1800)
,(11,'Post2','P O BOX 5493',1800)
,(12,'Post3','GROOTMOND',1800)
,(13,'Post4','',1800)
,(14,'Post5','',1800)
,(15,'PostPC','7195',1800)
,(16,'Delivered_To','PIET',1800)
,(17,'Telephone','029 428 0015',1800)
,(18,'Telephone2','028 428 0000',1800)
,(19,'Fax1','',1800)
,(20,'Fax2','',1800)
,(21,'Tax_Number','NOT TAX',1800)
,(22,'Registration','',1800)
,(23,'Credit_Limit','0',1800)
,(24,'On_Hold','0',1800)
,(25,'EMail','jan.fiskaal@arabella.com',1800)
,(26,'BranchCode','',1800)
,(27,'BankAccNum','',1800)
,(28,'CheckTerms','1',1800)
,(29,'cAccDescription','',1800)
,(30,'cWebPage','',1800)
,(31,'[Group]','C',1800)
,(32,'Area','WCA',1800)
,(33,'RepName','',1800)
,(34,'RepCode','',1800)
,(35,'BankName','',1800)
,(36,'PriceListName','Price List 1',1800)
,(37,'AgeCode','MNTH-STMT',1800)
,(38,'Name','BEACH ASSOCIATION',1602)
,(39,'Contact_Person','HARRY POTTER',1602)
,(40,'Physical1','3 BLUE HILLS DRIVE',1602)
,(41,'Physical2','BLUE HILLS',1602)
,(42,'Physical3','LOS ANGELES',1602)
,(43,'Physical4','',1602)
,(44,'Physical5','',1602)
,(45,'PhysicalPC','7441',1602)
,(46,'Addressee','',1602)
,(47,'Post1','',1602)
,(48,'Post2','PO BOX 237',1602)
,(49,'Post3','LOS ANGELES',1602)
,(50,'Post4','',1602)
,(51,'Post5','',1602)
,(52,'PostPC','7437',1602)
,(53,'Delivered_To','',1602)
,(54,'Telephone','031 678 0590',1602)
,(55,'Telephone2','',1602)
,(56,'Fax1','',1602)
,(57,'Fax2','031 678 0592',1602)
,(58,'Tax_Number','1234567890',1602)
,(59,'Registration','1765/4783/228',1602)
,(60,'Credit_Limit','0',1602)
,(61,'On_Hold','0',1602)
,(62,'EMail','Harry.Potter@beach.co.za',1602)
,(63,'BranchCode','',1602)
,(64,'BankAccNum','',1602)
,(65,'CheckTerms','1',1602)
,(66,'cAccDescription','',1602)
,(67,'cWebPage','',1602)
,(68,'[Group]','C',1602)
,(69,'Area','WCA',1602)
,(70,'RepName','',1602)
,(71,'RepCode','',1602)
,(72,'BankName','',1602)
,(73,'PriceListName','Price List 1',1602)

select
*
from    #Temp

The above results look like this:

Here is what I tried:

select
*
,   c.*
from    ClientUpdateLog
cross apply
(
values
('Name',[Value])
,('Contact_Person',[Value])
,('Physical1',[Value])
,('Physical2',[Value])
,('Physical3',[Value])
,('Physical4',[Value])
,('Physical5',[Value])
,('PhysicalPC',[Value])
,('Addressee',[Value])
,('Post1',[Value])
,('Post2',[Value])
,('Post3',[Value])
,('Post4',[Value])
,('Post5',[Value])
,('PostPC',[Value])
,('Delivered_To',[Value])
,('Telephone',[Value])
,('Telephone2',[Value])
,('Fax1',[Value])
,('Fax2',[Value])
,('Tax_Number',[Value])
,('Registration',[Value])
,('Credit_Limit',[Value])
,('On_Hold',[Value])
,('EMail',[Value])
,('BranchCode',[Value])
,('BankAccNum',[Value])
,('CheckTerms',[Value])
,('cAccDescription',[Value])
,('cWebPage',[Value])
,('[Group]',[Value])
,('Area',[Value])
,('RepName',[Value])
,('RepCode',[Value])
,('BankName',[Value])
,('PriceListName',[Value])
,('AgeCode',[Value])
) c (
    Name
,   Contact_Person
,   Physical1
,   Physical2
,   Physical3
,   Physical4
,   Physical5
,   PhysicalPC
,   Addressee
,   Post1
,   Post2
,   Post3
,   Post4
,   Post5
,   PostPC
,   Delivered_To
,   Telephone
,   Telephone2
,   Fax1
,   Fax2
,   Tax_Number
,   Registration
,   Credit_Limit
,   On_Hold
,   EMail
,   BranchCode
,   BankAccNum
,   CheckTerms
,   cAccDescription
,   cWebPage
,   [Group]
,   Area
,   RepName
,   RepCode
,   BankName
,   PriceListName
,   AgeCode
)

I need to Transpose it to look like this:

I could not include all the expected results due to space...

I've tried Cross Apply, but I get stuck the whole time.

I did not try a Pivot as I thought it might work?

Your assistance will be appreciated!

Solution

You're trying to use VALUES to unpivot your data, which is already unpivoted. You also create your VALUES table construct with 2 columns, but then state it has 37 in its definition.

You want a Cross-tab/PIVOT here. Personally i prefer a Cross-Tab:

SELECT c.DCLink,
       MAX(CASE Field WHEN 'Name' THEN [Value] END) AS [Name],
       MAX(CASE Field WHEN 'Contact_Person' THEN [Value] END) AS [Contact_Person],
       MAX(CASE Field WHEN 'Physical1' THEN [Value] END) AS [Physical1],
       MAX(CASE Field WHEN 'Physical2' THEN [Value] END) AS [Physical2],
       MAX(CASE Field WHEN 'Physical3' THEN [Value] END) AS [Physical3],
       MAX(CASE Field WHEN 'Physical4' THEN [Value] END) AS [Physical4],
       MAX(CASE Field WHEN 'Physical5' THEN [Value] END) AS [Physical5],
       MAX(CASE Field WHEN 'PhysicalPC' THEN [Value] END) AS [PhysicalPC],
       MAX(CASE Field WHEN 'Addressee' THEN [Value] END) AS [Addressee],
       MAX(CASE Field WHEN 'Post1' THEN [Value] END) AS [Post1],
       MAX(CASE Field WHEN 'Post2' THEN [Value] END) AS [Post2],
       MAX(CASE Field WHEN 'Post3' THEN [Value] END) AS [Post3],
       MAX(CASE Field WHEN 'Post4' THEN [Value] END) AS [Post4],
       MAX(CASE Field WHEN 'Post5' THEN [Value] END) AS [Post5],
       MAX(CASE Field WHEN 'PostPC' THEN [Value] END) AS [PostPC],
       MAX(CASE Field WHEN 'Delivered_To' THEN [Value] END) AS [Delivered_To],
       MAX(CASE Field WHEN 'Telephone' THEN [Value] END) AS [Telephone],
       MAX(CASE Field WHEN 'Telephone2' THEN [Value] END) AS [Telephone2],
       MAX(CASE Field WHEN 'Fax1' THEN [Value] END) AS [Fax1],
       MAX(CASE Field WHEN 'Fax2' THEN [Value] END) AS [Fax2],
       MAX(CASE Field WHEN 'Tax_Number' THEN [Value] END) AS [Tax_Number],
       MAX(CASE Field WHEN 'Registration' THEN [Value] END) AS [Registration],
       MAX(CASE Field WHEN 'Credit_Limit' THEN [Value] END) AS [Credit_Limit],
       MAX(CASE Field WHEN 'On_Hold' THEN [Value] END) AS [On_Hold],
       MAX(CASE Field WHEN 'EMail' THEN [Value] END) AS [EMail],
       MAX(CASE Field WHEN 'BranchCode' THEN [Value] END) AS [BranchCode],
       MAX(CASE Field WHEN 'BankAccNum' THEN [Value] END) AS [BankAccNum],
       MAX(CASE Field WHEN 'CheckTerms' THEN [Value] END) AS [CheckTerms],
       MAX(CASE Field WHEN 'cAccDescription' THEN [Value] END) AS [cAccDescription],
       MAX(CASE Field WHEN 'cWebPage' THEN [Value] END) AS [cWebPage],
       MAX(CASE Field WHEN '[Group]' THEN [Value] END) AS [Group],
       MAX(CASE Field WHEN 'Area' THEN [Value] END) AS [Area],
       MAX(CASE Field WHEN 'RepName' THEN [Value] END) AS [RepName],
       MAX(CASE Field WHEN 'RepCode' THEN [Value] END) AS [RepCode],
       MAX(CASE Field WHEN 'BankName' THEN [Value] END) AS [BankName],
       MAX(CASE Field WHEN 'PriceListName' THEN [Value] END) AS [PriceListName],
       MAX(CASE Field WHEN 'AgeCode' THEN [Value] END) AS [AgeCode]
FROM #Temp c
GROUP BY c.DCLink;