subquery in ms sql server

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

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

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

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.

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

Hi Ritesh

are you able to make the solution

thanks

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

Select allOpen in new window

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'.

Select allOpen in new window

have you run the full code I gave you or did you modify it?

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.

Select allOpen in new window

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.

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

Select allOpen in new window

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

Select allOpen in new window

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'.

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

Select allOpen in new window

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.

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.

sorry for that. thanks for your help.

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

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

Select allOpen in new window

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.

Can you provide some more sample data? and the expected result. The hardcoding can be avoided, but I want to know the requirement clearly.

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

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

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

If Year = 2008 than what will happen. Because I want to showlast 6 months data.

do you want latest six months data of given year?

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

No year is fixed.

Hi Ritesh

did you find any solution. Please help me.

Thanks

Hi Ritesh

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

Again Thanks