I’m looking to transpose by some columns in my table. I have something like the following example:
╔════════╦═══════╦══════╦══════╦═════════╦══════╦═══════╦═══════╦═══════╦═══════╦═══════╦═══════╦═══════╗ ║ name ║ week ║ colX ║ ColY ║ Col.... ║ ColZ ║ Day 1 ║ Day 2 ║ Day 3 ║ Day 4 ║ Day 5 ║ Day 6 ║ Day 7 ║ ╠════════╬═══════╬══════╬══════╬═════════╬══════╬═══════╬═══════╬═══════╬═══════╬═══════╬═══════╬═══════╣ ║ name x ║ week1 ║ xxx ║ yyy ║ .... ║ zzz ║ 0 ║ 5 ║ 0 ║ 0 ║ 0 ║ 0 ║ 0 ║ ╠════════╬═══════╬══════╬══════╬═════════╬══════╬═══════╬═══════╬═══════╬═══════╬═══════╬═══════╬═══════╣ ║ name Y ║ week1 ║ xxx ║ yyy ║ .... ║ zzz ║ 4 ║ 0 ║ 0 ║ 0 ║ 0 ║ 0 ║ 0 ║ ╠════════╬═══════╬══════╬══════╬═════════╬══════╬═══════╬═══════╬═══════╬═══════╬═══════╬═══════╬═══════╣ ║ name Z ║ week1 ║ xxx ║ yyy ║ .... ║ zzz ║ 0 ║ 0 ║ 5 ║ 0 ║ 5 ║ 0 ║ 0 ║ ╠════════╬═══════╬══════╬══════╬═════════╬══════╬═══════╬═══════╬═══════╬═══════╬═══════╬═══════╬═══════╣ ║ name x ║ week2 ║ xxx ║ yyy ║ .... ║ zzz ║ 0 ║ 0 ║ 0 ║ 5 ║ 5 ║ 0 ║ 0 ║ ╠════════╬═══════╬══════╬══════╬═════════╬══════╬═══════╬═══════╬═══════╬═══════╬═══════╬═══════╬═══════╣ ║ name Y ║ week2 ║ xxx ║ yyy ║ .... ║ zzz ║ 5 ║ 0 ║ 0 ║ 0 ║ 4 ║ 0 ║ 0 ║ ╠════════╬═══════╬══════╬══════╬═════════╬══════╬═══════╬═══════╬═══════╬═══════╬═══════╬═══════╬═══════╣ ║ name Z ║ week2 ║ xxx ║ yyy ║ .... ║ zzz ║ 0 ║ 4 ║ 5 ║ 4 ║ 0 ║ 0 ║ 2 ║ ╚════════╩═══════╩══════╩══════╩═════════╩══════╩═══════╩═══════╩═══════╩═══════╩═══════╩═══════╩═══════╝
I would like to have transposed the information to show 1 day per line and the rest of the information as is, I understand this will create several more lines, but would help keeping more clear the information.
right each line show 1 week and then the 7 days of that week, and the amount of hours allocated to each day per project (let’s say by colX value). I was trying to transpose only by the days, but I’m not getting how to use the pivot for only a few values.
Advertisement
Answer
Here’s an example with your data:
with t as ( select ' name x ' name,' week1 ' week,' xxx ' colx,' yyy ' coly,' .... ' colxyz,' zzz ' colz,' 0 'day1, ' 5 'day2, ' 0 'day3, ' 0 'day4, ' 0 ' day5,' 0 ' day6, ' 0 ' day7 union all select ' name Y ',' week1 ',' xxx ',' yyy ',' .... ',' zzz ',' 4 ',' 0 ',' 0 ',' 0 ',' 0 ',' 0 ',' 0 ' union all select ' name Z ',' week1 ',' xxx ',' yyy ',' .... ',' zzz ',' 0 ',' 0 ',' 5 ',' 0 ',' 5 ',' 0 ',' 0 ' union all select ' name x ',' week2 ',' xxx ',' yyy ',' .... ',' zzz ',' 0 ',' 0 ',' 0 ',' 5 ',' 5 ',' 0 ',' 0 ' union all select ' name Y ',' week2 ',' xxx ',' yyy ',' .... ',' zzz ',' 5 ',' 0 ',' 0 ',' 0 ',' 4 ',' 0 ',' 0 ' union all select ' name Z ',' week2 ',' xxx ',' yyy ',' .... ',' zzz ',' 0 ',' 4 ',' 5 ',' 4 ',' 0 ',' 0 ',' 2 ' ) select * from t unpivot (dayVal for dayName in (day1, day2, day3, day4, day5, day6, day7)) t
See the microsoft documentation for more details on using unpivot.
The output looks like this:
