June 11, 2012 at 12:50 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 5:34 am
Lookup ROW_NUMBER in books online for more information
IF OBJECT_ID('TempDB..#SectionMaster') IS NOT NULL DROP TABLE #SectionMaster
CREATE TABLE #SectionMaster
(SectionID INT NOT NULL PRIMARY KEY CLUSTERED,
SectionName VARCHAR(50) NOT NULL,
Position INT NOT NULL)
IF OBJECT_ID('TempDB..#QuestionMaster') IS NOT NULL DROP TABLE #QuestionMaster
CREATE TABLE #QuestionMaster
(QuestionID INT NOT NULL PRIMARY KEY CLUSTERED,
SectionID INT NOT NULL,
QuestionName VARCHAR(256) NOT NULL)
INSERT INTO #SectionMaster
SELECT 18, 'Advocacy', 1
UNION ALL SELECT 20, 'Project Management', 2
UNION ALL SELECT 24, 'Quality and Process', 3
INSERT INTO #QuestionMaster
SELECT 10, 18, 'How would you like to rate us.?'
UNION ALL SELECT 11, 20, 'Adherance to deadlines ..?'
UNION ALL SELECT 12, 20, 'Quality of status reporting..?'
UNION ALL SELECT 13, 20, 'Quality of Project Tracking..?'
UNION ALL SELECT 14, 24, 'Quality of Infrastructure...?'
UNION ALL SELECT 15, 24, 'Quality of Documentation..?'
UNION ALL SELECT 16, 24, 'Dealing with requirements..?'
SELECT CAST(SM.Position AS VARCHAR(2)) + '.' + CAST(ROW_NUMBER() OVER (PARTITION BY SM.SectionID ORDER BY SM.Position,QM.QuestionID) AS VARCHAR(2)) AS Number,
SM.SectionID,
QM.QuestionID,
SM.SectionName,
QM.QuestionName
FROM #QuestionMaster QM
INNER JOIN #SectionMaster SM ON QM.SectionID = SM.SectionID
June 11, 2012 at 5:59 am
Duplicate post, didn't see this, this morning when looking through the topics
http://www.sqlservercentral.com/Forums/Topic1313689-145-1.aspx#bm1313709
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply