how to display data in one coloumn in horizontal manner?

Question

0.00/5 (No votes)

See more:

hi,i am new ti c# windows from apllication development . i was tried using pivot .but i am not getting can hepl me . in this below link tells that only paticular year. but i want to disaply different years in crystal report .
i have tables like this :
create table student(studentid int primary key not null,name varchar(10),class varchar(10)..so on diffrent coloumns i was created )
create table studentreport(studentid int,date datetime,score varchar(10))
table 1 :
student id name class ...... so on
1 x 5th
2 y 6th
3 z 7th
4 a 8th... so on
table 2:
studentid date score

1 2-6-2012 40

2 5-7-2012 30

3 4-8-2012 35
1 4-6-2013 12
1 6-8-2013 23
2 4-7-2103 40
note :like this when ever year was insert into table .that should be upadate in crystal report in ascending order
like i want report like this seen below
studentid:1 name: x class :5th
------------------------------------------------------------------------------------------
month 2012 2013
score score
40 12
23

i was create parameter that is working for upper part means studentid ,class,name.but problem is below one shown in report i cant able to display years in horizontall manner aleast help that one. my report this like a indiviual report i wnt to genrate plz help me .plz hepl me

thanks advance

Posted 20-Feb-13 19:09pm

Member 9846414

Updated 21-Feb-13 0:15am

v4

Add a Solution

1 solution

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)

Hrushikesh_phapale · Answer 1 · 2013-02-21T02:10:00

Solution 1

SQL

Declare @year varchar(max)=''
 SELECT
       @year= Stuff((SELECT ', [' + cast(s as varchar(max))+']'
        FROM
        (SELECT DISTINCT DATEPART(YYYY,Sdate) as s FROM  dbo.StudDet
         ) x
        For XML PATH ('')),1,1,'')

exec('select Months,'+@year+'
from
(
  select cast(DATEPART(MONTH,Sdate) as varchar(max)) as Months,Score,
  DATEPART(year,Sdate) as st
  from dbo.StudDet
) x
pivot
(
  max(Score)
  for st in('+@year+')
)p ')

Posted 21-Feb-13 2:10am

Hrushikesh_phapale

Comments

Member 9846414 22-Feb-13 2:34am

thank u verry much thanks a lot . but it display all the details . iwant to dispaly only particular id how where id ="1" this where i should write tell me plz

[no name] 22-Feb-13 3:32am

You need to place ID in two different places..
1.=> SELECT DISTINCT DATEPART(YYYY,Sdate) as s FROM dbo.StudDet where id='1'
2.=>exec('select Months,'+@year+'
from
(
select cast(DATEPART(MONTH,Sdate) as varchar(max)) as Months,Score,
DATEPART(year,Sdate) as st
from dbo.StudDet where ID='1'
) x
pivot
(
max(Score)
for st in('+@year+')
)p ')

Member 9846414 22-Feb-13 6:05am

Actuvally in my id having integer as well as string .i was writen like this where id='1A'
when i was enetr like this string was not ascepting ,thats y i use like to avoid that one like this (SELECT DISTINCT DATEPART(YYYY,Sdate) as s FROM dbo.StudDet where id like '%"1A'%"
it shows error at here select cast(DATEPART(MONTH,Sdate) as varchar(max)) as Months,Score,
DATEPART(year,Sdate) as st
from dbo.StudDet where id like '%"1A'%"
help me .thank u very much for helping me

[no name] 22-Feb-13 7:05am

Put all these code into store procedure.

Declare @year varchar(max)=''

SELECT
@year= Stuff((SELECT ', [' + cast(s as varchar(max))+']'
FROM
(SELECT DISTINCT DATEPART(YYYY,Sdate) as s FROM dbo.StudDet
where id='4'
) xs
For XML PATH ('')),1,1,'')

exec('select Months,'+@year+'
from
(
select cast(DATEPART(MONTH,Sdate) as varchar(max)) as Months,Score,
DATEPART(year,Sdate) as st
from dbo.StudDet where id=''4''
) x
pivot
(
max(Score)
for st in('+@year+')
)p ')

Member 9846414 28-Feb-13 1:38am

thanku very much hepling me i got it . displaying in horizontall manner but i want to dsipaly rin range thats y i use formula feild in that i have writen like this toText (year(CurrentDate)) + "-" + toText (year(CurrentDate) + 1) but error generating like boolean variable required her .how to solve this error plz help me