Solved

# Convert rows to columns

Posted on 2011-02-22
845 Views
Hi,

I have a SP that produces this output:

YearWeek Shift TotalTime
--------- ------ -----------
2011/11   <NULL> 600
2011/11   1      14875
2011/11   2      2430
2011/11   3      1445
2011/11   4      12540
2011/12   1      14540
2011/12   2      2165
2011/12   3      1580
2011/12   4      9110
2011/13   1      15455
2011/13   2      2430
2011/13   3      1580
2011/13   4      17810
2011/14   1      11385
2011/14   2      1955
2011/14   3      1520
2011/14   4      14840
...

Now I want to put the weeks in columns, like this:

Shift       2011/11  2011/12  2011/13  2011/14  2011/15 ...
<NULL>       600                          ...
1                14875    14540         ...
2                 2430      2165          ...
3                 1445      1580          ...
4                12540     9110          ...

I don't know how many colums I will have.

Any help? I'm trying some solutions that I have found on web but with no luck.

Thanks
Jorge Paulino
0
Question by:jpaulino
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 2

LVL 10

Expert Comment

ID: 34950592
HI,

You can use Pivot for this purpose. Please read http://msdn.microsoft.com/en-us/library/ms177410(v=SQL.90).aspx for details.

Asim
0

LVL 48

Author Comment

ID: 34950614
Thanks for the quick reply, but the problem is that I don't know how many columns I will have.
0

LVL 10

Accepted Solution

Asim Nazir earned 250 total points
ID: 34950644
0

LVL 45

Assisted Solution

Kent Olsen earned 250 total points
ID: 34951084
Hi jpaulino,

There are a couple of articles on the DB2 forum that describe recursive SQL and how to use it for converting row to columns, or columns to rows.  Except for DB2 using the double-pipe (||) and SQL Server using plus (+) for concatenation, the syntax is the same for DB2 and SQL Server.

The technique will work for you if you can't use pivot tables.

http://www.experts-exchange.com/Database/DB2/A_3618-Recursive-SQL-in-DB2-Converting-rows-to-columns.html

http://www.experts-exchange.com/Database/DB2/A_3629-Recursive-SQL-in-DB2-Converting-Columns-to-Rows.html

Good Luck,
Kent
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
###### Suggested Courses
Course of the Month11 days, 9 hours left to enroll