October 17, 2011 at 10:59 pm
Comments posted to this topic are about the item Display multiple rows values as single row value (Different Columns)
October 18, 2011 at 2:42 am
Hi,
Try with this Concat query
DECLARE @VariableName DataType
SELECT
@VariableName = COALESCE(@VariableName + ', ', '') + ColumnName
FROM
DataBaseName
WHERE
ColumnName IS NOT NULL
SELECT @VariableName
October 18, 2011 at 6:18 am
Is it just me, or did the script referred to by the author not get posted?
October 18, 2011 at 6:51 am
I'm not seeing any script, either.
October 18, 2011 at 7:19 am
Sorry, First time using SQL Scripts Manager... I copied the SQL Script instead the .rgtool code. I've updated the article. Meanwhile you can see the script here:
USE XD
GO
IF OBJECT_ID('SCHEDULE') IS NOT NULL DROP TABLE SCHEDULE
GO
CREATE TABLE SCHEDULE
(
ID INT,
COURSE_ID INT,
TEACHER_ID INT,
SCDAY NVARCHAR(10),
SCINI TIME,
SCEND TIME
)
GO
INSERT INTO SCHEDULE
VALUES
(1, 1, 1, 'MONDAY', '12:40', '14:20'),
(2, 1, 1, 'TUESDAY', '13:15', '16:10'),
(3, 2, 1, 'WENDSDAY', '15:30', '17:00'),
(4, 3, 2, 'MONDAY', '15:30', '17:20'),
(5, 3, 2, 'FRIDAY', '11:10', '13:20')
GO
;WITH InnerTable AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY COURSE_ID, TEACHER_ID ORDER BY COURSE_ID) AS Row,
TEACHER_ID, COURSE_ID, SCDAY, SCINI, SCEND
FROM Schedule
), OutterTable (CourseId, TeacherId, CourseDay1, StartTime1, EndTime1, CourseDay2, StartTime2, EndTime2) AS
(
SELECT t1.TEACHER_ID, t1.COURSE_ID, t1.SCDAY, t1. SCINI, t1.SCEND, t2.SCDAY, t2. SCINI, t2.SCEND
FROM InnerTable t1
LEFT JOIN InnerTable t2 ON t2.Row = 2 AND t1.TEACHER_ID = t2.TEACHER_ID AND t1.COURSE_ID = t2.COURSE_ID
WHERE t1.Row = 1
)
SELECT * FROM OutterTable;
October 18, 2011 at 7:43 am
What's the performance like on a large data set?
October 18, 2011 at 7:58 am
Actually, I'm using it with a table with 247.954 Rows, and takes about 2 seconds.
October 18, 2011 at 6:37 pm
Your Subject Line is misleading, your query does not return multiple row values as a single row, it only does two rows as a single row and there are far simpler ways of achieving this result. If you add third record for Course_ID = 3 and Teacher_Id = 2, you still only get two sets of columns and not 3.
Secondly, your script has an error. In the definition of OutterTable the Teacher_ID and COurse_ID are the wrong way around.
October 19, 2011 at 6:58 am
your query does not return multiple row values as a single row, it only does two rows as a single row and there are far simpler ways of achieving this result.
Would you post a better approach? The requirement isn't for n rows it's just for 2. Read:
Recently in my work, My boss assigned me the task of creating a schedule of teachers, the schedule can have up to 2 hours of which are in different rows, but should be shown just on one, so I had to make this script.
Secondly, your script has an error. In the definition of OutterTable the Teacher_ID and COurse_ID are the wrong way around.
My Mistake. 🙁
October 20, 2011 at 3:07 am
;WITH InnerTable AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY COURSE_ID, TEACHER_ID ORDER BY COURSE_ID) AS Row,
TEACHER_ID, COURSE_ID, SCDAY, SCINI, SCEND
FROM Schedule
)
select Teacher_ID,
Course_ID,
max(case row when 1 then SCDAY end) as SCDAY1,
max(case row when 1 then SCINI end) as SCINI1,
max(case row when 1 then SCEND end) as SCEND1,
max(case row when 2 then SCDAY end) as SCDAY2,
max(case row when 2 then SCINI end) as SCINI2,
max(case row when 2 then SCEND end) as SCEND2,
max(case row when 3 then SCDAY end) as SCDAY3,
max(case row when 3 then SCINI end) as SCINI3,
max(case row when 3 then SCEND end) as SCEND3
from InnerTable
group by Teacher_ID,
Course_ID
This does the same thing for 3 rows, and it can be extended to (almost) any number of rows by adding additional columns. This technique is called a cross-tab and was described extensively by Jeff Moden. See my footer text for a link to one of his articles.
October 20, 2011 at 7:19 am
Thanks, It's a better approach indeed. 😀
May 12, 2016 at 6:56 am
Thanks for the script.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply