Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL stored procedure

Posted on 2010-01-11
6
Medium Priority
?
204 Views
Last Modified: 2012-05-08
Hi There,

I have a sql stored procedure which when I execute  gives me the following data
where
Q is a question and
Q1A is answer for Q1

ID   Month  Q1  Q1A    Q2     Q2A   Q3   Q3A   Q4   Q4A     Q5     Q5A        

I now want to create anothet stored procedure where the data is displayed as

ID      Month     QText    QA


Is this possible?

Could you please help me in creating this stored procedure2

Thanks

YRKS
0
Comment
Question by:YRKS
  • 3
  • 2
6 Comments
 

Author Comment

by:YRKS
ID: 26286348
I donot want to start from scratch can I use the procedure1 to extract the data the was I want.?  Any help would be greatly appreciated.
Thanks
0
 
LVL 10

Expert Comment

by:lof
ID: 26287259
Depending on how you store the data you can:

use CTE and query it multiple times with unions for each QA pair
use Unpivot feature
rebuild everything the stored procedure

let us see the code you have at the moment so we can give more accurate advice
0
 

Author Comment

by:YRKS
ID: 26287884
SELECT  DISTINCT
      cs.CSID
      ,lmn.I_Num
      ,CASE WHEN (lmn.Owner_Group_ID = 'abc' OR lmn.Owner_Group_ID = 'xyz') THEN 1 ELSE 0 END as sd_created
      ,lmn.Contact_Company
      ,DATEADD(month,DATEDIFF(month,0,cs.SCDate),0) as Survey_Month
      ,cs.SurveySentDate
      ,cs.SCDate
      ,'' as comments --ISNULL(CONVERT(varchar(1000), sa.FullAnswer),'') as comments

      ,q1.question as Question_1
      ,q1.question_score_cd as Question_1_Score
      ,q1.score_name as Question_1_Rating
        
      ,q2.question as Question_2
      ,q2.question_score_cd as Question_2_Score
      ,q2.score_name as Question_2_Rating
 
      ,q3.question as Question_3
      ,q3.question_score_cd as Question_3_Score
      ,q3.score_name as Question_3_Rating
 
      ,q4.question as Question_4
      ,q4.question_score_cd as Question_4_Score
      ,q4.score_name as Question_4_Rating
 
      ,q5.question as Question_5
      ,q5.question_score_cd as Question_5_Score
      ,q5.score_name as Question_5_Rating
 
FROM        
            (SELECT * FROM dbo.KS_SRV_CS cst
                  WHERE cst.SCDate BETWEEN @StartDate and @EndDate) cs
            INNER JOIN dbo.KS_SRV_SA sa
                  ON sa.CSI_ID = cs.CSI_ID
            INNER JOIN dbo.KS_SRV_SQ  sq
                  ON sq.instanceId = sa.QI_ID
            INNER JOIN dbo.LMN lmn
                  ON cs.Attribute5 = lmn.I_Num
            INNER JOIN (select * from udf_split(@company,'|'))  comp
                  ON lmn.Contact_Company = comp.[Value]
            LEFT JOIN
                  (
                        SELECT cs.CSID
                              ,sq.question
                              ,sa.Score as question_score_cd
                              ,sst.score_name as score_name
                        FROM        
                              dbo.KS_SRV_SQ sq
                              INNER JOIN dbo.KS_SRV_SA sa
                                    ON sq.instanceId = sa.QI_ID
                              INNER JOIN dbo.KS_SRV_CS cs
                                    ON sa.CSI_ID = cs.CSI_ID
                              INNER JOIN dbo.SSTs sst
                                    ON sa.Score = sst.score_cd
                        WHERE
                              sq.Question = 'My Question1'
                  ) q1 ON cs.CSID = q1.CSID

            LEFT JOIN
                  (
                        SELECT cs.CSID
                              ,sq.question
                              ,sa.Score as question_score_cd
                              ,sst.score_name as score_name
                        FROM        
                              dbo.KS_SRV_SQ sq
                              INNER JOIN dbo.KS_SRV_SA sa
                                    ON sq.instanceId = sa.QI_ID
                              INNER JOIN dbo.KS_SRV_CS cs
                                    ON sa.CSI_ID = cs.CSI_ID
                              INNER JOIN dbo.SSTs sst
                                    ON sa.Score = sst.score_cd
                        WHERE
                              sq.Question = 'My Question2 '
                  ) q2 ON cs.CSID = q2.CSID

            LEFT JOIN
                  (
                        SELECT cs.CSID
                              ,sq.question
                              ,sa.Score as question_score_cd
                              ,sst.score_name as score_name
                        FROM        
                              dbo.KS_SRV_SQ sq
                              INNER JOIN dbo.KS_SRV_SA sa
                                    ON sq.instanceId = sa.QI_ID
                              INNER JOIN dbo.KS_SRV_CS cs
                                    ON sa.CSI_ID = cs.CSI_ID
                              INNER JOIN dbo.SSTs sst
                                    ON sa.Score = sst.score_cd
                        WHERE
                              sq.Question = 'My Question3'
                  ) q3 ON cs.CSID = q3.CSID

            LEFT JOIN
                  (
                        SELECT cs.CSID
                              ,sq.question
                              ,sa.Score as question_score_cd
                              ,sst.score_name as score_name
                        FROM        
                              dbo.KS_SRV_SQ sq
                              INNER JOIN dbo.KS_SRV_SA sa
                                    ON sq.instanceId = sa.QI_ID
                              INNER JOIN dbo.KS_SRV_CS cs
                                    ON sa.CSI_ID = cs.CSI_ID
                              INNER JOIN dbo.SSTs sst
                                    ON sa.Score = sst.score_cd
                        WHERE
                              sq.Question = My Question4'
                  ) q4 ON cs.CSID = q4.CSID

            LEFT JOIN
                  (
                        SELECT cs.CSID
                              ,sq.question
                              ,sa.Score as question_score_cd
                              ,sst.score_name as score_name
                        FROM        
                              dbo.KS_SRV_SQ sq
                              INNER JOIN dbo.KS_SRV_SA sa
                                    ON sq.instanceId = sa.QI_ID
                              INNER JOIN dbo.KS_SRV_CS cs
                                    ON sa.CSI_ID = cs.CSI_ID
                              INNER JOIN dbo.SSTs sst
                                    ON sa.Score = sst.score_cd
                        WHERE
                              sq.Question = My Question 5'
                  ) q5 ON cs.CSID = q5.CSID


Hope this helps.

Thanks

YRKS
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 10

Accepted Solution

by:
lof earned 2000 total points
ID: 26287940
try something like that.
SELECT  DISTINCT 
      cs.CSID
      ,lmn.I_Num
      ,CASE WHEN (lmn.Owner_Group_ID = 'abc' OR lmn.Owner_Group_ID = 'xyz') THEN 1 ELSE 0 END as sd_created
      ,lmn.Contact_Company
      ,DATEADD(month,DATEDIFF(month,0,cs.SCDate),0) as Survey_Month
      ,cs.SurveySentDate
      ,cs.SCDate
      ,'' as comments --ISNULL(CONVERT(varchar(1000), sa.FullAnswer),'') as comments

      ,q1.question as Question_1
      ,q1.question_score_cd as Question_1_Score
      ,q1.score_name as Question_1_Rating
        
FROM         
            (SELECT * FROM dbo.KS_SRV_CS cst
                  WHERE cst.SCDate BETWEEN @StartDate and @EndDate) cs
            INNER JOIN dbo.KS_SRV_SA sa
                  ON sa.CSI_ID = cs.CSI_ID
            INNER JOIN dbo.KS_SRV_SQ  sq
                  ON sq.instanceId = sa.QI_ID
            INNER JOIN dbo.LMN lmn
                  ON cs.Attribute5 = lmn.I_Num 
            INNER JOIN (select * from udf_split(@company,'|'))  comp
                  ON lmn.Contact_Company = comp.[Value]
            LEFT JOIN
                  (
                        SELECT cs.CSID
                              ,sq.question 
                              ,sa.Score as question_score_cd 
                              ,sst.score_name as score_name
                        FROM         
                              dbo.KS_SRV_SQ sq
                              INNER JOIN dbo.KS_SRV_SA sa
                                    ON sq.instanceId = sa.QI_ID
                              INNER JOIN dbo.KS_SRV_CS cs
                                    ON sa.CSI_ID = cs.CSI_ID
                              INNER JOIN dbo.SSTs sst
                                    ON sa.Score = sst.score_cd
                        WHERE 
                              sq.Question in ('My Question1', 'My Question2', 'My Question3')
                  ) q1 ON cs.CSID = q1.CSID

Open in new window

0
 
LVL 7

Expert Comment

by:aplusexpert
ID: 26291413
Hi,


TO transform the table, first create a table in SP and enter row for each question.


Thanks.
0
 

Author Comment

by:YRKS
ID: 26318142
I have just submitted a question related to this. I need to display the null values too.

Thanks

YRKS
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
Exchange database can often fail to mount thereby halting the work of all users connected to it. Finding out why database isn’t mounting is crucial and getting the server back online. Stellar Phoenix Mailbox Exchange Recovery is a champion product t…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

564 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