T-SQL Pivot & Unpivot

Go straight: Row to Column

CREATE TABLE Salesbyquarter (
Year INT,
QUARTER VARCHAR (2),
AMOUNT money);

SET NOCOUNT on
DECLARE @index INT
DECLARE @q INT
SET @index = 0
DECLARE @year INT
while (@index < 30)
BEGIN
SET @year = 2005 + (@index% 4)
SET @q = (CAST ((RAND () *) as INT)% 4) + 1
INSERT into Salesbyquarter VALUES (@year, ' Q ' + CAST (@q as CHAR (1)), RAND () * 10000.00)
SET @index = @index + 1
END

Create a sales table from the SQL above and insert a few data

To implement row-to-column:

SELECT *from Salesquarter

PIVOT

(

SUM (AMOUNT)

For QUARTER in (Q1,Q2,Q3,Q4)

) as PVT

Because pivot is a property of Sql Server2005 or later, the properties of the database->options->compatibility level:sql Server 2005 (90), otherwise run fail.

List of career change:

CREATE TABLE #temptable (rowid int,colorname varchar), Hexa varchar (7)
, R tinyint,g tinyint,b tinyint)
GO
INSERT into #temptable values (1, ' Violet ', ' #8B00FF ', 139,0,255);
INSERT into #temptable values (2, ' Indigo ', ' #4B0082 ', 75,0,130);
INSERT into #temptable values (3, ' Blue ', ' #0000FF ', 0,0,255);
INSERT into #temptable values (4, ' Green ', ' #00FF00 ', 0,255,0);
INSERT into #temptable values (5, ' Yellow ', ' #FFFF00 ', 255,255,0);
INSERT into #temptable values (6, ' Orange ', ' #FFA500 ', 255,165,0);
INSERT into #temptable values (7, ' Red ', ' #FF0000 ', 255,0,0);
GO
SELECT * FROM #temptable
GO

Create a temporary table with the above SQL and insert the data

Select *from (select *from #TEMPTABLE) P

UNPIVOT

(Rgbvalue for RGB in (R,G,B)) As UPVT;

T-SQL Pivot & Unpivot