Un pivot example

Un pivot example
drop table MaheshUnPivotExample
CREATE TABLE dbo.MaheshUnPivotExample
(
  MaheshUnPivotExampleID INT PRIMARY KEY identity(1,1), -- FK
  PrimaryPhone VARCHAR(20),
  SecondPhone VARCHAR(20),
  OtherPhone VARCHAR(20) ,
  ProviderName VARCHAR(20)
);
truncate table MaheshUnPivotExample
INSERT dbo.MaheshUnPivotExample
  ( PrimaryPhone, SecondPhone, OtherPhone)
VALUES
  ('11111', '222222', '333333'),
  ('44444444444', NULL, NULL),
  ('55555', null, '666666666'),
  ('777777777777', '8888888888', null);

  select * from MaheshUnPivotExample


  SELECT MaheshUnPivotExampleID , UNPivotColumnName
FROM
(
  SELECT MaheshUnPivotExampleID, PrimaryPhone, SecondPhone, OtherPhone
  FROM dbo.MaheshUnPivotExample
) AS cp
UNPIVOT
(
  UNPivotColumnName FOR UnpivotObject IN (PrimaryPhone, SecondPhone, OtherPhone)
) AS up;
-- if you want to show null values
  SELECT MaheshUnPivotExampleID , UNPivotColumnName
FROM
(
  SELECT MaheshUnPivotExampleID, Isnull(PrimaryPhone,'N/A')PrimaryPhone, Isnull(SecondPhone,'N/A')SecondPhone, Isnull(OtherPhone,'N/A')OtherPhone
  FROM dbo.MaheshUnPivotExample
) AS cp
UNPIVOT
(
  UNPivotColumnName FOR UnpivotObject IN (PrimaryPhone, SecondPhone, OtherPhone)
) AS up;


--
update  MaheshUnPivotExample
set ProviderName  = 'Vodaphone'

update  MaheshUnPivotExample
set ProviderName  = 'AirTel'
where  len(Isnull(PrimaryPhone,'N/A'))> 5



  SELECT MaheshUnPivotExampleID,UNPivotColumnName , UNPivotColumnName2
FROM
(
  SELECT MaheshUnPivotExampleID, Isnull(PrimaryPhone,'N/A')PrimaryPhone, Isnull(SecondPhone,'N/A')SecondPhone, Isnull(OtherPhone,'N/A')OtherPhone, ProviderName
  FROM dbo.MaheshUnPivotExample
) AS cp
UNPIVOT
(
  UNPivotColumnName FOR UnpivotObject IN (PrimaryPhone, SecondPhone, OtherPhone)
) AS up
UNPIVOT
(
    UNPivotColumnName2 FOR UnpivotObject2 IN (ProviderName)
) AS up2;