Pivot(transpose) by some columns SQL

user_2572899 0
Viewed 54

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.

pivot sql-server sql
2 Answers

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: enter image description here

user_68689270

Have you checked out PIVOT?

@jarlh: tables have fields. Sometimes called columns, sometimes called fields.

user_111655630
Related