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,940 points
305 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
0 answers

How to ALTER multiple columns at once in SQL Server

ALTER the data types of several columns in ...READ MORE

Aug 12 in Database by Kithuzzz
• 20,660 points
513 views
0 votes
0 answers

Convert date from dd-mm-yyyy to yyyy-mm-dd in SQL Server

Given date: DECLARE @Date1 VARCHAR(50) = '30-01-2015' I want ...READ MORE

Aug 18 in Database by Kithuzzz
• 20,660 points
2,318 views
0 votes
0 answers

How to find sum of multiple columns in a table in SQL Server 2005?

I have a table Emp which has these rows: Emp_cd ...READ MORE

Aug 19 in Database by Kithuzzz
• 20,660 points
507 views
0 votes
0 answers

How to DROP multiple columns with a single ALTER TABLE statement in SQL Server?

In one ALTER TABLE statement, I would ...READ MORE

Sep 2 in Database by Kithuzzz
• 20,660 points
92 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,190 points
565 views
0 votes
0 answers

Efficiently convert rows to columns in sql server

I need to deal with a lot ...READ MORE

Sep 3 in Database by Kithuzzz
• 20,660 points
263 views
0 votes
0 answers

Simple way to transpose columns and rows in SQL?

How can I quickly switch rows and ...READ MORE

Sep 5 in Database by Kithuzzz
• 20,660 points
963 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
346 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
168 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
5,562 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP