Pivoting row/column using T-SQL

Question

How would be the T-SQL query for below scenario:

Select * from Table1

col1|col2|col3
--------------
xxxx|1111|2222
yyyy|3333|4444

to

col1|col2
---------
xxxx|yyyy
1111|3333
2222|4444

Answer 1

You can try this:

DECLARE @DataSource TABLE
(
    [Col1] VARCHAR(4)
   ,[Col2] VARCHAR(4)
   ,[Col3] VARCHAR(4)
);

INSERT INTO @DataSource ([Col1], [Col2], [Col3])
VALUES ('xxxx', '1111', '2222')
      ,('yyyy', '3333', '4444');

SELECT [1] AS [col1]
      ,[2] AS [col2]
FROM 
(
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS [RowID]
          ,[Col1]
          ,[Col2]
          ,[Col3]
    FROM @DataSource
)DS
UNPIVOT
(
    [Value] FOR [Column] IN ([Col1],[Col2], [Col3])
) UNPVT
PIVOT
(
    MAX([Value]) FOR [RowID] IN ([1], [2])
) PVT

Answer 2

Something like this perhaps. Added another column for demonstration purposes, note it will only work if the datatypes are the same:

;WITH CTE AS
(
  SELECT 
    row_number() over (order by (select null)) row1, 
    col1, col2, col3
  FROM
    -- this would be replaced by your table
    (values('xxxx', '1111', '2222'),('yyyy', '3333', '4444')) p(col1,col2,col3) 
), CTE2 as
(
  SELECT 
    items, 
    row1, 
    row_number() over(partition by row1 order by (select 1)) pos
  FROM CTE
  UNPIVOT      
    (Items FOR Seq IN ([col1], [col2], [col3])) AS unpvt  
)
SELECT
  [1] col1, [2] col2, [3] col3
FROM  
  cte2
PIVOT (max(items) FOR [row1] IN ([1], [2], [3])) AS pvt 

Result:

col1    col2    col3
xxxx    yyyy    NULL
1111    3333    NULL
2222    4444    NULL