June 11, 2012 at 12:51 am
Table : Section Master
SectionId | SectionName | Position
18 | Advocacy | 1
20 | Project Management | 2
24 | Quality and Process | 3
Table : QuestionMaster
QuestionId | SectionId | QuestionName
10 | 18 | How would you like to rate us.?
11 | 20 | Adherance to deadlines ..?
12 | 20 | Quality of status reporting..?
13 | 20 | Quality of Project Tracking..?
14 | 24 | Quality of Infrastructure...?
15 | 24 | Quality of Documentation..?
16 | 24 | Dealing with requirements..?
Out put Expected (Use Position column from section master and count of section ids from question master)
Number | SectionId | QuestionId | SectionName | QuestionName
1.1 | 18 | 10 | Advocacy How would you like to rate us.?
2.1 | 20 | 11 | Project Management Adherance to deadlines ..?
2.2 | 20 | 12 | Project Management Quality of status reporting..?
2.3 | 20 | 13 | Project Management Quality of Project Tracking..?
3.1 | 24 | 14 | Quality and Process Quality of Infrastructure...?
3.2 | 24 | 15 | Quality and Process Quality of Documentation..?
3.3 | 24 | 16 | Quality and Process Dealing with requirements..?
any help on this Query would be appreaciated.
June 11, 2012 at 1:51 am
Thanks for the sample data and expected results, but one thing I would advise is to post them in a consumable format, the second link in my signature block details on how to do this, but for reference I have also attached my consumable formatted data.
declare @sectionmaster table (sectionid int, sectionname nvarchar(50), position int)
declare @questionmaster table (questionid int, sectionid int, questionname nvarchar(100))
insert into @sectionmaster (sectionid, sectionname, position) values (18,'Advocacy',1)
insert into @sectionmaster (sectionid, sectionname, position) values (20,'Project Management',2)
insert into @sectionmaster (sectionid, sectionname, position) values (24,'Quality and Process',3)
insert into @questionmaster (questionid, sectionid, questionname) values (10,18,'How would you like to rate us.?')
insert into @questionmaster (questionid, sectionid, questionname) values (11,20,'Adherance to deadlines ..?')
insert into @questionmaster (questionid, sectionid, questionname) values (12,20,'Quality of status reporting..?')
insert into @questionmaster (questionid, sectionid, questionname) values (13,20,'Quality of Project Tracking..?')
insert into @questionmaster (questionid, sectionid, questionname) values (14,24,'Quality of Infrastructure...?')
insert into @questionmaster (questionid, sectionid, questionname) values (15,24,'Quality of Documentation..?')
insert into @questionmaster (questionid, sectionid, questionname) values (16,24,'Dealing with requirements..?')
And the output can be acheived with the following
SELECT
CONVERT(NVARCHAR,DENSE_RANK() OVER(ORDER BY sm.SectionID))+'.'+CONVERT(NVARCHAR,ROW_NUMBER() OVER(PARTITION BY qm.SectionID ORDER BY qm.QuestionID)) AS Number,
sm.SectionID,
qm.QuestionID,
sm.SectionName,
qm.QuestionName
FROM
@sectionmaster sm
INNER JOIN
@questionmaster qm
on
sm.sectionid = qm.sectionid
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply