Combining Rows - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Combining Rows

Author

Topic

mgarret
Starting Member

14 Posts

Posted - 2013-06-05 : 16:36:35

Hi all—
I have written a query which produces a dataset which looks like this:

EXAMINEE_NBR Skill Round_1 Round_2 RATIONALE_2
435 A1: Identifying Family Members and Support Systems NULL 3 Text, Text, Text…….
435 A1: Identifying Family Members and Support Systems 2 NULL NULL
435 B1: Tracking the Sequence NULL 5 Text, Text, Text…….
435 B1: Tracking the Sequence 1 NULL NULL
435 B2: Searching for Exceptions NULL 4 Text, Text, Text…….
435 B2: Searching for Exceptions 1 NULL NULL
435 C1: Family Level Outcomes 3 NULL NULL
435 C2: Individual Level Outcomes NULL 3 Text, Text, Text…….
435 C2: Individual Level Outcomes 2 NULL NULL
435 C3: Child Well Being Outcomes NULL 2 Text, Text, Text…….
435 C3: Child Well Being Outcomes 2 NULL NULL
435 C4: Outcome Steps 3 NULL NULL

I need to modify the query both the Round_1 and Round_2 scores are appear on the first row.
So, something which looks like this:

EXAMINEE_NBR Skill Round_1 Round_2 RATIONALE_2
435 A1: Identifying Family Members and Support Systems 2 3 Text, Text, Text…….
435 B1: Tracking the Sequence 1 5 Text, Text, Text…….
435 B2: Searching for Exceptions 1 4 Text, Text, Text…….
435 C1: Family Level Outcomes 3 NULL NULL
435 C2: Individual Level Outcomes 2 3 Text, Text, Text…….
435 C3: Child Well Being Outcomes 2 2 Text, Text, Text…….
435 C4: Outcome Steps 3 NULL NULL

I am not sure how to write for this.
Do I need a type of “Group by”??
Any assistance is greatly appreciated.
Thanks!!!

Here is my code:
SELECT
distinct
aa.EXAMINEE_NBR,
s.SKILL_CATEGORY+CAST(s.CATEGORY_NBR AS char(1))+': ' + s.SKILL_TITLE AS Skill,
(select sd.score where aa.SBC_RND=1) as Round_1,
(select sd.score where aa.SBC_RND=2) as Round_2,
(select sd.RATIONALE where aa.SBC_RND=2) as RATIONALE_2
FROM [New_EEs].[dbo].[vw_SBC_Assessment_Assignments]aa
inner join
[New_EEs].[dbo].[vw_SBC_Score_Details] sd
on sd.ASSESSMENT_ID=aa.ASSESSMENT_ID
inner join
[New_EEs].[dbo].[SBC_Skills] s
on s.SKILL_NBR=sd.SKILL_NBR
where sd.SBC_Status>1
and aa.SBC_Type=1;

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-06-05 : 21:26:06

It was a little difficult to parse your dataset, so I had to guess your column names and data in each column.
You may have to adjust the data.
The following query should get you what you need:
[CODE]
DECLARE @TEMP TABLE (ExamNum VARCHAR(10), Skill VARCHAR(50), Round1 INT, Round2 INT, Rationale1 VARCHAR(20));
INSERT INTO @TEMP VALUES('435 A1:', 'Identifying Family Members and Support Systems', NULL, 3, 'Text, Text, Text…….');
INSERT INTO @TEMP VALUES('435 A1:', 'Identifying Family Members and Support Systems', 2, NULL, NULL);
INSERT INTO @TEMP VALUES('435 B1:', 'Tracking the Sequence', NULL, 5, 'Text, Text, Text…….');
INSERT INTO @TEMP VALUES('435 B1:', 'Tracking the Sequence', 1, NULL, NULL);
INSERT INTO @TEMP VALUES('435 B2:', 'Searching for Exceptions', NULL, 4, 'Text, Text, Text…….');
INSERT INTO @TEMP VALUES('435 B2:', 'Searching for Exceptions', 1, NULL, NULL);
INSERT INTO @TEMP VALUES('435 C1:', 'Family Level Outcomes', 3, NULL, NULL);
INSERT INTO @TEMP VALUES('435 C2:', 'Individual Level Outcomes', NULL, 3, 'Text, Text, Text…….');
INSERT INTO @TEMP VALUES('435 C2:', 'Individual Level Outcomes', 2, NULL, NULL);
INSERT INTO @TEMP VALUES('435 C3:', 'Child Well Being Outcomes', NULL, 2, 'Text, Text, Text…….');
INSERT INTO @TEMP VALUES('435 C3:', 'Child Well Being Outcomes', 2, NULL, NULL);
INSERT INTO @TEMP VALUES('435 C4:', 'Outcome Steps', 3, NULL, NULL);

;WITH CTE AS
(SELECT *, ROW_NUMBER() OVER(PARTITION BY ExamNum ORDER BY Skill) as RN FROM @TEMP)
SELECT T.ExamNum, T.Skill, COALESCE(CAST(COALESCE(T.Round1, T1.Round1) AS VARCHAR), '') + ',' +
COALESCE(CAST(COALESCE(T.Round2, T1.Round2) AS VARCHAR), '') AS CombinedRound1AndRound2,
(COALESCE(T.Rationale1,'') + COALESCE(T1.Rationale1, '')) AS CombinedRationale FROM
CTE T LEFT JOIN CTE T1 ON T.ExamNum = T1.ExamNum and T.RN = T1.RN-1
WHERE T.RN = 1;

[/CODE]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources