?
Solved

SQL stored procedure

Posted on 2010-01-11
6
Medium Priority
?
202 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

 
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

Industry Leaders: 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!

Question has a verified solution.

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

When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
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…

771 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