Petr Novák Petr Novák - 1 month ago 6
SQL Question

sql rows into columns pivot table

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


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


Any ideas?

Answer

Answers to Mark's question will help.

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

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

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

Comments