SQL行到列数据透视表 [英] sql rows into columns pivot table

问题描述

我有这样的sql视图:

I have sql view like this:

id date_from  date_to
1  2005-01-05 2005-05-10
1  2005-01-05 2005-05-10
1  2005-01-05 2005-05-10
2  2005-01-01 2006-08-31
2  2010-01-01 2010-06-30
3  2005-01-01 2005-06-30

我想编写返回以下内容的sql语句:

And I want to write sql statement which returns:

1 2005-01-05 2005-05-10 2005-01-05 2005-05-10 2005-01-05 2005-05-10
2 2005-01-01 2006-08-31 2010-01-01 2010-06-30 NULL       NULL
3 2005-01-01 2005-06-30 NULL       NULL       NULL       NULL

有什么想法吗?

推荐答案

回答马克的问题会有所帮助.

Answers to Mark's question will help.

这是哪个RDBMS-MySQL，Oracle，SQLServer ...?还有，你曾经 想要每个id包含三个以上的date_from，date_to对吗?

Which RDBMS is this - MySQL, Oracle, SQLServer...? Also, are you ever going to want more than three date_from, date_to pairs per id?

使用t-sql，我明确处理了3个级别.如果希望它是动态的，则需要动态创建查询.

using t-sql, I explicitly handle 3 levels. If you want it to be dynamic, you need to create the query dynamically.

DECLARE @staging TABLE
(
    id int NOT NULL,
    date_from datetime NOT NULL,
    date_to datetime NOT NULL,
    step int
)

INSERT INTO @staging
SELECT id, date_from, date_to,
    ROW_NUMBER() OVER(PARTITION BY id ORDER BY date_from, date_to)
FROM tblTemp

-- below is static for 3 levels, make below dynamic to match what you want
SELECT t1.id, t1.date_from, t1.date_to, t2.date_from, t2.date_to, t3.date_from, t3.date_to
FROM @staging t1 LEFT OUTER JOIN
    @staging t2 ON t1.id = t2.id AND t2.step = 2 LEFT OUTER JOIN
    @staging t3 ON t2.id = t3.id AND t3.step = 3
WHERE t1.step = 1

测试@ http://sqlfiddle.com/#!3/9daae/4/0

这篇关于SQL行到列数据透视表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！