Changing a column in to a row in sql

1
13

Imagecreate table products
(
cust varchar(100),
product varchar(100),
amount money
)

select * from products
abhi keyboard 300.00
abhi laptop 20000.00
abhi mouse 200.00
asha keyboard 400.00
asha laptop 10000.00
asha mouse 600.00
john keyboard 100.00
john laptop 30000.00
john mouse 150.00

PIVOT DEMO
========
select cust,[laptop] as laptops,[mouse] as mouses,[keyboard] as keyboards
from
(select cust,product,amount from products) tab1

pivot
(
SUM (amount)
FOR Product IN
( [laptop], [mouse],[keyboard])
) AS pivot1

1. portion : This portion of the query selects the three columns for the final result set

2.This query pulls all the rows of data that we need to create the cross-tab results. The (tab1) after the query is creating a temporary table of the results that can then be used to satisfy the query for step 1.

3.This query does the actual summarization and puts the results into a temporary table called pivot1

Another key thing to notice in here is the use of the square brackets [ ] around the column names in both the SELECT in part (1) and the IN in part (3). These are key, because the pivot operation is treating the values in these columns as column names and this is how the breaking and grouping is done to display the data.

1 COMMENT

LEAVE A REPLY

Please enter your comment!
Please enter your name here