Collation and tilde (~)

I have a table with a char(7) column with values like this:
0000100
0000200
ABC
DEF
~000300
~000400
When I use the default SQL Server collation, the values starting with the ~
sort above the "ABC" and "DEF" values (unlike several other database servers
I have used). I want those values to come last when ordering by that
column.
So far, the only collation I have come up with (I've tried several) that
does that is Latin1_General_BIN. But that makes the column case sensitive.
Is there another collation that would sort the tilde last, but provide case
insensitive comparisions on the column?I don't know of a collation which does this, but if sorting the resultset if
your requirement, assuming you have no values with 'ZZZZZZZ', you can just
do:
ORDER BY CASE WHEN LEFT(col, 1) = '~' THEN REPLICATE('Z', 7) END
Anith|||Unfortunately, the program has to work across different database servers, so
we do not want to use SQL Server-specific queries if we really do not have
to.
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:OM1b8WrCEHA.2620@.TK2MSFTNGP12.phx.gbl...
> I don't know of a collation which does this, but if sorting the resultset
if
> your requirement, assuming you have no values with 'ZZZZZZZ', you can just
> do:
> ORDER BY CASE WHEN LEFT(col, 1) = '~' THEN REPLICATE('Z', 7) END
> --
> Anith
>|||Hello JJ,
I guess the only way to find the right collation to meet your needs
is to try it out different collations (as you might have already done
it). If you can't, then as Anith
has pointed out you will have to modify the queries to meet the
sorting needs.
Thanks for using MSDN Newsgroup.
Vikrant Dalwale
Microsoft SQL Server Support Professional
Microsoft highly recommends to all of our customers that they visit
the http://www.microsoft.com/protect site and perform the three
straightforward steps listed to improve your computers security.
This posting is provided "AS IS" with no warranties, and confers no
rights.
--
>From: "JJ" <jjjj@.nospam.com>
>References: <O6#ZVHqCEHA.2656@.TK2MSFTNGP12.phx.gbl>
<OM1b8WrCEHA.2620@.TK2MSFTNGP12.phx.gbl>
>Subject: Re: Collation and tilde (~)
>Date: Mon, 15 Mar 2004 14:21:21 -0500
>Lines: 19
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
>Message-ID: <#$do0KsCEHA.308@.TK2MSFTNGP11.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.server
>NNTP-Posting-Host: 146.145.51.166
>Path:
cpmsftngxa06.phx.gbl!TK2MSFTNGXS01.phx.gbl!TK2MSFTNGXA05.phx.gbl!TK2MS
FTNGP08.phx.gbl!TK2MSFTNGP11.phx.gbl
>Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:333939
>X-Tomcat-NG: microsoft.public.sqlserver.server
>Unfortunately, the program has to work across different database
servers, so
>we do not want to use SQL Server-specific queries if we really do
not have
>to.
>"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
>news:OM1b8WrCEHA.2620@.TK2MSFTNGP12.phx.gbl...
resultset
>if
can just
>
>

Collation

Hi
I have a table with a column as NVARCHAR(500).
The column has some values like this:
ROW1=> Ã?kergatanÃ? 4Ã?2
ROW2=> TORRE CUSCATLAN 6° NIVEL = > °
ROW3=> EX. EDIF. ANTEL CENTRO DE GOBIERNO 1° PL
ROW4=> MARJORIE DE QUIÃ?ONEZ
I want to avoid the UNICODE character set as seen above. Can anyone please
advice what Collation should I set for that column?
Thanks in advance,
ArunWhy do you want to avoid the UNICODE? What do you want to convert all of
those characters to?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Arun Shankar" <ArunShankar@.discussions.microsoft.com> wrote in message
news:185DBA54-A6AA-4786-BACA-2E48B97C36A2@.microsoft.com...
> Hi
> I have a table with a column as NVARCHAR(500).
> The column has some values like this:
> ROW1=> ÅkergatanÅ 4Å2
> ROW2=> TORRE CUSCATLAN 6° NIVEL = > °
> ROW3=> EX. EDIF. ANTEL CENTRO DE GOBIERNO 1° PL
> ROW4=> MARJORIE DE QUIÑONEZ
> I want to avoid the UNICODE character set as seen above. Can anyone please
> advice what Collation should I set for that column?
> Thanks in advance,
> Arun|||Unfortunaly if you want to have different languages within a database table
then unicode is the way to go.
The reason is that a collation is normally specific for a country so if you
change it to one collation then you may find your character change.
Can you please tell us why you no longer want a unicode field ?
In the meanwhile have a look at this
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da_6ttf.asp
Peter
"Only two things are infinite, the universe and human stupidity, and I'm not
sure about the former."
Albert Einstein
"Arun Shankar" wrote:
> Hi
> I have a table with a column as NVARCHAR(500).
> The column has some values like this:
> ROW1=> Ã?kergatanÃ? 4Ã?2
> ROW2=> TORRE CUSCATLAN 6° NIVEL = > °
> ROW3=> EX. EDIF. ANTEL CENTRO DE GOBIERNO 1° PL
> ROW4=> MARJORIE DE QUIÃ?ONEZ
> I want to avoid the UNICODE character set as seen above. Can anyone please
> advice what Collation should I set for that column?
> Thanks in advance,
> Arun|||I will be using XML to create reports from those columns. XML supports UTF8
character set and its not able to create reports with this data. All I want
to do is avoid those special characters in that column and made some readable
set of data. I am assuming changing the Collation for that Column will
resolve the issue. Please correct me if I am wrong. Also let me know if there
is any other way to do this.
Thanks,
Arun
"Adam Machanic" wrote:
> Why do you want to avoid the UNICODE? What do you want to convert all of
> those characters to?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Arun Shankar" <ArunShankar@.discussions.microsoft.com> wrote in message
> news:185DBA54-A6AA-4786-BACA-2E48B97C36A2@.microsoft.com...
> > Hi
> > I have a table with a column as NVARCHAR(500).
> > The column has some values like this:
> > ROW1=> Ã?kergatanÃ? 4Ã?2
> > ROW2=> TORRE CUSCATLAN 6° NIVEL = > °
> > ROW3=> EX. EDIF. ANTEL CENTRO DE GOBIERNO 1° PL
> > ROW4=> MARJORIE DE QUIÃ?ONEZ
> >
> > I want to avoid the UNICODE character set as seen above. Can anyone please
> > advice what Collation should I set for that column?
> >
> > Thanks in advance,
> > Arun
>
>|||Probably changing the column to VARCHAR, rather than altering the collation.
But I'm not sure what this will do with the 2-byte characters.
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Arun Shankar" <ArunShankar@.discussions.microsoft.com> wrote in message
news:435E4B78-DC0B-48BD-9D67-B9E5B8013814@.microsoft.com...
> I will be using XML to create reports from those columns. XML supports
UTF8
> character set and its not able to create reports with this data. All I
want
> to do is avoid those special characters in that column and made some
readable
> set of data. I am assuming changing the Collation for that Column will
> resolve the issue. Please correct me if I am wrong. Also let me know if
there
> is any other way to do this.
> Thanks,
> Arun
> "Adam Machanic" wrote:
> > Why do you want to avoid the UNICODE? What do you want to convert all
of
> > those characters to?
> >
> >
> > --
> > Adam Machanic
> > SQL Server MVP
> > http://www.sqljunkies.com/weblog/amachanic
> > --
> >
> >
> > "Arun Shankar" <ArunShankar@.discussions.microsoft.com> wrote in message
> > news:185DBA54-A6AA-4786-BACA-2E48B97C36A2@.microsoft.com...
> > > Hi
> > > I have a table with a column as NVARCHAR(500).
> > > The column has some values like this:
> > > ROW1=> ÅkergatanÅ 4Å2
> > > ROW2=> TORRE CUSCATLAN 6° NIVEL = > °
> > > ROW3=> EX. EDIF. ANTEL CENTRO DE GOBIERNO 1° PL
> > > ROW4=> MARJORIE DE QUIÑONEZ
> > >
> > > I want to avoid the UNICODE character set as seen above. Can anyone
please
> > > advice what Collation should I set for that column?
> > >
> > > Thanks in advance,
> > > Arun
> >
> >
> >

Collation

Hi
I have a table with a column as NVARCHAR(500).
The column has some values like this:
ROW1=> ?kergatan? 4?2
ROW2=> TORRE CUSCATLAN 6° NIVEL = > °
ROW3=> EX. EDIF. ANTEL CENTRO DE GOBIERNO 1° PL
ROW4=> MARJORIE DE QUI?ONEZ
I want to avoid the UNICODE character set as seen above. Can anyone please
advice what Collation should I set for that column?
Thanks in advance,
Arun
Why do you want to avoid the UNICODE? What do you want to convert all of
those characters to?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Arun Shankar" <ArunShankar@.discussions.microsoft.com> wrote in message
news:185DBA54-A6AA-4786-BACA-2E48B97C36A2@.microsoft.com...
> Hi
> I have a table with a column as NVARCHAR(500).
> The column has some values like this:
> ROW1=> kergatan 42
> ROW2=> TORRE CUSCATLAN 6 NIVEL = >
> ROW3=> EX. EDIF. ANTEL CENTRO DE GOBIERNO 1 PL
> ROW4=> MARJORIE DE QUIONEZ
> I want to avoid the UNICODE character set as seen above. Can anyone please
> advice what Collation should I set for that column?
> Thanks in advance,
> Arun
|||Unfortunaly if you want to have different languages within a database table
then unicode is the way to go.
The reason is that a collation is normally specific for a country so if you
change it to one collation then you may find your character change.
Can you please tell us why you no longer want a unicode field ?
In the meanwhile have a look at this
http://msdn.microsoft.com/library/de...ar_da_6ttf.asp
Peter
"Only two things are infinite, the universe and human stupidity, and I'm not
sure about the former."
Albert Einstein
"Arun Shankar" wrote:

> Hi
> I have a table with a column as NVARCHAR(500).
> The column has some values like this:
> ROW1=> ?kergatan? 4?2
> ROW2=> TORRE CUSCATLAN 6° NIVEL = > °
> ROW3=> EX. EDIF. ANTEL CENTRO DE GOBIERNO 1° PL
> ROW4=> MARJORIE DE QUI?ONEZ
> I want to avoid the UNICODE character set as seen above. Can anyone please
> advice what Collation should I set for that column?
> Thanks in advance,
> Arun
|||I will be using XML to create reports from those columns. XML supports UTF8
character set and its not able to create reports with this data. All I want
to do is avoid those special characters in that column and made some readable
set of data. I am assuming changing the Collation for that Column will
resolve the issue. Please correct me if I am wrong. Also let me know if there
is any other way to do this.
Thanks,
Arun
"Adam Machanic" wrote:

> Why do you want to avoid the UNICODE? What do you want to convert all of
> those characters to?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Arun Shankar" <ArunShankar@.discussions.microsoft.com> wrote in message
> news:185DBA54-A6AA-4786-BACA-2E48B97C36A2@.microsoft.com...
>
>
|||Probably changing the column to VARCHAR, rather than altering the collation.
But I'm not sure what this will do with the 2-byte characters.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Arun Shankar" <ArunShankar@.discussions.microsoft.com> wrote in message
news:435E4B78-DC0B-48BD-9D67-B9E5B8013814@.microsoft.com...
> I will be using XML to create reports from those columns. XML supports
UTF8
> character set and its not able to create reports with this data. All I
want
> to do is avoid those special characters in that column and made some
readable
> set of data. I am assuming changing the Collation for that Column will
> resolve the issue. Please correct me if I am wrong. Also let me know if
there[vbcol=seagreen]
> is any other way to do this.
> Thanks,
> Arun
> "Adam Machanic" wrote:
of[vbcol=seagreen]
please[vbcol=seagreen]

Collation

Hi
I have a table with a column as NVARCHAR(500).
The column has some values like this:
ROW1=> ?kergatan? 4?2
ROW2=> TORRE CUSCATLAN 6° NIVEL = > °
ROW3=> EX. EDIF. ANTEL CENTRO DE GOBIERNO 1° PL
ROW4=> MARJORIE DE QUI?ONEZ
I want to avoid the UNICODE character set as seen above. Can anyone please
advice what Collation should I set for that column?
Thanks in advance,
ArunWhy do you want to avoid the UNICODE? What do you want to convert all of
those characters to?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Arun Shankar" <ArunShankar@.discussions.microsoft.com> wrote in message
news:185DBA54-A6AA-4786-BACA-2E48B97C36A2@.microsoft.com...
> Hi
> I have a table with a column as NVARCHAR(500).
> The column has some values like this:
> ROW1=> kergatan 42
> ROW2=> TORRE CUSCATLAN 6 NIVEL = >
> ROW3=> EX. EDIF. ANTEL CENTRO DE GOBIERNO 1 PL
> ROW4=> MARJORIE DE QUIONEZ
> I want to avoid the UNICODE character set as seen above. Can anyone please
> advice what Collation should I set for that column?
> Thanks in advance,
> Arun|||Unfortunaly if you want to have different languages within a database table
then unicode is the way to go.
The reason is that a collation is normally specific for a country so if you
change it to one collation then you may find your character change.
Can you please tell us why you no longer want a unicode field ?
In the meanwhile have a look at this
http://msdn.microsoft.com/library/d...br />
6ttf.asp
Peter
"Only two things are infinite, the universe and human stupidity, and I'm not
sure about the former."
Albert Einstein
"Arun Shankar" wrote:

> Hi
> I have a table with a column as NVARCHAR(500).
> The column has some values like this:
> ROW1=> ?kergatan? 4?2
> ROW2=> TORRE CUSCATLAN 6° NIVEL = > °
> ROW3=> EX. EDIF. ANTEL CENTRO DE GOBIERNO 1° PL
> ROW4=> MARJORIE DE QUI?ONEZ
> I want to avoid the UNICODE character set as seen above. Can anyone please
> advice what Collation should I set for that column?
> Thanks in advance,
> Arun|||I will be using XML to create reports from those columns. XML supports UTF8
character set and its not able to create reports with this data. All I want
to do is avoid those special characters in that column and made some readabl
e
set of data. I am assuming changing the Collation for that Column will
resolve the issue. Please correct me if I am wrong. Also let me know if ther
e
is any other way to do this.
Thanks,
Arun
"Adam Machanic" wrote:

> Why do you want to avoid the UNICODE? What do you want to convert all of
> those characters to?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Arun Shankar" <ArunShankar@.discussions.microsoft.com> wrote in message
> news:185DBA54-A6AA-4786-BACA-2E48B97C36A2@.microsoft.com...
>
>|||Probably changing the column to VARCHAR, rather than altering the collation.
But I'm not sure what this will do with the 2-byte characters.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Arun Shankar" <ArunShankar@.discussions.microsoft.com> wrote in message
news:435E4B78-DC0B-48BD-9D67-B9E5B8013814@.microsoft.com...
> I will be using XML to create reports from those columns. XML supports
UTF8
> character set and its not able to create reports with this data. All I
want
> to do is avoid those special characters in that column and made some
readable
> set of data. I am assuming changing the Collation for that Column will
> resolve the issue. Please correct me if I am wrong. Also let me know if
there[vbcol=seagreen]
> is any other way to do this.
> Thanks,
> Arun
> "Adam Machanic" wrote:
>
of[vbcol=seagreen]
please[vbcol=seagreen]sqlsql

Collapsing Three Rows Into One with T-SQL Challange?

Hello,

I am wondering if someone has any good ideas how I could concatenate values in column 7:30- 9:50 so that I would have one row and a value: MTTHFW. Or even if it is possible having this in logical days of a week order : MTWTHF.
Thanks a lot for any help!
Building Time Room # 7:30- 9:50
Engeneering 7:30:00 AM - 9:50:00 AM 201 MTTH
Engeneering 7:30:00 AM - 9:50:00 AM 201 F
Engeneering 7:30:00 AM - 9:50:00 AM 201 Wahhh the old Database Systems 101 "course/room/schedule" problem

You will get nowhere until you normalize your tables!!!

Course(ID,CourseName)
Room(ID, Room)
Day(No, Name, Abbrv)
TimeSlot(ID, DayNo, StartTime, Length) -Different Days May have different Time Allotments
ScheduledCourse(CourseID, RoomID, TimeSlotID)

=======================================
Sample Data
=======================================
Course
ID CourseName
1 Engineering
2 Biology
3 Calculus

Room
ID Room
1 101
2 102
3 201
4 202

Day
No Name Abbrv
1 Monday M
2 Tuesday T
3 Wednesday W
4 Thursday Th
5 Friday F

TimeSlot
ID DayNo StartTime Length NOTE: StateTime and Length are DateTimes!!
1 1 7:30 2:20
2 2 7:30 2:20
3 3 7:30 2:20
4 4 7:30 2:20
5 5 7:30 2:20
6 1 7:30 2:20
7 2 10:10 2:20
8 3 10:10 2:20
9 4 10:10 2:20
10 5 10:10 2:20

ScheduledCourse
CourseID RoomID TimeSlot
1 3 1
1 3 2
1 3 3
1 3 4
1 3 5
=======================================
Try it out . . .
=======================================
create table Course(ID int identity primary key,CourseName sysname)
create table Room(ID int identity primary key, Room sysname)
create table ClassDay(Number int , DayName sysname primary key, Abbrv sysname)
create table TimeSlot(ID int identity primary key, DayNo int, StartTime dateTime, Length dateTime)
create table ScheduledCourse(CourseID int, RoomID int, TimeSlotID int, primary key(CourseID,RoomID, TimeSlotID ))
insert into Course (CourseName) values('Engineering')
insert into Course (CourseName) values('Biology')
insert into Course (CourseName) values('Calculus')
insert into Room (Room) values('101')
insert into Room (Room) values('102')
insert into Room (Room) values('201')
insert into Room (Room) values('202')
insert into ClassDay values(1, 'Monday','M')
insert into ClassDay values(2, 'Tuesday','T')
insert into ClassDay values(3, 'Wednesday','W')
insert into ClassDay values(4, 'Thursday','Th')
insert into ClassDay values(5, 'Friday','F')
insert into TimeSlot (DayNo, StartTime, Length ) values(1, '7:30', '2:20')
insert into TimeSlot (DayNo, StartTime, Length ) values(2, '7:30', '2:20')
insert into TimeSlot (DayNo, StartTime, Length ) values(3, '7:30', '2:20')
insert into TimeSlot (DayNo, StartTime, Length ) values(4, '7:30', '2:20')
insert into TimeSlot (DayNo, StartTime, Length ) values(5, '7:30', '2:20')
insert into TimeSlot (DayNo, StartTime, Length ) values(1, '7:30', '2:20')
insert into TimeSlot (DayNo, StartTime, Length ) values(2, '10:10', '2:20')
insert into TimeSlot (DayNo, StartTime, Length ) values(3, '10:10', '2:20')
insert into TimeSlot (DayNo, StartTime, Length ) values(4, '10:10', '2:20')
insert into TimeSlot (DayNo, StartTime, Length ) values(5, '10:10', '2:20')
insert into ScheduledCourse values(1, 3, 1)
insert into ScheduledCourse values(1, 3, 2)
insert into ScheduledCourse values(1, 3, 3)
insert into ScheduledCourse values(1, 3, 4)
insert into ScheduledCourse values(1, 3, 5)
insert into ScheduledCourse values(2, 1, 1)
insert into ScheduledCourse values(2, 2, 2)
insert into ScheduledCourse values(3, 2, 3)
insert into ScheduledCourse values(3, 1, 4)
=======================================
Now try this query:
=======================================
SELECT c.CourseName, r.Room, t.StartTime, t.Length, d.Abbrv
FROM ScheduledCourse s INNER JOIN Course c ON
s.CourseID = c.ID INNER JOIN Room r ON
s.RoomID = r.ID INNER JOIN TimeSlot t ON
s.TimeSlotID = t.ID INNER JOIN ClassDay d ON
t.DayNo = d.Number
ORDER BY d.Number, t.StartTime, c.CourseName
=======================================
Yields this:
=======================================
Biology 101 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 M
Engineering 201 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 M
Biology 102 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 T
Engineering 201 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 T
Calculus 102 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 W
Engineering 201 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 W
Calculus 101 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 Th
Engineering 201 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 Th
Engineering 201 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 F
=======================================
almost there.... create this function
=======================================
create function DaysOfWeek(@.courseId int, @.roomId int) returns sysname
as begin
declare @.abbrv sysname
declare @.dayno int
declare @.temp sysname
declare curs cursor for
select distinct abbrv, number from classday where number in
(SELECT dayno
FROM ScheduledCourse INNER JOIN TimeSlot
ON ScheduledCourse.TimeSlotID = TimeSlot.ID
inner join ClassDay on TimeSlot.DayNo = ClassDay.Number
where ScheduledCourse.courseId = @.courseId and
ScheduledCourse.RoomId = @.RoomId)
order by number
open curs
fetch next from curs into @.abbrv, @.dayno
while @.@.fetch_status = 0
begin
fetch next from curs into @.temp, @.dayno
if @.@.fetch_status = 0
set @.abbrv = @.abbrv+@.temp
end
close curs
deallocate curs
return @.abbrv
end
=======================================
almost there. . .
change the previous query to include the function. . .
=======================================
SELECT distinct c.CourseName, r.Room, t.StartTime, t.StartTime+ t.Length, dbo.DaysOfWeek(s.courseId, s.roomId )
FROM ScheduledCourse s INNER JOIN Course c ON
s.CourseID = c.ID INNER JOIN Room r ON
s.RoomID = r.ID INNER JOIN TimeSlot t ON
s.TimeSlotID = t.ID INNER JOIN ClassDay d ON
t.DayNo = d.Number
=======================================
Yeilds this. . .
=======================================
Biology 101 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 M
Biology 102 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 T
Calculus 101 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 Th
Calculus 102 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 W
Engineering 201 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 MTWThF
=======================================
Almost there. . . can you feel it? hold on!!! we need to format the times!!!!
=======================================
SELECT distinct c.CourseName, r.Room, cast(DatePart(hh, t.StartTime) as sysname) +':'+ cast(DatePart(mi, t.StartTime) as sysname) + ' - ' +
cast(DatePart(hh, t.StartTime+ t.Length) as sysname) +':'+
cast(DatePart(mi,t.StartTime+ t.Length) as sysname) ,
dbo.DaysOfWeek(s.courseId, s.roomId )
FROM ScheduledCourse s INNER JOIN Course c ON
s.CourseID = c.ID INNER JOIN Room r ON
s.RoomID = r.ID INNER JOIN TimeSlot t ON
s.TimeSlotID = t.ID INNER JOIN ClassDay d ON
t.DayNo = d.Number
=======================================
Yields. . .
=======================================
Biology 101 7:30 - 9:50 M
Biology 102 7:30 - 9:50 T
Calculus 101 7:30 - 9:50 Th
Calculus 102 7:30 - 9:50 W
Engineering 201 7:30 - 9:50 MTWThF
=======================================

BOO-YAH!
|||Thank you very much Allen. It looks like a great design but unfortunately I don't dba right to change schema of a table I pulling my data from. The table schema looks this:

ID, CSM_ID, CSM_FAC_ID_NAME, CSM_START_TIME, CSM_END_TIME, CSM_BLDG, CSM_ROOM, CSM_CAPACITY, CSM_MON, CSM_TUE, CSM_WED, CSM_THU, CSM_FRI, CSM_SAT, CSM_SUN, CSM_COURSE_ID, CSM_COURSE_SEC_MEETING_ID, CSM_TECH, CSM_TERM, TimeRuleID, ViolatesRules, FacultyConflict, IsFromImport, ModifiedBy, IsDeleted, CoursePlannerID, IsArranged, IsTBA

CSM_MON, CSM_TUE, CSM_WED, CSM_THU, CSM_FRI, CSM_SAT, CSM_SUN include Y if there is a class. Based on this I have a query that figures out if there is a class or not in the paricular time slot:
--
SELECT IDENTITY(int, 1,1) AS Custom_ID, A.* INTO #EarlyMorning FROM (
SELECT DISTINCT
'Bannan for 05FQ' AS Building,
'7:30-9:50' AS Time,
CSM_ROOM AS [Room Number],
[Tech] = CASE ISNULL(CSM_TECH, '')
WHEN '' THEN ''
ELSE 'X' END,
CSM_CAPACITY AS [Capacity],
[7:30-9:50] = CONVERT( VARCHAR (25), CASE ISNULL(CSM_MON, '') WHEN '' THEN '' ELSE 'M' END
+ CASE ISNULL(CSM_TUE, '') WHEN '' THEN '' ELSE 'T' END
+ CASE ISNULL(CSM_WED, '') WHEN '' THEN '' ELSE 'W' END
+ CASE ISNULL(CSM_THU, '') WHEN '' THEN '' ELSE 'TH' END
+ CASE ISNULL(CSM_FRI, '') WHEN '' THEN '' ELSE 'F' END
+ CASE ISNULL(CSM_SAT, '') WHEN '' THEN '' ELSE 'SA' END
+ CASE ISNULL(CSM_SUN, '') WHEN '' THEN '' ELSE 'SU' END) FROM
COURSESCH_MEET
WHERE CSM_BLDG = 'ENG'
AND CSM_TERM = '05FQ'
AND CAST(CSM_START_TIME AS datetime) BETWEEN '7:30:00 AM' AND '9:50:00 AM'
AND CAST(CSM_END_TIME AS datetime)BETWEEN '7:30:00 AM' AND '9:50:00 AM'
) AS A
ORDER BY A.[Room Number]

Since I may have several different classes for the particular time slot, I can get multiple rows. Looking at the example, instead of three rows, I would like to have one row that would conatenate values from [7:30- 9:50] column into one string. So I would have one row from Room#202 and a string MTTHFW. Do you think I could accamplish here?

Building Time Room # 7:30- 9:50
Engeneering 7:30:00 AM - 9:50:00 AM 201 MTTH
Engeneering 7:30:00 AM - 9:50:00 AM 201 F
Engeneering 7:30:00 AM - 9:50:00 AM 201 W|||

donni100 wrote:

Do you think I could accamplish here?

I don't think so . . . at least not in T-SQL without having rights to create a stored procedure / function.

Someone needs to grab the dba and have him redesign the database as the table is not in third normal form.

If a database isn't in (at least) third normal form, it makes doing things via sql extremely difficult.|||

Give this a shot.

--First dump the result set into the first temp table (#temp1)

CREATE TABLE #temp3 --Final result table
(Building varchar(30),
Time varchar(40),
[Room #] int,
[Day of week] varchar(10))

SET NOCOUNT on --don't want the row affected count displaying.
declare @.DayOfWeek varchar(10), @.Room varchar(20), @.Time varchar(40)
--Now we create the cursor get the distinct times and room numbers and well flip --threw them.

DECLARE cur_room_time CURSOR FOR
SELECT DISTINCT Room, Time
FROM #temp1

OPEN cur_room_time
FETCH NEXT FROM cur_room_time
INTO @.Room, @.Time

WHILE @.@.FETCH_STATUS = 0
BEGIN
--Creating the temp table that we will eveluate the day of the week
Select *
into #temp2
from #temp1
where Room = @.Room
and Time = @.Time

set @.DayOfWeek = ''

--Checking for Monday (M)
If exists(Select * from #temp2 where charindex('M', DOW)> 0)
begin
Select @.DayOfWeek = @.DayOfWeek +'M'
end
--Checking for Tuesday (T) and insuring that it is not (TH)
If exists(Select * from #temp2 where charindex('T', DOW) > 0 and charindex('T', DOW)<> charindex('TH', DOW))
begin
Select @.DayOfWeek = @.DayOfWeek + 'T'
end
--Checking for Wednesday (W)
If exists(Select * from #temp2 where charindex('W', DOW)> 0)
begin
Select @.DayOfWeek = @.DayOfWeek + 'W'
end
--Checking for Thursday (TH)
If exists(Select * from #temp2 where charindex('TH', DOW)> 0)
begin
Select @.DayOfWeek = @.DayOfWeek + 'TH'
end
--Checking for Friday (F)
If exists(Select * from #temp2 where charindex('F', DOW)> 0)
begin
Select @.DayOfWeek = @.DayOfWeek + 'F'
end
--Checking for Saturday (SA)
If exists(Select * from #temp2 where charindex('SA', DOW)> 0)
begin
Select @.DayOfWeek = @.DayOfWeek + 'SA'
end
--Checking for Saturday (SA)
If exists(Select * from #temp2 where charindex('SU', DOW)> 0)
begin
Select @.DayOfWeek = @.DayOfWeek + 'SU'
end

insert into #temp3
Select Building, Time, Room, @.DayOfWeek from #temp2 group by Building, Time, Room

Drop table #temp2

FETCH NEXT FROM cur_room_time
INTO @.Room, @.Time

END

--cleraning up the cursor

CLOSE cur_room_time
DEALLOCATE cur_room_time

--selecting the final result set

Select *
from #temp3

DROP TABLE #temp3
Hope this works for you!

Ron N

|||It should work. Thanks a lot for help!|||

i belive this can help

well use this function to get retrive the string that cotaians the rows of the specific ID.

CREATEFUNCTION dbo.ConRow(@.JID int)

RETURNSVARCHAR(8000)

AS

BEGIN

DECLARE @.Output VARCHAR(8000)

SELECT @.Output =COALESCE(@.Output+', ','')+CONVERT(varchar(20), JP.a)

FROM [E_JobPending] JP

WHERE JP.JobID = @.JID

RETURN @.Output

END

select dbo.ConRow(jobid), vE_Job.*

from

vE_Job

DROPFUNCTION dbo.ConRow

|||

Untested, but should give a single row

SELECT IDENTITY(int, 1,1) AS Custom_ID, A.* INTO #EarlyMorning FROM (
SELECT
'Bannan for 05FQ' AS Building,
'7:30-9:50' AS Time,
CSM_ROOM AS [Room Number],
CASE ISNULL(CSM_TECH, '') WHEN '' THEN '' ELSE 'X' END AS [Tech],
CSM_CAPACITY AS [Capacity],
CONVERT( VARCHAR (25), CASE ISNULL(MAX(CSM_MON), '') WHEN '' THEN '' ELSE 'M' END
+ CASE ISNULL(MAX(CSM_TUE), '') WHEN '' THEN '' ELSE 'T' END
+ CASE ISNULL(MAX(CSM_WED), '') WHEN '' THEN '' ELSE 'W' END
+ CASE ISNULL(MAX(CSM_THU), '') WHEN '' THEN '' ELSE 'TH' END
+ CASE ISNULL(MAX(CSM_FRI), '') WHEN '' THEN '' ELSE 'F' END
+ CASE ISNULL(MAX(CSM_SAT), '') WHEN '' THEN '' ELSE 'SA' END
+ CASE ISNULL(MAX(CSM_SUN), '') WHEN '' THEN '' ELSE 'SU' END) AS [7:30-9:50]
FROM COURSESCH_MEET
WHERE CSM_BLDG = 'ENG'
AND CSM_TERM = '05FQ'
AND CAST(CSM_START_TIME AS datetime) BETWEEN '7:30:00 AM' AND '9:50:00 AM'
AND CAST(CSM_END_TIME AS datetime)BETWEEN '7:30:00 AM' AND '9:50:00 AM'
GROUP BY CSM_ROOM,CSM_TECH,CSM_CAPACITY
) AS A
ORDER BY A.[Room Number]

|||

Please post the version of SQL Server you are using so it is easier to suggest the correct solution. If you are using SQL Server 2005 you can use PIVOT operator and ROW_NUMBER in a query like below:

SELECT pt.Building, pt.Time, pt."Room #"

, pt.[1] + coalesce(pt.[2], '') + coalesce(pt.[3], '') + coalesce(pt.[4], '') as DaysOfWeek

FROM (

SELECT t.Building, t.Time, t."Room #", t."7:30- 9:50"

, ROW_NUMBER() OVER(PARTITION BY t.Building, t.Time, t."Room #" ORDER BY t."7:30- 9:50") as seq

FROM tbl as t

) AS t1

PIVOT (max(t1."7:30- 9:50") for t1.seq in ([1], [2], [3], [4] /*... as many maximum rows per grouping above*/)) as pt

You can do the same query above in older versions of SQL Server also. Use a temporary table to generate the sequence (possibly) or use correlated sub-query. And convert PIVOT to GROUP BY query with CASE expressions in SELECT list.

Collapsing Three Rows Into One with T-SQL Challange?

Hello,

I am wondering if someone has any good ideas how I could concatenate values in column 7:30- 9:50 so that I would have one row and a value: MTTHFW. Or even if it is possible having this in logical days of a week order : MTWTHF.
Thanks a lot for any help!
Building Time Room # 7:30- 9:50
Engeneering 7:30:00 AM - 9:50:00 AM 201 MTTH
Engeneering 7:30:00 AM - 9:50:00 AM 201 F
Engeneering 7:30:00 AM - 9:50:00 AM 201 Wahhh the old Database Systems 101 "course/room/schedule" problem

You will get nowhere until you normalize your tables!!!

Course(ID,CourseName)
Room(ID, Room)
Day(No, Name, Abbrv)
TimeSlot(ID, DayNo, StartTime, Length) -Different Days May have different Time Allotments
ScheduledCourse(CourseID, RoomID, TimeSlotID)

=======================================
Sample Data
=======================================
Course
ID CourseName
1 Engineering
2 Biology
3 Calculus

Room
ID Room
1 101
2 102
3 201
4 202

Day
No Name Abbrv
1 Monday M
2 Tuesday T
3 Wednesday W
4 Thursday Th
5 Friday F

TimeSlot
ID DayNo StartTime Length NOTE: StateTime and Length are DateTimes!!
1 1 7:30 2:20
2 2 7:30 2:20
3 3 7:30 2:20
4 4 7:30 2:20
5 5 7:30 2:20
6 1 7:30 2:20
7 2 10:10 2:20
8 3 10:10 2:20
9 4 10:10 2:20
10 5 10:10 2:20

ScheduledCourse
CourseID RoomID TimeSlot
1 3 1
1 3 2
1 3 3
1 3 4
1 3 5
=======================================
Try it out . . .
=======================================
create table Course(ID int identity primary key,CourseName sysname)
create table Room(ID int identity primary key, Room sysname)
create table ClassDay(Number int , DayName sysname primary key, Abbrv sysname)
create table TimeSlot(ID int identity primary key, DayNo int, StartTime dateTime, Length dateTime)
create table ScheduledCourse(CourseID int, RoomID int, TimeSlotID int, primary key(CourseID,RoomID, TimeSlotID ))
insert into Course (CourseName) values('Engineering')
insert into Course (CourseName) values('Biology')
insert into Course (CourseName) values('Calculus')
insert into Room (Room) values('101')
insert into Room (Room) values('102')
insert into Room (Room) values('201')
insert into Room (Room) values('202')
insert into ClassDay values(1, 'Monday','M')
insert into ClassDay values(2, 'Tuesday','T')
insert into ClassDay values(3, 'Wednesday','W')
insert into ClassDay values(4, 'Thursday','Th')
insert into ClassDay values(5, 'Friday','F')
insert into TimeSlot (DayNo, StartTime, Length ) values(1, '7:30', '2:20')
insert into TimeSlot (DayNo, StartTime, Length ) values(2, '7:30', '2:20')
insert into TimeSlot (DayNo, StartTime, Length ) values(3, '7:30', '2:20')
insert into TimeSlot (DayNo, StartTime, Length ) values(4, '7:30', '2:20')
insert into TimeSlot (DayNo, StartTime, Length ) values(5, '7:30', '2:20')
insert into TimeSlot (DayNo, StartTime, Length ) values(1, '7:30', '2:20')
insert into TimeSlot (DayNo, StartTime, Length ) values(2, '10:10', '2:20')
insert into TimeSlot (DayNo, StartTime, Length ) values(3, '10:10', '2:20')
insert into TimeSlot (DayNo, StartTime, Length ) values(4, '10:10', '2:20')
insert into TimeSlot (DayNo, StartTime, Length ) values(5, '10:10', '2:20')
insert into ScheduledCourse values(1, 3, 1)
insert into ScheduledCourse values(1, 3, 2)
insert into ScheduledCourse values(1, 3, 3)
insert into ScheduledCourse values(1, 3, 4)
insert into ScheduledCourse values(1, 3, 5)
insert into ScheduledCourse values(2, 1, 1)
insert into ScheduledCourse values(2, 2, 2)
insert into ScheduledCourse values(3, 2, 3)
insert into ScheduledCourse values(3, 1, 4)
=======================================
Now try this query:
=======================================
SELECT c.CourseName, r.Room, t.StartTime, t.Length, d.Abbrv
FROM ScheduledCourse s INNER JOIN Course c ON
s.CourseID = c.ID INNER JOIN Room r ON
s.RoomID = r.ID INNER JOIN TimeSlot t ON
s.TimeSlotID = t.ID INNER JOIN ClassDay d ON
t.DayNo = d.Number
ORDER BY d.Number, t.StartTime, c.CourseName
=======================================
Yields this:
=======================================
Biology 101 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 M
Engineering 201 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 M
Biology 102 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 T
Engineering 201 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 T
Calculus 102 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 W
Engineering 201 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 W
Calculus 101 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 Th
Engineering 201 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 Th
Engineering 201 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 F
=======================================
almost there.... create this function
=======================================
create function DaysOfWeek(@.courseId int, @.roomId int) returns sysname
as begin
declare @.abbrv sysname
declare @.dayno int
declare @.temp sysname
declare curs cursor for
select distinct abbrv, number from classday where number in
(SELECT dayno
FROM ScheduledCourse INNER JOIN TimeSlot
ON ScheduledCourse.TimeSlotID = TimeSlot.ID
inner join ClassDay on TimeSlot.DayNo = ClassDay.Number
where ScheduledCourse.courseId = @.courseId and
ScheduledCourse.RoomId = @.RoomId)
order by number
open curs
fetch next from curs into @.abbrv, @.dayno
while @.@.fetch_status = 0
begin
fetch next from curs into @.temp, @.dayno
if @.@.fetch_status = 0
set @.abbrv = @.abbrv+@.temp
end
close curs
deallocate curs
return @.abbrv
end
=======================================
almost there. . .
change the previous query to include the function. . .
=======================================
SELECT distinct c.CourseName, r.Room, t.StartTime, t.StartTime+ t.Length, dbo.DaysOfWeek(s.courseId, s.roomId )
FROM ScheduledCourse s INNER JOIN Course c ON
s.CourseID = c.ID INNER JOIN Room r ON
s.RoomID = r.ID INNER JOIN TimeSlot t ON
s.TimeSlotID = t.ID INNER JOIN ClassDay d ON
t.DayNo = d.Number
=======================================
Yeilds this. . .
=======================================
Biology 101 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 M
Biology 102 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 T
Calculus 101 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 Th
Calculus 102 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 W
Engineering 201 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 MTWThF
=======================================
Almost there. . . can you feel it? hold on!!! we need to format the times!!!!
=======================================
SELECT distinct c.CourseName, r.Room, cast(DatePart(hh, t.StartTime) as sysname) +':'+ cast(DatePart(mi, t.StartTime) as sysname) + ' - ' +
cast(DatePart(hh, t.StartTime+ t.Length) as sysname) +':'+
cast(DatePart(mi,t.StartTime+ t.Length) as sysname) ,
dbo.DaysOfWeek(s.courseId, s.roomId )
FROM ScheduledCourse s INNER JOIN Course c ON
s.CourseID = c.ID INNER JOIN Room r ON
s.RoomID = r.ID INNER JOIN TimeSlot t ON
s.TimeSlotID = t.ID INNER JOIN ClassDay d ON
t.DayNo = d.Number
=======================================
Yields. . .
=======================================
Biology 101 7:30 - 9:50 M
Biology 102 7:30 - 9:50 T
Calculus 101 7:30 - 9:50 Th
Calculus 102 7:30 - 9:50 W
Engineering 201 7:30 - 9:50 MTWThF
=======================================

BOO-YAH!
|||Thank you very much Allen. It looks like a great design but unfortunately I don't dba right to change schema of a table I pulling my data from. The table schema looks this:

ID, CSM_ID, CSM_FAC_ID_NAME, CSM_START_TIME, CSM_END_TIME, CSM_BLDG, CSM_ROOM, CSM_CAPACITY, CSM_MON, CSM_TUE, CSM_WED, CSM_THU, CSM_FRI, CSM_SAT, CSM_SUN, CSM_COURSE_ID, CSM_COURSE_SEC_MEETING_ID, CSM_TECH, CSM_TERM, TimeRuleID, ViolatesRules, FacultyConflict, IsFromImport, ModifiedBy, IsDeleted, CoursePlannerID, IsArranged, IsTBA

CSM_MON, CSM_TUE, CSM_WED, CSM_THU, CSM_FRI, CSM_SAT, CSM_SUN include Y if there is a class. Based on this I have a query that figures out if there is a class or not in the paricular time slot:
--
SELECT IDENTITY(int, 1,1) AS Custom_ID, A.* INTO #EarlyMorning FROM (
SELECT DISTINCT
'Bannan for 05FQ' AS Building,
'7:30-9:50' AS Time,
CSM_ROOM AS [Room Number],
[Tech] = CASE ISNULL(CSM_TECH, '')
WHEN '' THEN ''
ELSE 'X' END,
CSM_CAPACITY AS [Capacity],
[7:30-9:50] = CONVERT( VARCHAR (25), CASE ISNULL(CSM_MON, '') WHEN '' THEN '' ELSE 'M' END
+ CASE ISNULL(CSM_TUE, '') WHEN '' THEN '' ELSE 'T' END
+ CASE ISNULL(CSM_WED, '') WHEN '' THEN '' ELSE 'W' END
+ CASE ISNULL(CSM_THU, '') WHEN '' THEN '' ELSE 'TH' END
+ CASE ISNULL(CSM_FRI, '') WHEN '' THEN '' ELSE 'F' END
+ CASE ISNULL(CSM_SAT, '') WHEN '' THEN '' ELSE 'SA' END
+ CASE ISNULL(CSM_SUN, '') WHEN '' THEN '' ELSE 'SU' END) FROM
COURSESCH_MEET
WHERE CSM_BLDG = 'ENG'
AND CSM_TERM = '05FQ'
AND CAST(CSM_START_TIME AS datetime) BETWEEN '7:30:00 AM' AND '9:50:00 AM'
AND CAST(CSM_END_TIME AS datetime)BETWEEN '7:30:00 AM' AND '9:50:00 AM'
) AS A
ORDER BY A.[Room Number]

Since I may have several different classes for the particular time slot, I can get multiple rows. Looking at the example, instead of three rows, I would like to have one row that would conatenate values from [7:30- 9:50] column into one string. So I would have one row from Room#202 and a string MTTHFW. Do you think I could accamplish here?

Building Time Room # 7:30- 9:50
Engeneering 7:30:00 AM - 9:50:00 AM 201 MTTH
Engeneering 7:30:00 AM - 9:50:00 AM 201 F
Engeneering 7:30:00 AM - 9:50:00 AM 201 W|||

donni100 wrote:

Do you think I could accamplish here?

I don't think so . . . at least not in T-SQL without having rights to create a stored procedure / function.

Someone needs to grab the dba and have him redesign the database as the table is not in third normal form.

If a database isn't in (at least) third normal form, it makes doing things via sql extremely difficult.|||

Give this a shot.

--First dump the result set into the first temp table (#temp1)

CREATE TABLE #temp3 --Final result table
(Building varchar(30),
Time varchar(40),
[Room #] int,
[Day of week] varchar(10))

SET NOCOUNT on --don't want the row affected count displaying.
declare @.DayOfWeek varchar(10), @.Room varchar(20), @.Time varchar(40)
--Now we create the cursor get the distinct times and room numbers and well flip --threw them.

DECLARE cur_room_time CURSOR FOR
SELECT DISTINCT Room, Time
FROM #temp1

OPEN cur_room_time
FETCH NEXT FROM cur_room_time
INTO @.Room, @.Time

WHILE @.@.FETCH_STATUS = 0
BEGIN
--Creating the temp table that we will eveluate the day of the week
Select *
into #temp2
from #temp1
where Room = @.Room
and Time = @.Time

set @.DayOfWeek = ''

--Checking for Monday (M)
If exists(Select * from #temp2 where charindex('M', DOW)> 0)
begin
Select @.DayOfWeek = @.DayOfWeek +'M'
end
--Checking for Tuesday (T) and insuring that it is not (TH)
If exists(Select * from #temp2 where charindex('T', DOW) > 0 and charindex('T', DOW)<> charindex('TH', DOW))
begin
Select @.DayOfWeek = @.DayOfWeek + 'T'
end
--Checking for Wednesday (W)
If exists(Select * from #temp2 where charindex('W', DOW)> 0)
begin
Select @.DayOfWeek = @.DayOfWeek + 'W'
end
--Checking for Thursday (TH)
If exists(Select * from #temp2 where charindex('TH', DOW)> 0)
begin
Select @.DayOfWeek = @.DayOfWeek + 'TH'
end
--Checking for Friday (F)
If exists(Select * from #temp2 where charindex('F', DOW)> 0)
begin
Select @.DayOfWeek = @.DayOfWeek + 'F'
end
--Checking for Saturday (SA)
If exists(Select * from #temp2 where charindex('SA', DOW)> 0)
begin
Select @.DayOfWeek = @.DayOfWeek + 'SA'
end
--Checking for Saturday (SA)
If exists(Select * from #temp2 where charindex('SU', DOW)> 0)
begin
Select @.DayOfWeek = @.DayOfWeek + 'SU'
end

insert into #temp3
Select Building, Time, Room, @.DayOfWeek from #temp2 group by Building, Time, Room

Drop table #temp2

FETCH NEXT FROM cur_room_time
INTO @.Room, @.Time

END

--cleraning up the cursor

CLOSE cur_room_time
DEALLOCATE cur_room_time

--selecting the final result set

Select *
from #temp3

DROP TABLE #temp3
Hope this works for you!

Ron N

|||It should work. Thanks a lot for help!|||

i belive this can help

well use this function to get retrive the string that cotaians the rows of the specific ID.

CREATE FUNCTION dbo.ConRow(@.JID int)

RETURNS VARCHAR(8000)

AS

BEGIN

DECLARE @.Output VARCHAR(8000)

SELECT @.Output = COALESCE(@.Output+', ', '') + CONVERT(varchar(20), JP.a)

FROM [E_JobPending] JP

WHERE JP.JobID = @.JID

RETURN @.Output

END

select dbo.ConRow(jobid), vE_Job.*

from

vE_Job

DROP FUNCTION dbo.ConRow

|||

Untested, but should give a single row

SELECT IDENTITY(int, 1,1) AS Custom_ID, A.* INTO #EarlyMorning FROM (
SELECT
'Bannan for 05FQ' AS Building,
'7:30-9:50' AS Time,
CSM_ROOM AS [Room Number],
CASE ISNULL(CSM_TECH, '') WHEN '' THEN '' ELSE 'X' END AS [Tech],
CSM_CAPACITY AS [Capacity],
CONVERT( VARCHAR (25), CASE ISNULL(MAX(CSM_MON), '') WHEN '' THEN '' ELSE 'M' END
+ CASE ISNULL(MAX(CSM_TUE), '') WHEN '' THEN '' ELSE 'T' END
+ CASE ISNULL(MAX(CSM_WED), '') WHEN '' THEN '' ELSE 'W' END
+ CASE ISNULL(MAX(CSM_THU), '') WHEN '' THEN '' ELSE 'TH' END
+ CASE ISNULL(MAX(CSM_FRI), '') WHEN '' THEN '' ELSE 'F' END
+ CASE ISNULL(MAX(CSM_SAT), '') WHEN '' THEN '' ELSE 'SA' END
+ CASE ISNULL(MAX(CSM_SUN), '') WHEN '' THEN '' ELSE 'SU' END) AS [7:30-9:50]
FROM COURSESCH_MEET
WHERE CSM_BLDG = 'ENG'
AND CSM_TERM = '05FQ'
AND CAST(CSM_START_TIME AS datetime) BETWEEN '7:30:00 AM' AND '9:50:00 AM'
AND CAST(CSM_END_TIME AS datetime)BETWEEN '7:30:00 AM' AND '9:50:00 AM'
GROUP BY CSM_ROOM,CSM_TECH,CSM_CAPACITY
) AS A
ORDER BY A.[Room Number]

|||

Please post the version of SQL Server you are using so it is easier to suggest the correct solution. If you are using SQL Server 2005 you can use PIVOT operator and ROW_NUMBER in a query like below:

SELECT pt.Building, pt.Time, pt."Room #"

, pt.[1] + coalesce(pt.[2], '') + coalesce(pt.[3], '') + coalesce(pt.[4], '') as DaysOfWeek

FROM (

SELECT t.Building, t.Time, t."Room #", t."7:30- 9:50"

, ROW_NUMBER() OVER(PARTITION BY t.Building, t.Time, t."Room #" ORDER BY t."7:30- 9:50") as seq

FROM tbl as t

) AS t1

PIVOT (max(t1."7:30- 9:50") for t1.seq in ([1], [2], [3], [4] /*... as many maximum rows per grouping above*/)) as pt

You can do the same query above in older versions of SQL Server also. Use a temporary table to generate the sequence (possibly) or use correlated sub-query. And convert PIVOT to GROUP BY query with CASE expressions in SELECT list.

Collapsing Three Rows Into One with T-SQL Challange?

Hello,

I am wondering if someone has any good ideas how I could concatenate values in column 7:30- 9:50 so that I would have one row and a value: MTTHFW. Or even if it is possible having this in logical days of a week order : MTWTHF.
Thanks a lot for any help!
Building Time Room # 7:30- 9:50
Engeneering 7:30:00 AM - 9:50:00 AM 201 MTTH
Engeneering 7:30:00 AM - 9:50:00 AM 201 F
Engeneering 7:30:00 AM - 9:50:00 AM 201 Wahhh the old Database Systems 101 "course/room/schedule" problem

You will get nowhere until you normalize your tables!!!

Course(ID,CourseName)
Room(ID, Room)
Day(No, Name, Abbrv)
TimeSlot(ID, DayNo, StartTime, Length) -Different Days May have different Time Allotments
ScheduledCourse(CourseID, RoomID, TimeSlotID)

=======================================
Sample Data
=======================================
Course
ID CourseName
1 Engineering
2 Biology
3 Calculus

Room
ID Room
1 101
2 102
3 201
4 202

Day
No Name Abbrv
1 Monday M
2 Tuesday T
3 Wednesday W
4 Thursday Th
5 Friday F

TimeSlot
ID DayNo StartTime Length NOTE: StateTime and Length are DateTimes!!
1 1 7:30 2:20
2 2 7:30 2:20
3 3 7:30 2:20
4 4 7:30 2:20
5 5 7:30 2:20
6 1 7:30 2:20
7 2 10:10 2:20
8 3 10:10 2:20
9 4 10:10 2:20
10 5 10:10 2:20

ScheduledCourse
CourseID RoomID TimeSlot
1 3 1
1 3 2
1 3 3
1 3 4
1 3 5
=======================================
Try it out . . .
=======================================
create table Course(ID int identity primary key,CourseName sysname)
create table Room(ID int identity primary key, Room sysname)
create table ClassDay(Number int , DayName sysname primary key, Abbrv sysname)
create table TimeSlot(ID int identity primary key, DayNo int, StartTime dateTime, Length dateTime)
create table ScheduledCourse(CourseID int, RoomID int, TimeSlotID int, primary key(CourseID,RoomID, TimeSlotID ))
insert into Course (CourseName) values('Engineering')
insert into Course (CourseName) values('Biology')
insert into Course (CourseName) values('Calculus')
insert into Room (Room) values('101')
insert into Room (Room) values('102')
insert into Room (Room) values('201')
insert into Room (Room) values('202')
insert into ClassDay values(1, 'Monday','M')
insert into ClassDay values(2, 'Tuesday','T')
insert into ClassDay values(3, 'Wednesday','W')
insert into ClassDay values(4, 'Thursday','Th')
insert into ClassDay values(5, 'Friday','F')
insert into TimeSlot (DayNo, StartTime, Length ) values(1, '7:30', '2:20')
insert into TimeSlot (DayNo, StartTime, Length ) values(2, '7:30', '2:20')
insert into TimeSlot (DayNo, StartTime, Length ) values(3, '7:30', '2:20')
insert into TimeSlot (DayNo, StartTime, Length ) values(4, '7:30', '2:20')
insert into TimeSlot (DayNo, StartTime, Length ) values(5, '7:30', '2:20')
insert into TimeSlot (DayNo, StartTime, Length ) values(1, '7:30', '2:20')
insert into TimeSlot (DayNo, StartTime, Length ) values(2, '10:10', '2:20')
insert into TimeSlot (DayNo, StartTime, Length ) values(3, '10:10', '2:20')
insert into TimeSlot (DayNo, StartTime, Length ) values(4, '10:10', '2:20')
insert into TimeSlot (DayNo, StartTime, Length ) values(5, '10:10', '2:20')
insert into ScheduledCourse values(1, 3, 1)
insert into ScheduledCourse values(1, 3, 2)
insert into ScheduledCourse values(1, 3, 3)
insert into ScheduledCourse values(1, 3, 4)
insert into ScheduledCourse values(1, 3, 5)
insert into ScheduledCourse values(2, 1, 1)
insert into ScheduledCourse values(2, 2, 2)
insert into ScheduledCourse values(3, 2, 3)
insert into ScheduledCourse values(3, 1, 4)
=======================================
Now try this query:
=======================================
SELECT c.CourseName, r.Room, t.StartTime, t.Length, d.Abbrv
FROM ScheduledCourse s INNER JOIN Course c ON
s.CourseID = c.ID INNER JOIN Room r ON
s.RoomID = r.ID INNER JOIN TimeSlot t ON
s.TimeSlotID = t.ID INNER JOIN ClassDay d ON
t.DayNo = d.Number
ORDER BY d.Number, t.StartTime, c.CourseName
=======================================
Yields this:
=======================================
Biology 101 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 M
Engineering 201 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 M
Biology 102 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 T
Engineering 201 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 T
Calculus 102 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 W
Engineering 201 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 W
Calculus 101 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 Th
Engineering 201 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 Th
Engineering 201 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 F
=======================================
almost there.... create this function
=======================================
create function DaysOfWeek(@.courseId int, @.roomId int) returns sysname
as begin
declare @.abbrv sysname
declare @.dayno int
declare @.temp sysname
declare curs cursor for
select distinct abbrv, number from classday where number in
(SELECT dayno
FROM ScheduledCourse INNER JOIN TimeSlot
ON ScheduledCourse.TimeSlotID = TimeSlot.ID
inner join ClassDay on TimeSlot.DayNo = ClassDay.Number
where ScheduledCourse.courseId = @.courseId and
ScheduledCourse.RoomId = @.RoomId)
order by number
open curs
fetch next from curs into @.abbrv, @.dayno
while @.@.fetch_status = 0
begin
fetch next from curs into @.temp, @.dayno
if @.@.fetch_status = 0
set @.abbrv = @.abbrv+@.temp
end
close curs
deallocate curs
return @.abbrv
end
=======================================
almost there. . .
change the previous query to include the function. . .
=======================================
SELECT distinct c.CourseName, r.Room, t.StartTime, t.StartTime+ t.Length, dbo.DaysOfWeek(s.courseId, s.roomId )
FROM ScheduledCourse s INNER JOIN Course c ON
s.CourseID = c.ID INNER JOIN Room r ON
s.RoomID = r.ID INNER JOIN TimeSlot t ON
s.TimeSlotID = t.ID INNER JOIN ClassDay d ON
t.DayNo = d.Number
=======================================
Yeilds this. . .
=======================================
Biology 101 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 M
Biology 102 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 T
Calculus 101 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 Th
Calculus 102 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 W
Engineering 201 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 MTWThF
=======================================
Almost there. . . can you feel it? hold on!!! we need to format the times!!!!
=======================================
SELECT distinct c.CourseName, r.Room, cast(DatePart(hh, t.StartTime) as sysname) +':'+ cast(DatePart(mi, t.StartTime) as sysname) + ' - ' +
cast(DatePart(hh, t.StartTime+ t.Length) as sysname) +':'+
cast(DatePart(mi,t.StartTime+ t.Length) as sysname) ,
dbo.DaysOfWeek(s.courseId, s.roomId )
FROM ScheduledCourse s INNER JOIN Course c ON
s.CourseID = c.ID INNER JOIN Room r ON
s.RoomID = r.ID INNER JOIN TimeSlot t ON
s.TimeSlotID = t.ID INNER JOIN ClassDay d ON
t.DayNo = d.Number
=======================================
Yields. . .
=======================================
Biology 101 7:30 - 9:50 M
Biology 102 7:30 - 9:50 T
Calculus 101 7:30 - 9:50 Th
Calculus 102 7:30 - 9:50 W
Engineering 201 7:30 - 9:50 MTWThF
=======================================

BOO-YAH!
|||Thank you very much Allen. It looks like a great design but unfortunately I don't dba right to change schema of a table I pulling my data from. The table schema looks this:

ID, CSM_ID, CSM_FAC_ID_NAME, CSM_START_TIME, CSM_END_TIME, CSM_BLDG, CSM_ROOM, CSM_CAPACITY, CSM_MON, CSM_TUE, CSM_WED, CSM_THU, CSM_FRI, CSM_SAT, CSM_SUN, CSM_COURSE_ID, CSM_COURSE_SEC_MEETING_ID, CSM_TECH, CSM_TERM, TimeRuleID, ViolatesRules, FacultyConflict, IsFromImport, ModifiedBy, IsDeleted, CoursePlannerID, IsArranged, IsTBA

CSM_MON, CSM_TUE, CSM_WED, CSM_THU, CSM_FRI, CSM_SAT, CSM_SUN include Y if there is a class. Based on this I have a query that figures out if there is a class or not in the paricular time slot:
--
SELECT IDENTITY(int, 1,1) AS Custom_ID, A.* INTO #EarlyMorning FROM (
SELECT DISTINCT
'Bannan for 05FQ' AS Building,
'7:30-9:50' AS Time,
CSM_ROOM AS [Room Number],
[Tech] = CASE ISNULL(CSM_TECH, '')
WHEN '' THEN ''
ELSE 'X' END,
CSM_CAPACITY AS [Capacity],
[7:30-9:50] = CONVERT( VARCHAR (25), CASE ISNULL(CSM_MON, '') WHEN '' THEN '' ELSE 'M' END
+ CASE ISNULL(CSM_TUE, '') WHEN '' THEN '' ELSE 'T' END
+ CASE ISNULL(CSM_WED, '') WHEN '' THEN '' ELSE 'W' END
+ CASE ISNULL(CSM_THU, '') WHEN '' THEN '' ELSE 'TH' END
+ CASE ISNULL(CSM_FRI, '') WHEN '' THEN '' ELSE 'F' END
+ CASE ISNULL(CSM_SAT, '') WHEN '' THEN '' ELSE 'SA' END
+ CASE ISNULL(CSM_SUN, '') WHEN '' THEN '' ELSE 'SU' END) FROM
COURSESCH_MEET
WHERE CSM_BLDG = 'ENG'
AND CSM_TERM = '05FQ'
AND CAST(CSM_START_TIME AS datetime) BETWEEN '7:30:00 AM' AND '9:50:00 AM'
AND CAST(CSM_END_TIME AS datetime)BETWEEN '7:30:00 AM' AND '9:50:00 AM'
) AS A
ORDER BY A.[Room Number]

Since I may have several different classes for the particular time slot, I can get multiple rows. Looking at the example, instead of three rows, I would like to have one row that would conatenate values from [7:30- 9:50] column into one string. So I would have one row from Room#202 and a string MTTHFW. Do you think I could accamplish here?

Building Time Room # 7:30- 9:50
Engeneering 7:30:00 AM - 9:50:00 AM 201 MTTH
Engeneering 7:30:00 AM - 9:50:00 AM 201 F
Engeneering 7:30:00 AM - 9:50:00 AM 201 W|||

donni100 wrote:

Do you think I could accamplish here?

I don't think so . . . at least not in T-SQL without having rights to create a stored procedure / function.

Someone needs to grab the dba and have him redesign the database as the table is not in third normal form.

If a database isn't in (at least) third normal form, it makes doing things via sql extremely difficult.|||

Give this a shot.

--First dump the result set into the first temp table (#temp1)

CREATE TABLE #temp3 --Final result table
(Building varchar(30),
Time varchar(40),
[Room #] int,
[Day of week] varchar(10))

SET NOCOUNT on --don't want the row affected count displaying.
declare @.DayOfWeek varchar(10), @.Room varchar(20), @.Time varchar(40)
--Now we create the cursor get the distinct times and room numbers and well flip --threw them.

DECLARE cur_room_time CURSOR FOR
SELECT DISTINCT Room, Time
FROM #temp1

OPEN cur_room_time
FETCH NEXT FROM cur_room_time
INTO @.Room, @.Time

WHILE @.@.FETCH_STATUS = 0
BEGIN
--Creating the temp table that we will eveluate the day of the week
Select *
into #temp2
from #temp1
where Room = @.Room
and Time = @.Time

set @.DayOfWeek = ''

--Checking for Monday (M)
If exists(Select * from #temp2 where charindex('M', DOW)> 0)
begin
Select @.DayOfWeek = @.DayOfWeek +'M'
end
--Checking for Tuesday (T) and insuring that it is not (TH)
If exists(Select * from #temp2 where charindex('T', DOW) > 0 and charindex('T', DOW)<> charindex('TH', DOW))
begin
Select @.DayOfWeek = @.DayOfWeek + 'T'
end
--Checking for Wednesday (W)
If exists(Select * from #temp2 where charindex('W', DOW)> 0)
begin
Select @.DayOfWeek = @.DayOfWeek + 'W'
end
--Checking for Thursday (TH)
If exists(Select * from #temp2 where charindex('TH', DOW)> 0)
begin
Select @.DayOfWeek = @.DayOfWeek + 'TH'
end
--Checking for Friday (F)
If exists(Select * from #temp2 where charindex('F', DOW)> 0)
begin
Select @.DayOfWeek = @.DayOfWeek + 'F'
end
--Checking for Saturday (SA)
If exists(Select * from #temp2 where charindex('SA', DOW)> 0)
begin
Select @.DayOfWeek = @.DayOfWeek + 'SA'
end
--Checking for Saturday (SA)
If exists(Select * from #temp2 where charindex('SU', DOW)> 0)
begin
Select @.DayOfWeek = @.DayOfWeek + 'SU'
end

insert into #temp3
Select Building, Time, Room, @.DayOfWeek from #temp2 group by Building, Time, Room

Drop table #temp2

FETCH NEXT FROM cur_room_time
INTO @.Room, @.Time

END

--cleraning up the cursor

CLOSE cur_room_time
DEALLOCATE cur_room_time

--selecting the final result set

Select *
from #temp3

DROP TABLE #temp3
Hope this works for you!

Ron N

|||It should work. Thanks a lot for help!|||

i belive this can help

well use this function to get retrive the string that cotaians the rows of the specific ID.

CREATE FUNCTION dbo.ConRow(@.JID int)

RETURNS VARCHAR(8000)

AS

BEGIN

DECLARE @.Output VARCHAR(8000)

SELECT @.Output = COALESCE(@.Output+', ', '') + CONVERT(varchar(20), JP.a)

FROM [E_JobPending] JP

WHERE JP.JobID = @.JID

RETURN @.Output

END

select dbo.ConRow(jobid), vE_Job.*

from

vE_Job

DROP FUNCTION dbo.ConRow

|||

Untested, but should give a single row

SELECT IDENTITY(int, 1,1) AS Custom_ID, A.* INTO #EarlyMorning FROM (
SELECT
'Bannan for 05FQ' AS Building,
'7:30-9:50' AS Time,
CSM_ROOM AS [Room Number],
CASE ISNULL(CSM_TECH, '') WHEN '' THEN '' ELSE 'X' END AS [Tech],
CSM_CAPACITY AS [Capacity],
CONVERT( VARCHAR (25), CASE ISNULL(MAX(CSM_MON), '') WHEN '' THEN '' ELSE 'M' END
+ CASE ISNULL(MAX(CSM_TUE), '') WHEN '' THEN '' ELSE 'T' END
+ CASE ISNULL(MAX(CSM_WED), '') WHEN '' THEN '' ELSE 'W' END
+ CASE ISNULL(MAX(CSM_THU), '') WHEN '' THEN '' ELSE 'TH' END
+ CASE ISNULL(MAX(CSM_FRI), '') WHEN '' THEN '' ELSE 'F' END
+ CASE ISNULL(MAX(CSM_SAT), '') WHEN '' THEN '' ELSE 'SA' END
+ CASE ISNULL(MAX(CSM_SUN), '') WHEN '' THEN '' ELSE 'SU' END) AS [7:30-9:50]
FROM COURSESCH_MEET
WHERE CSM_BLDG = 'ENG'
AND CSM_TERM = '05FQ'
AND CAST(CSM_START_TIME AS datetime) BETWEEN '7:30:00 AM' AND '9:50:00 AM'
AND CAST(CSM_END_TIME AS datetime)BETWEEN '7:30:00 AM' AND '9:50:00 AM'
GROUP BY CSM_ROOM,CSM_TECH,CSM_CAPACITY
) AS A
ORDER BY A.[Room Number]

|||

Please post the version of SQL Server you are using so it is easier to suggest the correct solution. If you are using SQL Server 2005 you can use PIVOT operator and ROW_NUMBER in a query like below:

SELECT pt.Building, pt.Time, pt."Room #"

, pt.[1] + coalesce(pt.[2], '') + coalesce(pt.[3], '') + coalesce(pt.[4], '') as DaysOfWeek

FROM (

SELECT t.Building, t.Time, t."Room #", t."7:30- 9:50"

, ROW_NUMBER() OVER(PARTITION BY t.Building, t.Time, t."Room #" ORDER BY t."7:30- 9:50") as seq

FROM tbl as t

) AS t1

PIVOT (max(t1."7:30- 9:50") for t1.seq in ([1], [2], [3], [4] /*... as many maximum rows per grouping above*/)) as pt

You can do the same query above in older versions of SQL Server also. Use a temporary table to generate the sequence (possibly) or use correlated sub-query. And convert PIVOT to GROUP BY query with CASE expressions in SELECT list.

Coalesce with Sum

I am having a problem with syntax. I am trying to sum a column where some of the values will be null and because I want to include the rows where the column may be null I am attempting to coalesce to zero.

Below is my sample:

SELECT *

FROM dbo.Student w

LEFT JOIN dbo.StudentDailyAbsence q ON q.StudentID = w.StudentID

Group BY q.StudentID

Having

(SUM(Coalesce(q.AbsenceValue),0) = 0.00)

COALESCE(SUM(q.AbsenceValue) = 0.00,0)

I have tried using the coalesce statement a couple of ways with no resolution, pls help!!

Change to this:

COALESCE( q.AbsenceValue, 0)

|||Ok, but how does that incorporate summing the column?|||

Try something like this: (in case you need the student name from your student table)

SELECT w.StudentID, w.StudentName, SUM(Coalesce(q.AbsenceValue,0) ) AS sumAbsenceValue

FROM dbo.Student w

LEFT JOIN dbo.StudentDailyAbsence q ON q.StudentID = w.StudentID

Group BY w.StudentID, w.StudentName

But you don't need to do the coalesce: SUM and AVG will skip the NULL value in the caculation.

The follwing should return the same result:

SELECT w.StudentID, w.StudentName, SUM(q.AbsenceValue) AS sumAbsenceValue

FROM dbo.Student w

LEFT JOIN dbo.StudentDailyAbsence q ON q.StudentID = w.StudentID

Group BY w.StudentID, w.StudentName

|||

Thanks for putting me on the right track. I actually got the result I needed by modifying your first example a little.

SELECT w.StudentID, SUM(Coalesce(q.AbsenceValue,0) ) AS sumAbsenceValue

FROM dbo.Student w

LEFT JOIN dbo.StudentDailyAbsence q ON q.StudentID = w.StudentID

Group BY w.StudentID

Having SUM(Coalesce(q.AbsenceValue,0) ) = 0.00

This gets me the desired result. I still needed to compare the result of the sum so that it equalled 0.00.

Thanks for setting me straight, I was about an inch from pulling hairs .

coalesce does not seem to work

Hi,

I have the following table with some sample values, I want to return the first non null value in that order. COALESCE does not seem to work for me, it does not return the 3rd record. I need to include this in my select statement. Any urgent help please.

Mobile Business Private

NULL 345 NULL
4646 65464 65765
NULL 564
654654 564 6546

I want the following as my results:

Number

345
4646
564
654654

Select COALESCE(Mobile,Business,Private) as Number from Table returns:

345
4646

654654

(this is a test to see if private returns & it did with is not null but then how do i include in my select statement to show any one of the 3 fields)

select mobile,business,private where private is not null returns:

65765
564
6546

thanks

As you mentioned, COALESCE returns the first Non NULL value. You result is not what you want but the COALESCE is correct. You have a blank cell in your table. It is not NULL. You can use a CASE statement to check either NULL or blank to get the result you want. Or you can make sure your missing value cells are NULL.

HTH.

Coalesce / Comma Delimitted List

I'm trying to return multiple column, one of which is a column + a comma delimitted list of values. Below is a simplified, non working, query:

(assume @.MyBit, @.MyVarChar, and @.MyValue are all declared)

SELECT distinct
IsNull(Column1, '') AS Column1,
IsNull(Column2,'') + CASE WHEN @.MyBit = 1 THEN ' My Test: ' + (select @.MyVarChar = COALESCE(Column3 + ', ', '') from TableSub where Value = @.MyValue) ELSE '' END AS Column2,
FROM TableMain

I get an ADO error: Incorrect syntax near '='

Thoughts?

Thanks(select @.MyVarChar = COALESCE(Column3 + ', ', '') from TableSub where Value = @.MyValue)

use "[ ]" brackets instead of "( )" around the above select statment.

(and remove the last comma before the FROM stmnt)|||I now have:

SELECT distinct
IsNull(Column1, '') AS Column1,
IsNull(Column2,'') + CASE WHEN @.MyBit = 1 THEN ' My Test: ' + [select @.MyVarChar = COALESCE(Column3 + ', ', '') from TableSub where Value = @.MyValue] ELSE '' END AS Column2
FROM TableMain

and I get:
ADO error: Invalid column name 'select @.MyVarChar = COALESCE(Column3 + ',', '') from TableSub where Value = @.MyValue'

Thanks|||my apologies - here is the correct query

SELECT distinct
IsNull(Column1, '') AS Column1,
IsNull(Column2,'') + CASE WHEN @.MyBit = 1 THEN ' My Test: ' + (select COALESCE(Column3 + ', ', '') from TableSub where Value = @.MyValue) ELSE '' END AS Column2
FROM TableMain

put the "( )" back on and remove the @.MyVarChar = (that is where the err is)
you just need select coalesce...

Clusters with "missing" values

Hello all and a happy new year!

I used Microsoft clustering for grouping my data. Even though i already cleaned the data and have no null values i get one cluster with missing values in every attribute. (i set CLUSTER_COUNT=3 and i'm using Scalable k-means algorithm)

Does "missing" mean that the algorithm cannot group that particular tuple in another group so it consider it as missing?

Thank you in advance.

"missing" is an implicit state of any attribute, and is considered whether or not there is missing data or not. For example, a "gender" attribute could have states "Male", "Female" and "missing". An "Age" attribute would have as possible states the age value, or "missing." When clusters are initialized the centroid of the cluster is initialized as a probability of that state being in that cluster, e.g. 45% Male, 50% female, and 5% missing. In general, the initial probabilities are determined by perturbing the distributions of the values shown in the data.

That being said, you say that you are getting a cluster with missing values in every attribute - what is the support of this cluster relative to all the others? It may be possible that you are really only finding two clusters and getting a cluster that is negligable. Could you post the results of SELECT FLATTENED * FROM <model name>.CONTENT?

|||

Thank you for you answer it was very helpful!

The support of these cluster is very small comparing to the other clusters (~0,01 -0,02%)
Can i consider those data as outliers and discard them?

|||Likely - you could just name it as such

Clusters with "missing" values

Hello all and a happy new year!

I used Microsoft clustering for grouping my data. Even though i already cleaned the data and have no null values i get one cluster with missing values in every attribute. (i set CLUSTER_COUNT=3 and i'm using Scalable k-means algorithm)

Does "missing" mean that the algorithm cannot group that particular tuple in another group so it consider it as missing?

Thank you in advance.

"missing" is an implicit state of any attribute, and is considered whether or not there is missing data or not. For example, a "gender" attribute could have states "Male", "Female" and "missing". An "Age" attribute would have as possible states the age value, or "missing." When clusters are initialized the centroid of the cluster is initialized as a probability of that state being in that cluster, e.g. 45% Male, 50% female, and 5% missing. In general, the initial probabilities are determined by perturbing the distributions of the values shown in the data.

That being said, you say that you are getting a cluster with missing values in every attribute - what is the support of this cluster relative to all the others? It may be possible that you are really only finding two clusters and getting a cluster that is negligable. Could you post the results of SELECT FLATTENED * FROM <model name>.CONTENT?

|||

Thank you for you answer it was very helpful!

The support of these cluster is very small comparing to the other clusters (~0,01 -0,02%)
Can i consider those data as outliers and discard them?

|||Likely - you could just name it as such