subquery in ms sql server

Hi

I have a problem is ms sql server 2003. code is below and result is showing -
NationalPoints   M    Y
88.33      4      2009
83.02      3      2009
41.67      2      2009.
but i want
all NationalPoints in separate column. like
NationalPoints1  NationalPoints2 NationalPoints3
88.33                  83.02                41.67
and Month name and Year as well.
Please can any one help me ASAP.
Thanks in advance  

arifpervezAsked:
Who is Participating?
 
RiteshShahCommented:
select distinct
(select NationalPoints from temp1 where M=datepart(m,dateadd(m,0,getdate())) and y=datepart(yy,dateadd(mm,0,getdate())) ) val1,
(select NationalPoints from temp1 where M=datepart(m,dateadd(m,-1,getdate())) and y=datepart(yy,dateadd(mm,-1,getdate())) ) val2,
(select NationalPoints from temp1 where M=datepart(m,dateadd(m,-2,getdate())) and y=datepart(yy,dateadd(mm,-2,getdate())) ) val3,
(select NationalPoints from temp1 where M=datepart(m,dateadd(m,-3,getdate())) and y=datepart(yy,dateadd(mm,-3,getdate())) ) val4,
(select NationalPoints from temp1 where M=datepart(m,dateadd(m,-4,getdate())) and y=datepart(yy,dateadd(mm,-4,getdate())) ) val5,
(select NationalPoints from temp1 where M=datepart(m,dateadd(m,-5,getdate())) and y=datepart(yy,dateadd(mm,-5,getdate())) ) val6
from temp1 t
0
 
RiteshShahCommented:
you are looking for pivot

here is my small article for this task.

http://www.sqlhub.com/2009/03/dynamic-pivot-with-where-condition-in.html
0
 
arifpervezAuthor Commented:
Hi Ritesh
Please can you give me some idea how i use pivot code according to my query. i don't understand how to do that.

thanks in advance
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
arifpervezAuthor Commented:
My Code Is

SELECT @Cols=@Cols+ '['+s.(CONVERT(numeric(5, 2), SUM(dbo.QuestionDetails.CentreScore) /
      SUM(dbo.QuestionCategory.PointsAvailable) * 100) +']'+ ', ' FROM

(SELECT DISTINCT CONVERT(numeric(5, 2), SUM(dbo.QuestionDetails.CentreScore) /
      SUM(dbo.QuestionCategory.PointsAvailable) * 100))
              AS NationalPoints,       
      MONTH(dbo.QuestionDetails.EnquiryDate) AS M,
      YEAR(dbo.QuestionDetails.EnquiryDate) AS Y       

FROM        dbo.QuestionDetails INNER JOIN
            dbo.QuestionCategory ON dbo.QuestionDetails.QCat = dbo.QuestionCategory.LID INNER JOIN
            dbo.SurveyCategory ON dbo.QuestionDetails.SurveyTypeID = dbo.SurveyCategory.LID

GROUP BY MONTH(dbo.QuestionDetails.EnquiryDate), YEAR(dbo.QuestionDetails.EnquiryDate) ) AS s
0
 
RiteshShahCommented:
unfortunately I don't have SQL server access right now, let me try to do it based on sysntax in my mind. please provide me with the simple query which you are using to get

NationalPoints   M    Y
88.33      4      2009
83.02      3      2009
41.67      2      2009.
0
 
arifpervezAuthor Commented:
SELECT Top 3(CONVERT(numeric(5, 2), SUM(dbo.QuestionDetails.CentreScore) /
      SUM(dbo.QuestionCategory.PointsAvailable) * 100))
              AS NationalPoints,       
      MONTH(dbo.QuestionDetails.EnquiryDate) AS M,
      YEAR(dbo.QuestionDetails.EnquiryDate) AS Y       

FROM        dbo.QuestionDetails INNER JOIN
            dbo.QuestionCategory ON dbo.QuestionDetails.QCat = dbo.QuestionCategory.LID INNER JOIN
            dbo.SurveyCategory ON dbo.QuestionDetails.SurveyTypeID = dbo.SurveyCategory.LID

GROUP BY MONTH(dbo.QuestionDetails.EnquiryDate), YEAR(dbo.QuestionDetails.EnquiryDate)

ORDER BY MONTH(dbo.QuestionDetails.EnquiryDate) Desc, YEAR(dbo.QuestionDetails.EnquiryDate) Desc
0
 
arifpervezAuthor Commented:
Hi Ritesh

are you able to make the solution

thanks

0
 
RiteshShahCommented:
well, see below solution, It may work, I can't check it thoroughly as I don't have SQL Server access right now but it should work. have a look

DECLARE @Cols NVARCHAR(4000)
SET @Cols=''
--make column list for PIVOT
SELECT @Cols=@Cols+ '['+s.NationalPoints +']'+ ', ' FROM
(
---------------------
select distinct NationalPoints from
(
SELECT Top 3(CONVERT(numeric(5, 2), SUM(dbo.QuestionDetails.CentreScore) / 
      SUM(dbo.QuestionCategory.PointsAvailable) * 100))
              AS NationalPoints,       
      MONTH(dbo.QuestionDetails.EnquiryDate) AS M, 
      YEAR(dbo.QuestionDetails.EnquiryDate) AS Y       
 
FROM        dbo.QuestionDetails INNER JOIN
            dbo.QuestionCategory ON dbo.QuestionDetails.QCat = dbo.QuestionCategory.LID INNER JOIN
            dbo.SurveyCategory ON dbo.QuestionDetails.SurveyTypeID = dbo.SurveyCategory.LID
 
GROUP BY MONTH(dbo.QuestionDetails.EnquiryDate), YEAR(dbo.QuestionDetails.EnquiryDate)
ORDER BY MONTH(dbo.QuestionDetails.EnquiryDate) Desc, YEAR(dbo.QuestionDetails.EnquiryDate) Desc
)tab
---------------------
) AS s
--remove last comma from column list
SET @Cols=LEFT(@Cols,LEN(@Cols)-1)
--create pivot query as we have just added distinct year list in @Cols variable
SET @Cols='SELECT * from 
(
SELECT Top 3(CONVERT(numeric(5, 2), SUM(dbo.QuestionDetails.CentreScore) / 
      SUM(dbo.QuestionCategory.PointsAvailable) * 100))
              AS NationalPoints,       
      MONTH(dbo.QuestionDetails.EnquiryDate) AS M, 
      YEAR(dbo.QuestionDetails.EnquiryDate) AS Y       
 
FROM        dbo.QuestionDetails INNER JOIN
            dbo.QuestionCategory ON dbo.QuestionDetails.QCat = dbo.QuestionCategory.LID INNER JOIN
            dbo.SurveyCategory ON dbo.QuestionDetails.SurveyTypeID = dbo.SurveyCategory.LID
 
GROUP BY MONTH(dbo.QuestionDetails.EnquiryDate), YEAR(dbo.QuestionDetails.EnquiryDate)
ORDER BY MONTH(dbo.QuestionDetails.EnquiryDate) Desc, YEAR(dbo.QuestionDetails.EnquiryDate) Desc
) up
PIVOT (count(NationalPoints) for NationalPoints in ('+@cols+')) AS pivo'
--print query and check
print @cols
EXECUTE sp_executeSQL @Cols

Open in new window

0
 
arifpervezAuthor Commented:
i am getting a error message. i attached the error message :


Warning: Null value is eliminated by an aggregate or other SET operation.
SELECT * from 
(
SELECT Top 3(CONVERT(numeric(5, 2), SUM(dbo.QuestionDetails.CentreScore) / 
      SUM(dbo.QuestionCategory.PointsAvailable) * 100))
              AS NationalPoints,       
      MONTH(dbo.QuestionDetails.EnquiryDate) AS M, 
      YEAR(dbo.QuestionDetails.EnquiryDate) AS Y       
 
FROM        dbo.QuestionDetails INNER JOIN
            dbo.QuestionCategory ON dbo.QuestionDetails.QCat = dbo.QuestionCategory.LID INNER JOIN
            dbo.SurveyCategory ON dbo.QuestionDetails.SurveyTypeID = dbo.SurveyCategory.LID
 
GROUP BY MONTH(dbo.QuestionDetails.EnquiryDate), YEAR(dbo.QuestionDetails.EnquiryDate)
ORDER BY MONTH(dbo.QuestionDetails.EnquiryDate) Desc, YEAR(dbo.QuestionDetails.EnquiryDate) Desc
) up
PIVOT (count(NationalPoints) for NationalPoints in ([41.67], [83.02], [88.33])) AS pivo
Server: Msg 170, Level 15, State 1, Line 16
Line 16: Incorrect syntax near 'PIVOT'.

Open in new window

0
 
RiteshShahCommented:
have you run the full code I gave you or did you modify it?
0
 
arifpervezAuthor Commented:
I run your full code what your gave me. just i modify the '+s.NationalPoints+' to +convert(varchar(4000),s.NationalPoints)+. other wise it is giving me error msg like below.

thanks
Server: Msg 8114, Level 16, State 5, Line 4
Error converting data type varchar to numeric.

Open in new window

0
 
RiteshShahCommented:
can you do me a favor? Since I can't access SQL Server, can you run my code and print @cols variable rather than EXECUTE it. and send me back the output.
0
 
arifpervezAuthor Commented:
If I run your code exactly than i got an error msg below -
Server: Msg 8114, Level 16, State 5, Line 4
Error converting data type varchar to numeric.

than i changed the s.NationalPoints to convert(varchar(4000),s.NationalPoints). When i convert that and only print the @cols variable then the output is below -  
Warning: Null value is eliminated by an aggregate or other SET operation.
SELECT * from 
(
SELECT Top 3(CONVERT(numeric(5, 2), SUM(dbo.QuestionDetails.CentreScore) / 
      SUM(dbo.QuestionCategory.PointsAvailable) * 100))
              AS NationalPoints,       
      MONTH(dbo.QuestionDetails.EnquiryDate) AS M, 
      YEAR(dbo.QuestionDetails.EnquiryDate) AS Y       
 
FROM        dbo.QuestionDetails INNER JOIN
            dbo.QuestionCategory ON dbo.QuestionDetails.QCat = dbo.QuestionCategory.LID INNER JOIN
            dbo.SurveyCategory ON dbo.QuestionDetails.SurveyTypeID = dbo.SurveyCategory.LID
 
GROUP BY MONTH(dbo.QuestionDetails.EnquiryDate), YEAR(dbo.QuestionDetails.EnquiryDate)
ORDER BY MONTH(dbo.QuestionDetails.EnquiryDate) Desc, YEAR(dbo.QuestionDetails.EnquiryDate) Desc
) up
PIVOT (count(NationalPoints) for NationalPoints in ([41.67], [83.02], [88.33])) AS pivo

Open in new window

0
 
RiteshShahCommented:
what happens if you try to run below code?



SELECT * from 
(
SELECT Top 3(CONVERT(numeric(5, 2), SUM(dbo.QuestionDetails.CentreScore) / 
      SUM(dbo.QuestionCategory.PointsAvailable) * 100))
              AS NationalPoints,       
      MONTH(dbo.QuestionDetails.EnquiryDate) AS M, 
      YEAR(dbo.QuestionDetails.EnquiryDate) AS Y       
 
FROM        dbo.QuestionDetails INNER JOIN
            dbo.QuestionCategory ON dbo.QuestionDetails.QCat = dbo.QuestionCategory.LID INNER JOIN
            dbo.SurveyCategory ON dbo.QuestionDetails.SurveyTypeID = dbo.SurveyCategory.LID
 
GROUP BY MONTH(dbo.QuestionDetails.EnquiryDate), YEAR(dbo.QuestionDetails.EnquiryDate)
ORDER BY MONTH(dbo.QuestionDetails.EnquiryDate) Desc, YEAR(dbo.QuestionDetails.EnquiryDate) Desc
) up
PIVOT (count(NationalPoints) for NationalPoints in ([41.67], [83.02], [88.33])) AS pivo

Open in new window

0
 
arifpervezAuthor Commented:
you put the NationalPoints value. I run the above code and get below error msg:

Server: Msg 170, Level 15, State 1, Line 16
Line 16: Incorrect syntax near 'PIVOT'.
0
 
RiteshShahCommented:
just for testing, can you run below script?



create table temp1
(
NationalPoints varchar(5),
M int,
Y int
)
 
insert into temp1
select '41.67',4,2009 union all
select '83.02',3,2009 union all
select '88.33',2,2009 
 
SELECT * from 
(
SELECT NationalPoints,M,Y FROM Temp1
) as up
PIVOT (count(m) for NationalPoints in ([41.67], [83.02], [88.33])) AS pivo

Open in new window

0
 
arifpervezAuthor Commented:
I run the above code and get same error msg:

Server: Msg 170, Level 15, State 1, Line 17
Line 17: Incorrect syntax near 'PIVOT'.

but one thing i am using sql server 2000. is any problem for that.

please help me.

0
 
RiteshShahCommented:
ufff, you have selected zone 'SQL Server 2005' so I thought you are using it. this won't work in SQL Server 2000 for SURE. You should have to put your question in MS SQL Server zone and have to specify the version first.
0
 
arifpervezAuthor Commented:
sorry for that. thanks for your help.
0
 
arifpervezAuthor Commented:
Hi Ritesh

Just I checked the SQL Server 2000 Books Online. Its showing pivot table. That means it support pivot. Please can you check the error if possible.

Thanks in advance
0
 
SharathData EngineerCommented:
If PIVOT won't works, then you have tp try like this.
select Y,
       (select NationalPoints from temp1 where M = 4) val1,
       (select NationalPoints from temp1 where M = 3) val2,
       (select NationalPoints from temp1 where M = 2) val3
  from temp1
 group by Y

Open in new window

0
 
arifpervezAuthor Commented:
Hi Sharath

Thanks for your help. But what will happen after 6 months. I don't want to fix the month. I want last 3 months or last 6 months results.

Please help me.

0
 
SharathData EngineerCommented:
Can you provide some more sample data? and the expected result. The hardcoding can be avoided, but I want to know the requirement clearly.
0
 
arifpervezAuthor Commented:
Some sample data -

NationalPoints Month Year
88.33      4      2009
83.02      3      2009
41.67      2      2009
87.50      1      2009
54.99             11                 2008
33.98             10                 2008
76.40              09                2008

Expected Results
NP0 NP1 NP2 NP3 NP4 NP5
88.33     83.02      41.67   87.50 54.99  33.98  

I want to show last available 6 months data. might be within last 6 months 1 month data not available. that time it will show month 7 data. that means last top 6 months data i want to show.
I am trying to close my other question.

Thanks in advance
0
 
RiteshShahCommented:
hi, this will show you latest three months data, you can have it for six also.

select t.y,
(select NationalPoints from temp1 where M=datepart(m,getdate())) val1,
(select NationalPoints from temp1 where M=datepart(m,getdate())-1) val2,
(select NationalPoints from temp1 where M=datepart(m,getdate())-2) val3
from temp1 t
where y=2009
group by t.y

0
 
RiteshShahCommented:
this is for latest six months.


select t.y,
(select NationalPoints from temp1 where M=datepart(m,getdate())) val1,
(select NationalPoints from temp1 where M=datepart(m,getdate())-1) val2,
(select NationalPoints from temp1 where M=datepart(m,getdate())-2) val3,
(select NationalPoints from temp1 where M=datepart(m,getdate())-3) val4,
(select NationalPoints from temp1 where M=datepart(m,getdate())-4) val5,
(select NationalPoints from temp1 where M=datepart(m,getdate())-5) val6

from temp1 t
where y=2009
group by t.y

0
 
arifpervezAuthor Commented:
If Year = 2008 than what will happen. Because I want to showlast 6 months data.
0
 
RiteshShahCommented:
do you want latest six months data of given year?
0
 
arifpervezAuthor Commented:
In my table there is a field EnquiryDate. Based on the enquiry date I want to show last 6 months data. like EnquiryDate = 30/04/2009. So it will show last 6 months data. how can i will do this.

Thanks
0
 
arifpervezAuthor Commented:
No year is fixed.
0
 
arifpervezAuthor Commented:
Hi Ritesh

did you find any solution. Please help me.

Thanks
0
 
arifpervezAuthor Commented:
Hi Ritesh

Thank you very much for your help. I got my solution and its working fine.

Again Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.