Brief explanation of pivot element that could be easily understood by a beginner in SQL Server

Question

I am required to convert columns of a table into rows ways. After spending sometime I found, it can be done by using pivot clause. But I didn't understand the concept, the working of pivot operators even after going through many articles,documents which they say simple but hard for me to understand. So can anyone help me understand the basics of pivot using the tables? consider the below tables to answer

--------------
|name  |value|
|------|-----|
|rate1 |145  |
|rate2 |150  |
|rate3 |155  |
-------------- 

now I want the result as

-------------------
|rate1|rate2|rate3|
|-----|-----|-----|
|145  |150  |155  |
-------------------

else I just want only one column converted. okay simple table with one column as shown below

-------
|value|
|145  |
|150  |
|155  |
-------

now the result should be like

----------------
|hdr1|hdr2|hdr3|
|----|----|----|
|145 |150 |155 |
----------------

Note:The headers doesn't matter, it can be anything for the resulting table. now anyone please help me how to achieve this.Explanations will be highly appreciated.

Answer 1

The following demo will return your expected result with the given sample data:

DECLARE @TestTable TABLE ([name] VARCHAR (20), [value] INT);

INSERT INTO @TestTable ([name], [value]) VALUES
('rate1', 145),
('rate2', 150),
('rate3', 155);

SELECT rate1, rate2, rate3 FROM 
( SELECT [name], [value] FROM @TestTable ) AS T1
PIVOT  
( SUM([value]) FOR [name] IN (rate1, rate2, rate3)) AS T2  

Output:

rate1   rate2   rate3
-------------------------
145     150     155

In the PIVOT block, you need to mention by which value you need to pivot the values like rate1, ...

Demo on db<>fiddle

Answer 2

Your query:

Just read the @Arulkumar's post: https://stackoverflow.com/a/56197463/1666800

PIVOT explanation:

While you are using PIVOT, you need to answer three questions:

1. What do you want to see on rows? (Lets call it Grouping Element)
2. What do you want to see on columns? (Lets call it Spreading Element)
3. What do you want to see on cross point of row and column? (Lets call it Aggregation Element)

After that you answer these questions, you can simply write your PIVOT query as below:

WITH PivotData AS
(SELECT 
     <grouping column>, 
     <spreading column>, 
     <aggregation column>
 FROM < source table >
)
SELECT < select list > FROM PivotData
PIVOT( < aggregate function >(< aggregation column >)
FOR < spreading column > IN (< distinct spreading values >) ) AS P

PIVOT itself returns a SET, which means that if you use it inside your query, you need to assign an alias to it(Here is P).

Example:

Imagine that we have a table which has three columns: CustomerId, ShipperId and Freight. we need to see the overall freight shipped by each shipper for each customer. lets imagine that ShipperId has three distinct values: 1, 2 and 3.

WITH    PivotData AS 
( SELECT   
            CustomerId, -- grouping column
            ShipperId , -- spreading column
            Freight-- aggregation column
        FROM     Sales.Orders
)
SELECT  CustomerId,[1], [2], [3]
    FROM    PivotData 
PIVOT( 
    SUM(Freight) 
    FOR ShipperId IN ( [1], [2], [3] )
) AS P;

the output will be like this:

CustomerId   [1]     [2]      [3]
----------------------------------
C1            5       7        2
C2            10      1        9
C3            6       0        4

Constraints:

1. We can not use Spreading element and Aggregation element in Select list to show in output
2. We can not use Count(*) as Aggregation Function. We need to specify the column name inside the Count function
3. Each PIVOT is allowed to have only one aggregation function
4. Content inside IN should be static and we can not specify a Select query inside it, however we can use Dynamic SQL to overcome this issue.(https://stackoverflow.com/a/10404455/1666800)