x

how to convert row data into column dynamically in sql-server with rank function

I want to convert row data into columns dynamically from two tables.

Plz help me. i have data in following format from two tables

Reg no      Title              Marks Obt     Max Marks
10900231    Asgn 1             10.00         15
10900231    Asgn 2             10.00         15
10900231    Asgn 3             5.00          15
10900231    P4                 75.00         100
10900231    P5                 65.00         100
10900231    P6                 80.00         100
10900231    P7                 80.00         100

10900232    Asgn 1             12.00         15
10900232    Asgn 2             13.00         15
10900232    Asgn 3             5.00          15
10900232    P4                 75.00         100
10900232    P5                 65.00         100
10900232    P6                 80.00         100
10900232    P7                 80.00         100

But i want to result in follwing format to dynamically make columns:

Reg NO Asgn 1 Asgn 2 Asgn 3 P4 P5 P6 P7
10900231 10 10 5 75 65 80 80
10900232 12 13 5 75 65 80 80
more ▼

asked Apr 07 '10 at 07:52 AM in Default

Manpreet thakur gravatar image

Manpreet thakur
1 3 3 3

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

Based on the content of your question, you want to use PIVOT.

Based on the title of your question, I have no idea what you're asking, and you'll have to expand what you've written to at least include some examples.

Edit ->

Having seen your update, you definitely want to use PIVOT. If you do not know what the source data range will be before you pivot, then you can use dynamic SQL to return the result. You can find an example of that in my answer to this question.

more ▼

answered Apr 07 '10 at 07:59 AM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 87

(comments are locked)
10|1200 characters needed characters left

Form me same secanario i have to use dynamic sql but i cant use dynamic sql in view can any one give solution to my scanario

more ▼

answered May 06 '10 at 01:06 AM

premsingh gravatar image

premsingh
1

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1933
x76
x7

asked: Apr 07 '10 at 07:52 AM

Seen: 4949 times

Last Updated: Apr 08 '10 at 08:29 AM