CODEWITHSUNDEEP
sql-server-2005
DotNetStudent
DotNetStudent

Reputation: 899

Vertical & Horizontal Tables in SQL Server 2005

Please condider the situation in which there are two tables, Body and File, in which every record of the first one has zero or more corresponding records in the second one:

BodyID | Body
-------------
1      | X
2      | Y

FileID | BodyID | File
------------------------
1      | 1      | A
2      | 1      | B
3      | 2      | C
4      | 2      | D
5      | 2      | E

Of course, it is pretty easy to join both tables and get something like

BodyID | FileID | Body | File
-----------------------------
1      | 1      | X    | A
1      | 2      | X    | B
2      | 3      | Y    | C
2      | 4      | Y    | D
2      | 5      | Y    | E

However, what I would like to be able to do is to transform this same table into an horizontal one, like

BodyID | Body | File1 | File2 | File3 | ...
-------------------------------------------
1      | X    | A     | B     | NULL  | ...
2      | Y    | C     | D     | E     | ...

where each column is replaced by NULL (or any other "meaningful" value) when there are no files. The problem is that I can neither use D-SQL nor cursors. Can anyone please help me out?

Upvotes: 1

Views: 256

Answers (1)

ratneshsinghparihar
ratneshsinghparihar

Reputation: 301

whenever you need to convert your rows into columns then use pivot.you can search msdn for pivot.

Upvotes: 2

Related Questions