0

I have this simple sql statement:

SELECT FCDT.description AS DetailType, FCD.data
FROM Contact_Detail FCD
LEFT JOIN Contact_Detail_Types FCDT
ON FCD.contact_detail_types_id=FCDT.contact_detail_types_id
WHERE contacts_id='4AA36958--0804CA'

which makes this result:

DetailType   data
-------------------------------
Phone        123-456-7890
Email        myEmail@domain.com

I need to convert this rows to columns, to have this result:

Phone         Email
--------------------------------
123-456-7890  myEmail@domain.com

How I can accomplish this? I have some hours playing with the pivot function, but no results so far.

BernieSF
  • 1,722
  • 1
  • 28
  • 42

1 Answers1

1
Select * From
(SELECT FCDT.description AS DetailType, FCD.data As Data
FROM Contact_Detail FCD
LEFT JOIN Contact_Detail_Types FCDT
ON FCD.contact_detail_types_id=FCDT.contact_detail_types_id
WHERE contacts_id='4AA36958--0804CA')P
pivot
(Max(Data) for DetailType in ([Phone], [Email])) As Pvt
Sravan Kumar
  • 51
  • 4
  • Thanks Sravan! works, but what happen if the initial query returns more rows (like fax, Cell Phone, etc)? The DetailType its dinamyc! – BernieSF Feb 03 '16 at 17:56
  • It doesn't matter as long as 'DetailType in ([Phone], [Email])' remains the same. If you want all of them as columns, add it in here. – Sravan Kumar Feb 03 '16 at 17:59
  • Then there is no way to have variable columns in the pivot part of the sql sentence? If a user add a new record to DetailType, then forces me to modify this query and add one more element to the pivot list – BernieSF Feb 03 '16 at 18:03
  • 1
    Then we have to go with dynamic sql statement. This link may help you. [Link](http://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server) – Sravan Kumar Feb 03 '16 at 18:06