what is pivot in sql?

Subash

10y2.7kSQL

what is the Pivot in SQL please explain the concept of pivot

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Ananth G10y ago
Accepted answer
example for pivot
+1
Daniel Jones9y ago
PIVOT in SQL Server allows you to write a cross-tabulation. This means that you can aggregate your results and rotate rows into columns.
0
Nigel Fernandes9y ago
Sample SQL query:
CREATE TABLE #test
    ([Store] int, [Week] int, [xCount] int)
;

INSERT INTO #test
    ([Store], [Week], [xCount])
VALUES
    (100, 1, 88),
    (102, 1, 96),
    (101, 1, 138),
    (105, 1, 37),
    (109, 1, 59),
    (101, 2, 282),
    (102, 2, 212),
    (105, 2, 78),
    (109, 2, 97),
    (105, 3, 60),
    (102, 3, 123),
    (101, 3, 220),
    (109, 3, 87)
;


select *
from
(
  select store, week, xCount
  from #test
) src
pivot
(
  sum(xcount)
  for week in ([1], [2], [3])
) piv;
0
Rajeesh Menoth10y ago
Hi,

PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output, and performs aggregations where they are required on any remaining column values that are wanted in the final output. UNPIVOT performs the opposite operation to PIVOT by rotating columns of a table-valued expression into column values.

Reference :

https://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx

http://www.codeproject.com/Tips/500811/Simple-Way-To-Use-Pivot-In-SQL-Query
+1
Santhakumar Munuswamy10y ago
Hi Subash,
https://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx
http://blog.sqlauthority.com/2008/06/07/sql-server-pivot-and-unpivot-table-examples/
http://sqlhints.com/2014/03/10/pivot-and-unpivot-in-sql-server/
0