?
Solved

subquery in ms sql server

Posted on 2009-04-29
32
Medium Priority
?
259 Views
Last Modified: 2012-05-06
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  

0
Comment
Question by:arifpervez
  • 18
  • 12
  • 2
32 Comments
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24258726
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
 

Author Comment

by:arifpervez
ID: 24258914
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
 

Author Comment

by:arifpervez
ID: 24258917
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
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
LVL 31

Expert Comment

by:RiteshShah
ID: 24258957
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
 

Author Comment

by:arifpervez
ID: 24259001
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
 

Author Comment

by:arifpervez
ID: 24259177
Hi Ritesh

are you able to make the solution

thanks

0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24259277
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
 

Author Comment

by:arifpervez
ID: 24259353
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
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24259390
have you run the full code I gave you or did you modify it?
0
 

Author Comment

by:arifpervez
ID: 24259413
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
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24259551
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
 

Author Comment

by:arifpervez
ID: 24259616
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
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24259689
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
 

Author Comment

by:arifpervez
ID: 24259721
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
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24259811
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
 

Author Comment

by:arifpervez
ID: 24259879
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
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24259895
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
 

Author Comment

by:arifpervez
ID: 24259938
sorry for that. thanks for your help.
0
 

Author Comment

by:arifpervez
ID: 24261981
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
 
LVL 41

Expert Comment

by:Sharath
ID: 24262712
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
 

Author Comment

by:arifpervez
ID: 24263420
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
 
LVL 41

Expert Comment

by:Sharath
ID: 24266141
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
 

Author Comment

by:arifpervez
ID: 24267780
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
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24267973
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
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24267975
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
 

Author Comment

by:arifpervez
ID: 24268001
If Year = 2008 than what will happen. Because I want to showlast 6 months data.
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24268010
do you want latest six months data of given year?
0
 

Author Comment

by:arifpervez
ID: 24268015
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
 

Author Comment

by:arifpervez
ID: 24268020
No year is fixed.
0
 

Author Comment

by:arifpervez
ID: 24268533
Hi Ritesh

did you find any solution. Please help me.

Thanks
0
 
LVL 31

Accepted Solution

by:
RiteshShah earned 1000 total points
ID: 24268607
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
 

Author Comment

by:arifpervez
ID: 24268681
Hi Ritesh

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

Again Thanks
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question