How to Concatinate results to display results in a list from Left - Right instead of Top -

Hi,
I am creating a report using SQL Server Reporting Services 2000. My
report displays values in datatable. Say, the table looks like this
EmpName Emp# Address Phone#
..... ... ...... ...
I want all phone # for an employee to be displayed in a single row.
This can be acheived by displaying phone numbers in a group footer
(group by emp#). But I want all phone# to be displayed horizontally
(Left - Right) not vertically (Top - Bottom).
Instead of displaying results like this
EmpName Emp# Address
..... ... ......
Phones
###
###
###
###
I want to display results like below -
EmpName Emp# Address Phone#
..... ... ...... ...
Phones ### , ### , ####, ###, ###, ###
..... ... ...... ...
Phones ### , ###
..... ... ...... ...
Phones ### , ###, ### , ###
How can I concatinate the phone# ? I tried using a sub report in the
footer to retrive all phone# for an employee but how to concatinate
them ?
I tried to query like this
SELECT @.STRCON = EmpPhone + ', ' FROM EMPLOYEE WHERE EMPID = ###
But I'm not sure about the length of the return value (may be more than
8000 characters).
So is there any way in concatinating values in Report ? Or is there any
way in acheiving this in a Listbox or Table ?
Regards,
ChiroI think Chris Hays has something that will work for you.
http://blogs.msdn.com/chrishays/archive/2004/07/23/HorizontalTables.aspx
Steve MunLeeuw
"Chiro" <chirangv@.hotmail.com> wrote in message
news:1161268089.076044.227180@.e3g2000cwe.googlegroups.com...
> Hi,
> I am creating a report using SQL Server Reporting Services 2000. My
> report displays values in datatable. Say, the table looks like this
> EmpName Emp# Address Phone#
> ..... ... ...... ...
> I want all phone # for an employee to be displayed in a single row.
> This can be acheived by displaying phone numbers in a group footer
> (group by emp#). But I want all phone# to be displayed horizontally
> (Left - Right) not vertically (Top - Bottom).
> Instead of displaying results like this
> EmpName Emp# Address
> ..... ... ......
> Phones
> ###
> ###
> ###
> ###
> I want to display results like below -
> EmpName Emp# Address Phone#
> ..... ... ...... ...
> Phones ### , ### , ####, ###, ###, ###
> ..... ... ...... ...
> Phones ### , ###
> ..... ... ...... ...
> Phones ### , ###, ### , ###
>
> How can I concatinate the phone# ? I tried using a sub report in the
> footer to retrive all phone# for an employee but how to concatinate
> them ?
> I tried to query like this
> SELECT @.STRCON = EmpPhone + ', ' FROM EMPLOYEE WHERE EMPID = ###
> But I'm not sure about the length of the return value (may be more than
> 8000 characters).
> So is there any way in concatinating values in Report ? Or is there any
> way in acheiving this in a Listbox or Table ?
> Regards,
> Chiro
>|||Hi,
Thank You.
I was able to acheive this as follows -
1. Add a Matrix control. Add Phone# in column grouping of matrix.
2. Add a ListBox control. Include Matrix inside the Listbox.
3. Edit details of Listbox to add a group expression
=RowNumber(Nothing) / 15. (15 is number of columns to be displayed)
4. Add a Matrix column group expression as your Listbox group
expression.
=RowNumber("list1_Details_Group"). Now your matrix should contain 2
group expressions. (1 for Phone # and other for controling no. of
columns).
Regards,
Chiro
Steve MunLeeuw wrote:
> I think Chris Hays has something that will work for you.
> http://blogs.msdn.com/chrishays/archive/2004/07/23/HorizontalTables.aspx
> Steve MunLeeuw
> "Chiro" <chirangv@.hotmail.com> wrote in message
> news:1161268089.076044.227180@.e3g2000cwe.googlegroups.com...
> > Hi,
> > I am creating a report using SQL Server Reporting Services 2000. My
> > report displays values in datatable. Say, the table looks like this
> >
> > EmpName Emp# Address Phone#
> > ..... ... ...... ...
> > I want all phone # for an employee to be displayed in a single row.
> > This can be acheived by displaying phone numbers in a group footer
> > (group by emp#). But I want all phone# to be displayed horizontally
> > (Left - Right) not vertically (Top - Bottom).
> > Instead of displaying results like this
> > EmpName Emp# Address
> > ..... ... ......
> > Phones
> > ###
> > ###
> > ###
> > ###
> > I want to display results like below -
> > EmpName Emp# Address Phone#
> > ..... ... ...... ...
> > Phones ### , ### , ####, ###, ###, ###
> > ..... ... ...... ...
> > Phones ### , ###
> > ..... ... ...... ...
> > Phones ### , ###, ### , ###
> >
> >
> > How can I concatinate the phone# ? I tried using a sub report in the
> > footer to retrive all phone# for an employee but how to concatinate
> > them ?
> > I tried to query like this
> > SELECT @.STRCON = EmpPhone + ', ' FROM EMPLOYEE WHERE EMPID = ###
> > But I'm not sure about the length of the return value (may be more than
> >
> > 8000 characters).
> > So is there any way in concatinating values in Report ? Or is there any
> >
> > way in acheiving this in a Listbox or Table ?
> > Regards,
> > Chiro
> >

How to check whether SQL login exists?

Is there any way to check whether the login exists before creating login ?

create login should be executed after check.

thanks,

Sreenath

Try something like this:

DECLARE @.SqlStatement nvarchar(4000)
Declare @.loginName varchar (100)

Select @.loginName = 'test\thermanson'

If not Exists (select loginname from master.dbo.syslogins where name = @.loginName and dbname = 'PUBS')
Begin

Set @.SqlStatement = 'CREATE LOGIN [' + @.loginName + '] FROM WINDOWS WITH DEFAULT_DATABASE=[PUBS], DEFAULT_LANGUAGE=[us_english]'

EXEC sp_executesql @.SqlStatement
End