SQL 重新格式化表列 [英] SQL Reformatting table columns

问题描述

我现在有一张桌子，格式很不方便.
例子:
列:

I have a table right now which is in a very inconvenient format.
Example:
Columns:

Quarter / ID  / Sales
1     / 234   / 50
1     / 258  / 100 
2     / 234  / 50 
2     / 456  / 125
3     / 258  / 100
3     / 456  / 75 
3     / 555  / 100  
4     / 555  / 50

这只是一个例子，实际上有 5600 列.

我想重新格式化表格，使我刚刚展示的示例看起来更像:

This is just an example, there are in reality like 5600 columns.

I want to reformat the table so that the example I just showed looks more like:

ID /   Q1   /    Q2    /   Q3    /    Q4
234/   50   /    50    /   0     /     0
258/   100  /    0     /   100   /     0
456/    0   /    125   /   75    /     0
555/    0   /     0    /   100   /     50

有没有一种方法可以让我轻松地移动这些列，也许是基于对 ID 号的某种查找?我只需要将每个 ID 的所有数据放在一个具有单独列的记录中，而不是 4 个记录中，每个季度一个.

Is there a way for me to easily just move these columns, maybe based on some sort of lookup for the ID number? I just need all the data for each ID in one record with separate columns, rather than in 4 records, one for each quarter.

谢谢

---到目前为止，我所做的只是创建一个具有不同 ID 的新表.然后我想使用某种连接或查找插入，可以将所有记录放入 Q1，其中季度 = 1，经销商 = 记录经销商.

---So far all I have done is create a new table with distinct ID. Then I want to use some sort of join or lookup insert that can put all records into Q1 where quarter = 1 and dealer = dealer of record.

推荐答案

好吧，我想通了.首先我做了一个新表.

Well I figured it out. First I made a new table.

CREATE TABLE newtable (ID varchar (5), Q1 int, Q2 int, Q3 int, Q4 int)

然后我插入了不同的 ID

Then I inserted distinct ID's

INSERT INTO newtable
SELECT DISTINCT ID
FROM oldtable

然后对于 Q1 我做了一个插入语句:

Then for Q1 I made a insert statement as such:

UPDATE newtable
SET newtable.Q1 = oldtable.sales
FROM newtable 
INNER JOIN oldtable
ON newtable.ID = oldtable.ID
WHERE oldtable.quarter = '1'

然后我只是复制并在每个季度执行，更改为 Q2，然后分别在 WHERE 语句中更改为2".

I then just copied and did it for each quarter, changing to Q2 and then respectively '2' in the WHERE statement.

只要确保您检查重复项，我的实际数据集有重复项，因为我使用了与另一列配对的不同选择.

Just make sure you check for duplicates, my actual dataset had duplicates because I used a select distinct paired with another column.

这篇关于SQL 重新格式化表列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！