Convert Rows to columns using Pivot in SQL Server

0 votes

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
6 days ago in Others by polo
• 1,500 points
7 views

1 answer to this question.

0 votes

If you are using SQL Server 2005+, then you can use the PIVOT function to transform the data from rows into columns.

It sounds like you will need to use dynamic sql if the weeks are unknown but it is easier to see the correct code using a hard-coded version initially.

First up, here are some quick 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);

If your values are known, then you will hard-code the query:

select *
from 
(
  select store, week, xCount
  from yt 
) src
pivot
(
  sum(xcount)
  for week in ([1], [2], [3])
) piv;
answered 5 days ago by nisha
• 2,160 points

Related Questions In Others

0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

Feb 17 in Others by Edureka
• 13,600 points
26 views
0 votes
1 answer

How to delete duplicate rows in SQL Server?

To answer your query, note that CTEs ...READ MORE

answered Feb 10 in Others by Soham
• 8,730 points
81 views
0 votes
1 answer

How to create subnet inside a VPC in AWS using Terraform?

Hi@akhtar, You need to use aws_subnet resource inside your ...READ MORE

answered Jul 21, 2020 in Others by MD
• 95,360 points
162 views
0 votes
1 answer

Convert Rows to columns using 'Pivot' in SQL Server

You can use the PIVOT function to ...READ MORE

answered Feb 8 in Database by Vaani
• 6,820 points
43 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
294 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
4,998 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
• 6,820 points
53 views
0 votes
1 answer

How to delete duplicate rows in SQL Server?

WITH CTE AS ( SELECT *,ROW_NUMBER() OVER (PARTITION BY ...READ MORE

answered 4 days ago in Others by nisha
• 2,160 points
13 views
0 votes
1 answer

SQL Server replaces LEFT JOIN for LEFT OUTER JOIN in view query

there are different ways of writing joins. ...READ MORE

answered Jun 9 in Others by nisha
• 2,160 points
22 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