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