how to use pivot table query in sql server 2008

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

im use pivot table query for display the data...

in the Abc table 4 columns like SID (auto increment), StudentID, Attendance, Day.

SQL

select * from Abc
pivot (max (Attendance) for Day in ([Day1],[Day2],[Day3])) as DayWise

Output :-

 SID    StudentID    Day1   Day2   Day3
----------------------------------------
  1       101         A     null   null
  2       101        null    A     null
  3       101        null   null    A
  4       102         A     null   null
  5       103        null    A     null

But i want this type of output :-

StudentID    Day1   Day2   Day3
---------------------------------
101           A      A      A
102           A      A     null

So how can i do this....
Thanx in advance...

Posted 30-May-14 1:16am

Harnis Findoliya

Updated 30-May-14 1:23am

v4

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Awadhendra Tripathi · Answer 1 · 2014-05-30T01:31:00

Solution 1

Please refer this site i think you may find your desired output

Simple Way To Use Pivot In SQL Query[^]

http://blog.sqlauthority.com/2008/05/22/sql-server-pivot-table-example/[^]

Posted 30-May-14 1:31am

Awadhendra Tripathi

Updated 30-May-14 1:33am

v2

Comments

Harnis Findoliya 30-May-14 7:44am

yes i learn it... but when im take the primary key in the table then it is not work well like above example...

Animesh Datta · Answer 2 · 2014-05-30T01:44:00

Solution 2

Hello ,
Refer: How to Display student attendance using SQL Pivot
thanks

Posted 30-May-14 1:44am

Animesh Datta

Comments

Harnis Findoliya 30-May-14 7:47am

yes i learn it... but when im take the primary key in the table then it is not work well like above example...

Animesh Datta 30-May-14 8:03am

what problem then showing ?

Harnis Findoliya 30-May-14 8:21am

i want to show the one student attendance in only one row...

Peter Leow · Answer 3 · 2014-05-30T03:37:00

SQL

select distinct studentid,
(select attendance from abc where day='Day1' and abc.studentid=t1.studentid) day1,
(select attendance from abc where day='Day2' and abc.studentid=t1.studentid) day2,
(select attendance from abc where day='Day3' and abc.studentid=t1.studentid) day3
from abc t1