Convert Rows to columns using Pivot in SQL Server

0 votes

I have read the stuff on MS pivot tables and I am still having problems getting this correct.

I have a temp table that is being created, we will say that column 1 is a Store number, and column 2 is a week number and lastly column 3 is a total of some type. Also the Week numbers are dynamic, the store numbers are static.

Store      Week     xCount
-------    ----     ------
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

I would like it to come out as a pivot table, like this:

Store        1          2          3        4        5        6....
----- 
101        138        282        220
102         96        212        123
105         37        
109

Store numbers down the side and weeks across the top.

Feb 8 in Database by Neha
• 8,560 points
55 views

1 answer to this question.

0 votes

You can use the PIVOT function to transform the data from rows into columns, if you are working on SQL Server 2005+

Some table definitions and data for use:

CREATE TABLE yt 
(
  [Store] int, 
  [Week] int, 
  [xCount] int
);
    
INSERT INTO yt
(
  [Store], 
  [Week], [xCount]
)
VALUES
    (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);

You can hard-code the query if the values are known:

select *
from 
(
  select store, week, xCount
  from yt 
) src
pivot
(
  sum(xcount)
  for week in ([1], [2], [3])
) piv;

answered Feb 8 by Vaani
• 7,020 points

Related Questions In Database

0 votes
1 answer

How to rename a column in a database table using SQL?

For SQL Server, use sp_rename USE AdventureWorks; GO EXEC sp_rename 'Customers.CustomerTerritory.TerritoryID', ...READ MORE

answered Feb 9 in Database by Neha
• 8,560 points
67 views
0 votes
1 answer

What are the different authentication modes in SQL Server? How can it be changed?

Windows mode and Mixed Mode – SQL ...READ MORE

answered Oct 29, 2018 in Database by Sahiti
• 6,380 points
596 views
0 votes
1 answer
0 votes
1 answer

Error: Cannot connect to the server using SA account - MSSQL Linux

The option is to reset the system ...READ MORE

answered Mar 17, 2019 in Database by Fatima
276 views
0 votes
1 answer

Convert Rows to columns using 'Pivot' in SQL Server

If you are using SQL Server 2005+, ...READ MORE

answered Jun 20 in Others by nisha
• 2,160 points
12 views
0 votes
1 answer

Calculate Time Intersection to Correlate Sequences of Independent Events

I think this solution requires a CROSS JOIN implementation. ...READ MORE

answered Oct 26, 2018 in Power BI by Upasana
• 8,620 points
297 views
0 votes
1 answer

Pivot rows into columns in AWS Athena?

You can use the following method :- It ...READ MORE

answered Oct 29, 2018 in AWS by Priyaj
• 58,080 points
5,046 views
0 votes
1 answer

How do I UPDATE from a SELECT in SQL Server?

MERGE INTO YourTable T USING ...READ MORE

answered Feb 3 in Database by Vaani
• 7,020 points
57 views
0 votes
1 answer

How to delete duplicate rows in SQL Server?

CTEs and ROW_NUMBER can be combined together which will ...READ MORE

answered Feb 10 in Database by Vaani
• 7,020 points
37 views
0 votes
1 answer

Convert Date format into DD/MMM/YYYY format in SQL Server

Using convert() and style 106, you can ...READ MORE

answered Feb 23 in Database by Vaani
• 7,020 points
1,653 views
webinar REGISTER FOR FREE WEBINAR X
Send OTP
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP