how to comment out a block of codes in SQL Query Analyzer?

Hello,
I know '--' can be used to comment out a single line. I tried /* and */ but
got the follow errors.
/*use master
go
exec sp_addlinkedsrvlogin
@.rmtsrvname = 'access',
@.useself = false,
@.locallogin = 'administrator',
@.rmtuser = 'admin',
@.rmtpassword = NULL
*/
Server: Msg 113, Level 15, State 1, Line 1
Missing end comment mark '*/'.
Server: Msg 170, Level 15, State 1, Line 8
Line 8: Incorrect syntax near '*'.
Thanks,
Bingbing wrote on Tue, 29 Nov 2005 09:02:15 -0800:
> Hello,
> I know '--' can be used to comment out a single line. I tried /* and */
> but got the follow errors.
> /*use master
> go
> exec sp_addlinkedsrvlogin
> @.rmtsrvname = 'access',
> @.useself = false,
> @.locallogin = 'administrator',
> @.rmtuser = 'admin',
> @.rmtpassword = NULL
> */
> Server: Msg 113, Level 15, State 1, Line 1
> Missing end comment mark '*/'.
> Server: Msg 170, Level 15, State 1, Line 8
> Line 8: Incorrect syntax near '*'.
> Thanks,
> Bing
The GO is being interpreted as a batch delimiter. Just add some additional
markers around the GO.
/*
use master
*/
go
/*
exec sp_addlinkedsrvlogin
@.rmtsrvname = 'access',
@.useself = false,
@.locallogin = 'administrator',
@.rmtuser = 'admin',
@.rmtpassword = NULL
*/
Dan|||"Daniel Crichton" wrote:
> bing wrote on Tue, 29 Nov 2005 09:02:15 -0800:
> > Hello,
> >
> > I know '--' can be used to comment out a single line. I tried /* and */
> > but got the follow errors.
> >
> > /*use master
> > go
> >
> > exec sp_addlinkedsrvlogin
> > @.rmtsrvname = 'access',
> > @.useself = false,
> > @.locallogin = 'administrator',
> > @.rmtuser = 'admin',
> > @.rmtpassword = NULL
> > */
> >
> > Server: Msg 113, Level 15, State 1, Line 1
> > Missing end comment mark '*/'.
> > Server: Msg 170, Level 15, State 1, Line 8
> > Line 8: Incorrect syntax near '*'.
> >
> > Thanks,
> >
> > Bing
>
> The GO is being interpreted as a batch delimiter. Just add some additional
> markers around the GO.
> /*
> use master
> */
> go
> /*
> exec sp_addlinkedsrvlogin
> @.rmtsrvname = 'access',
> @.useself = false,
> @.locallogin = 'administrator',
> @.rmtuser = 'admin',
> @.rmtpassword = NULL
> */
>
Ah, ok, thanks much for the heads-up.
Bing

how to combine two records into one while displaying

here is the table structure
name |account | balance
jessica 234 45.00
mary 234 45.00
Robert 111 50.00
I need show all the persons names having similar account Id in one record
my expected output when i query this should be
jessica 234 45.00
&
mary
Robert 111 50.00
thanks for your reply in advance.Your table design appears to be denormalised. I would have expected Balance
to be in a separate table. Surely the same account number can't have two
different Balances? Fixing this should make your reporting task easier.
If you have a maximum of two names per account then just use MIN and MAX:
SELECT MIN(name),
CASE WHEN MAX(name)>MIN(name)
THEN MAX(name) END,
account, balance
FROM AccountNames
GROUP BY account, balance
If you have an undefined number of names per account then probably the best
approach is to compile your report-style output in your client application
or report writer.
--
David Portas
--
Please reply only to the newsgroup
--|||David,
Thanks for the reply. Altough your right It did not really answer my question. Please forgot about the table structure, main thing when i get an import from other system my table which i showed gets accurate(same) balances for the accounts having same id.
My question is .. when you query the table i need to get the records with the same account id as one record with all the names merged in the name column.
What I was doing is , I was using a cursor to loop through the table and get the names merged . My problem is once I complete the looping process based on accountid I have to insert the record into temporary table . I was wondering is there a way without using this cursors and temp tables to just get the data easily.
thanks|||Assuming that the combination of (account, name) is unique:
SELECT MIN(CASE seq WHEN 1 THEN name END)+
COALESCE(', '+MIN(CASE seq WHEN 2 THEN name END),'')+
COALESCE(', '+MIN(CASE seq WHEN 3 THEN name END),'')+
COALESCE(', '+MIN(CASE seq WHEN 4 THEN name END),'')
... etc...
account, MIN(balance)
FROM
(SELECT A1.name, A1.account, MIN(A1.balance), COUNT(*)
FROM AccountNames AS A1
JOIN AccountNames AS A2
ON A1.account = A2.account
AND A1.name >= A2.name
GROUP BY A1.name, A1.account)
AS A(name, account, balance, seq)
GROUP BY account
--
David Portas
--
Please reply only to the newsgroup
--|||david I need your help once agai
Main thing is I need the same output as before but I was not able to join my real world tables properly to get the data
I have three table
1. Person - which has (IdColumn(PK), PersonId, Firstname,Lastname,Middlename
2. UserPerson - which has (PersonId, UserId
3. PersonAccount - which has (IdColumn(PK), PersonId, JointAccount,Balance
Also all the personId's are GUID's
I need the userperson table because my where clause has a condition to get only persons beloging to a user. (like where userid = 100
My output as discussed previously should be
person name (Firstname+middlename+lastname), JointaccountId, balanc
The one which you given works fine with the previous table structure we have discussed
I very much appreciate your help
thank
-- David Portas wrote: --
Assuming that the combination of (account, name) is unique
SELECT MIN(CASE seq WHEN 1 THEN name END)
COALESCE(', '+MIN(CASE seq WHEN 2 THEN name END),'')
COALESCE(', '+MIN(CASE seq WHEN 3 THEN name END),'')
COALESCE(', '+MIN(CASE seq WHEN 4 THEN name END),''
... etc..
account, MIN(balance
FRO
(SELECT A1.name, A1.account, MIN(A1.balance), COUNT(*
FROM AccountNames AS A
JOIN AccountNames AS A
ON A1.account = A2.accoun
AND A1.name >= A2.nam
GROUP BY A1.name, A1.account
AS A(name, account, balance, seq
GROUP BY accoun
--
David Porta
--
Please reply only to the newsgrou
-|||SELECT MIN(CASE seq WHEN 1 THEN fullname END)+
COALESCE(', '+MIN(CASE seq WHEN 2 THEN fullname END),'')+
COALESCE(', '+MIN(CASE seq WHEN 3 THEN fullname END),'')+
COALESCE(', '+MIN(CASE seq WHEN 4 THEN fullname END),'')
jointaccount, MIN(balance)
FROM
(SELECT A1.personid, A1.jointaccount, MIN(A1.balance), COUNT(*)
FROM PersonAccount AS A1
JOIN PersonAccount AS A2
ON A1.jointaccount = A2.jointaccount
AND A1.personid >= A2.personid
GROUP BY A1.personid, A1.jointaccount)
AS A(personid, jointaccount, balance, seq)
JOIN
(SELECT personid,
COALESCE(firstname+' ','')+
COALESCE(middlename+' ','')+
COALESCE(lastname,'') AS fullname
FROM Person) AS P
ON A.personid = P.personid
GROUP BY jointaccount
--
David Portas
--
Please reply only to the newsgroup
--

how to combine two different SQL server data instance into one db?

Hi

I'd like to know how I can combine two tables from two different sql instances ito one db?

thanks.

Volkan, can you provide a little more info?

Are you trying move the data from sql instance 1 and sql instance 2 into a new database on sql instance 3?

Are you asking how you can query the data from two different instances?

You can query separate instances by using a four-part qualifier if the instances are defined as linked servers.

select * from [servername].[databasename].[schema].[tablename]

Example: server = MySQL1, database = MyDatabase, schema = dbo, table = MyTable

select * from MySQL1.MyDatabase.dbo.MyTable

|||

If both the DBs are on same Server and the current user have access permission on both database then you can use the following query..

Select SomeColumns From CurrentDBName..TableName
Select SomeColumns From OtherDBName..TableName

If the databases are on different Server then you have to use the Linked Server..

EXEC sp_addlinkedserver @.server = 'SERVER', @.provider = 'SQLOLEDB.1', @.srvproduct = '', @.provstr = 'Privider=SQLOLEDB.1;Data Source=TargetServer;Initial Catalog=Database'

go
Exec sp_addlinkedsrvlogin @.rmtsrvname = 'SERVER', @.useself = true, @.locallogin = null, @.rmtuser = 'Userid', @.rmtpassword = 'Password'

On your SP you can use..

Select * From OpenQuery(MyRemoteServer, 'Select * From Sysobjects')

--OR

Select * From MyRemoteServer.DatabaseName.dbo.Sysobjects

How to combine two columns into one in an sql

I have an stored procedure that returns 3 columns. Month, Date, and Total Number of Calls.

Here is the stored Proc:

SELECTDATEPART(mm, CALLSTARTTIME) , DATEPART(dd, CALLSTARTTIME),COUNT(*)

FROMCALL_LOG_MASTER

WHERE(COMMERCIALS = '1')AND(PINCODEIS NOT NULL)

GROUP BY DATEPART(mm, CALLSTARTTIME),DATEPART(dd, CALLSTARTTIME)

ORDER BY DATEPART(mm, CALLSTARTTIME),DATEPART(dd, CALLSTARTTIME)

It returns a table:

MONTH DATE TOTAL NUMBER OF CALLS
======= ===== ===========
1 1 10

1 2 15

My question is: is it possible to combine the Month and Date column into one column. e.g.

Date Total Number of Calls
==== ==============
1/1 10
1/2 15

Please Help, Thanks in advance :)

SELECT CONVERT(nvarchar(2),DATEPART(mm, CALLSTARTTIME)) + '/' + CONVERT(nvarchar(2),DATEPART(dd, CALLSTARTTIME)) AS [Date],COUNT(*)

FROMCALL_LOG_MASTER

WHERE(COMMERCIALS = '1')AND(PINCODEIS NOT NULL)

GROUP BY DATEPART(mm, CALLSTARTTIME)) + '/' + CONVERT(nvarchar(2),DATEPART(dd, CALLSTARTTIME))

ORDER BY DATEPART(mm, CALLSTARTTIME),DATEPART(dd, CALLSTARTTIME)

|||

thx, I did some configuration. works like a charm

SELECT CONVERT(nvarchar(2),DATEPART(mm, CALLSTARTTIME)) + '/' +CONVERT(nvarchar(2),DATEPART(dd, CALLSTARTTIME))AS[Date],COUNT(*)

FROMCALL_LOG_MASTER

WHERE(COMMERCIALS = '1')AND(PINCODEIS NOT NULL)

GROUP BY DATEPART(mm, CALLSTARTTIME) ,DatePart(dd, Callstarttime),CONVERT(nvarchar(2),DATEPART(mm, CALLSTARTTIME)) + '/' +CONVERT(nvarchar(2),DATEPART(dd, CALLSTARTTIME))

ORDER BY DATEPART(mm, CALLSTARTTIME),DATEPART(dd, CALLSTARTTIME)

How to Combine two column in one table using SQL statement ?

Could you write the simple SQL statement from 'Combine two column in one table '?

I try to use 'Union' which combine two column in two table . thxYou can use the concatention operator (+):

SELECT
 column1 + column2 AS myColumn
FROM
 myTable

Terri|||If the columns are numeric then "+" will sum the column values.
If you do not desire this, you should use str() function

Check the below statement,

select ltrim(str(column1)) + ltrim(str(column2)) from mytable

How to combine to boolean collumns ?

Hi, my name is Wilhelm and I'm new to this forum.

I having problems with a SQL SELECT statement. I'm trying to combine two boolean columns in ms sql server 2000.

I tried this:

SELECT (Bol1 OR Bol2) as Bol

Anyone have any suggestion why this don't work ?
I think i have done something similar in Orcale ones.SELECT (Bol1 | Bol2) as Bol|||I just found out. Thanks anyway :)

how to combine the data as one report (rdl) from accessing two data sources (ODBC)

Hi,
Does any one know how to combine the data as one report (rdl) from accessing
two data sources (ODBC) without using integration services?
any extend method? as I know one rdl should be able to connect to one data
source only. pleaae help & advise
For example:
SQL2005:
source 1: mssql 2000: employee table: employee id, employee name
source 2: Oracle: employee address table: employee id, employee address
I want join get the data from two sources such that a rdl report shows
(employee id, employee name, employee address). Of-course my real case is
quite complexOn Oct 8, 7:53 pm, "frankie lo" <frankiec...@.hotmail.com> wrote:
> Hi,
> Does any one know how to combine the data as one report (rdl) from accessing
> two data sources (ODBC) without using integration services?
> any extend method? as I know one rdl should be able to connect to one data
> source only. pleaae help & advise
> For example:
> SQL2005:
> source 1: mssql 2000: employee table: employee id, employee name
> source 2: Oracle: employee address table: employee id, employee address
> I want join get the data from two sources such that a rdl report shows
> (employee id, employee name, employee address). Of-course my real case is
> quite complex
I'm not sure if this will help, but you might try using a subreport
for a different datasource. That way a secondary report can have it's
own datasource and it can be included in the main report by way of a
subreport control. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Thanks Enrique
But can I use source 1's employee id list as pararmeter for source 2?
"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:1191892063.275037.327290@.g4g2000hsf.googlegroups.com...
> On Oct 8, 7:53 pm, "frankie lo" <frankiec...@.hotmail.com> wrote:
>> Hi,
>> Does any one know how to combine the data as one report (rdl) from
>> accessing
>> two data sources (ODBC) without using integration services?
>> any extend method? as I know one rdl should be able to connect to one
>> data
>> source only. pleaae help & advise
>> For example:
>> SQL2005:
>> source 1: mssql 2000: employee table: employee id, employee name
>> source 2: Oracle: employee address table: employee id, employee address
>> I want join get the data from two sources such that a rdl report shows
>> (employee id, employee name, employee address). Of-course my real case is
>> quite complex
>
> I'm not sure if this will help, but you might try using a subreport
> for a different datasource. That way a secondary report can have it's
> own datasource and it can be included in the main report by way of a
> subreport control. Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||First, just to clear up a misconception you have. A report can have multiple
datasources. What it cannot do is join those data sources. For instance,
let's say you have a parameter on a report. One data source returns a single
record. You could put that in a table or list or whatever. Next below that
you want a bunch of detail. For that you have a second dataset to a
different datasource. No problem whatsoever. However if you want master
detail of the sense that you have multiple masters and for each master
record you want to show detail then that cannot be done with a single
report. You have to use subreports. Master-detail reports are exactly for
what subreports are designed.
You need to understand two things about subreports. First, subreports are
just a report. Like all reports you can have report parameters (and it would
be a rare subreport that didn't). Design and test your subreport stand alone
and make sure it works.
The second thing to understand is the act of embedding and hooking up the
subreport. I just drag and drop the report previously created and tested
onto my main report. You can drag it onto a list. You can drag it onto a
cell of a table control. I do both. Then right mouse click onto the
subreport and map your parameters. You can map your parameters to a field of
a dataset, you can map them to a report parameter on the main report, you
can map them to an expression.
Subreports are very important in RS.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"frankie lo" <frankiecblo@.hotmail.com> wrote in message
news:OVq07ohCIHA.464@.TK2MSFTNGP02.phx.gbl...
> Thanks Enrique
> But can I use source 1's employee id list as pararmeter for source 2?
>
> "EMartinez" <emartinez.pr1@.gmail.com> wrote in message
> news:1191892063.275037.327290@.g4g2000hsf.googlegroups.com...
>> On Oct 8, 7:53 pm, "frankie lo" <frankiec...@.hotmail.com> wrote:
>> Hi,
>> Does any one know how to combine the data as one report (rdl) from
>> accessing
>> two data sources (ODBC) without using integration services?
>> any extend method? as I know one rdl should be able to connect to one
>> data
>> source only. pleaae help & advise
>> For example:
>> SQL2005:
>> source 1: mssql 2000: employee table: employee id, employee name
>> source 2: Oracle: employee address table: employee id, employee address
>> I want join get the data from two sources such that a rdl report shows
>> (employee id, employee name, employee address). Of-course my real case
>> is
>> quite complex
>>
>> I'm not sure if this will help, but you might try using a subreport
>> for a different datasource. That way a secondary report can have it's
>> own datasource and it can be included in the main report by way of a
>> subreport control. Hope this helps.
>> Regards,
>> Enrique Martinez
>> Sr. Software Consultant
>|||Hi Bruce,
Thanks. it becomes clear that i think I am able to do & try. You are very
cool. Thanks a lot.
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:us0p%23inCIHA.3884@.TK2MSFTNGP05.phx.gbl...
> First, just to clear up a misconception you have. A report can have
> multiple datasources. What it cannot do is join those data sources. For
> instance, let's say you have a parameter on a report. One data source
> returns a single record. You could put that in a table or list or
> whatever. Next below that you want a bunch of detail. For that you have a
> second dataset to a different datasource. No problem whatsoever. However
> if you want master detail of the sense that you have multiple masters and
> for each master record you want to show detail then that cannot be done
> with a single report. You have to use subreports. Master-detail reports
> are exactly for what subreports are designed.
> You need to understand two things about subreports. First, subreports are
> just a report. Like all reports you can have report parameters (and it
> would be a rare subreport that didn't). Design and test your subreport
> stand alone and make sure it works.
> The second thing to understand is the act of embedding and hooking up the
> subreport. I just drag and drop the report previously created and tested
> onto my main report. You can drag it onto a list. You can drag it onto a
> cell of a table control. I do both. Then right mouse click onto the
> subreport and map your parameters. You can map your parameters to a field
> of a dataset, you can map them to a report parameter on the main report,
> you can map them to an expression.
> Subreports are very important in RS.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "frankie lo" <frankiecblo@.hotmail.com> wrote in message
> news:OVq07ohCIHA.464@.TK2MSFTNGP02.phx.gbl...
>> Thanks Enrique
>> But can I use source 1's employee id list as pararmeter for source 2?
>>
>> "EMartinez" <emartinez.pr1@.gmail.com> wrote in message
>> news:1191892063.275037.327290@.g4g2000hsf.googlegroups.com...
>> On Oct 8, 7:53 pm, "frankie lo" <frankiec...@.hotmail.com> wrote:
>> Hi,
>> Does any one know how to combine the data as one report (rdl) from
>> accessing
>> two data sources (ODBC) without using integration services?
>> any extend method? as I know one rdl should be able to connect to one
>> data
>> source only. pleaae help & advise
>> For example:
>> SQL2005:
>> source 1: mssql 2000: employee table: employee id, employee name
>> source 2: Oracle: employee address table: employee id, employee
>> address
>> I want join get the data from two sources such that a rdl report shows
>> (employee id, employee name, employee address). Of-course my real case
>> is
>> quite complex
>>
>> I'm not sure if this will help, but you might try using a subreport
>> for a different datasource. That way a secondary report can have it's
>> own datasource and it can be included in the main report by way of a
>> subreport control. Hope this helps.
>> Regards,
>> Enrique Martinez
>> Sr. Software Consultant
>>
>|||On Oct 9, 7:37 pm, "frankie lo" <frankiec...@.hotmail.com> wrote:
> Hi Bruce,
> Thanks. it becomes clear that i think I am able to do & try. You are very
> cool. Thanks a lot.
> "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com> wrote in messagenews:us0p%23inCIHA.3884@.TK2MSFTNGP05.phx.gbl...
> > First, just to clear up a misconception you have. A report can have
> > multiple datasources. What it cannot do is join those data sources. For
> > instance, let's say you have a parameter on a report. One data source
> > returns a single record. You could put that in a table or list or
> > whatever. Next below that you want a bunch of detail. For that you have a
> > second dataset to a different datasource. No problem whatsoever. However
> > if you want master detail of the sense that you have multiple masters and
> > for each master record you want to show detail then that cannot be done
> > with a single report. You have to use subreports. Master-detail reports
> > are exactly for what subreports are designed.
> > You need to understand two things about subreports. First, subreports are
> > just a report. Like all reports you can have report parameters (and it
> > would be a rare subreport that didn't). Design and test your subreport
> > stand alone and make sure it works.
> > The second thing to understand is the act of embedding and hooking up the
> > subreport. I just drag and drop the report previously created and tested
> > onto my main report. You can drag it onto a list. You can drag it onto a
> > cell of a table control. I do both. Then right mouse click onto the
> > subreport and map your parameters. You can map your parameters to a field
> > of a dataset, you can map them to a report parameter on the main report,
> > you can map them to an expression.
> > Subreports are very important in RS.
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> > "frankie lo" <frankiec...@.hotmail.com> wrote in message
> >news:OVq07ohCIHA.464@.TK2MSFTNGP02.phx.gbl...
> >> Thanks Enrique
> >> But can I use source 1's employee id list as pararmeter for source 2?
> >> "EMartinez" <emartinez...@.gmail.com> wrote in message
> >>news:1191892063.275037.327290@.g4g2000hsf.googlegroups.com...
> >> On Oct 8, 7:53 pm, "frankie lo" <frankiec...@.hotmail.com> wrote:
> >> Hi,
> >> Does any one know how to combine the data as one report (rdl) from
> >> accessing
> >> two data sources (ODBC) without using integration services?
> >> any extend method? as I know one rdl should be able to connect to one
> >> data
> >> source only. pleaae help & advise
> >> For example:
> >> SQL2005:
> >> source 1: mssql 2000: employee table: employee id, employee name
> >> source 2: Oracle: employee address table: employee id, employee
> >> address
> >> I want join get the data from two sources such that a rdl report shows
> >> (employee id, employee name, employee address). Of-course my real case
> >> is
> >> quite complex
> >> I'm not sure if this will help, but you might try using a subreport
> >> for a different datasource. That way a secondary report can have it's
> >> own datasource and it can be included in the main report by way of a
> >> subreport control. Hope this helps.
> >> Regards,
> >> Enrique Martinez
> >> Sr. Software Consultant
You're welcome. Also, one thing that Bruce did not mention. If you
want to reference a single value from one dataset (i.e., ds1) in
another (i.e., ds2) you can reference it via an aggregate expression
similar to the following:
=Max(Fields!employee_id.Value, "ds1")
Also, you could try having one of the datasets used as a hidden multi-
select parameter and then reference it from the other dataset via an
expression:
=Parameters!employee_id.Value
Hope this helps further clarify things for you.
Regards,
Enrique Martinez
Sr. Software Consultant|||Hi Enrique,
Thanks for your suggestion, I will try
"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:1191980383.493910.75050@.g4g2000hsf.googlegroups.com...
> On Oct 9, 7:37 pm, "frankie lo" <frankiec...@.hotmail.com> wrote:
>> Hi Bruce,
>> Thanks. it becomes clear that i think I am able to do & try. You are very
>> cool. Thanks a lot.
>> "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com> wrote in
>> messagenews:us0p%23inCIHA.3884@.TK2MSFTNGP05.phx.gbl...
>> > First, just to clear up a misconception you have. A report can have
>> > multiple datasources. What it cannot do is join those data sources. For
>> > instance, let's say you have a parameter on a report. One data source
>> > returns a single record. You could put that in a table or list or
>> > whatever. Next below that you want a bunch of detail. For that you have
>> > a
>> > second dataset to a different datasource. No problem whatsoever.
>> > However
>> > if you want master detail of the sense that you have multiple masters
>> > and
>> > for each master record you want to show detail then that cannot be done
>> > with a single report. You have to use subreports. Master-detail reports
>> > are exactly for what subreports are designed.
>> > You need to understand two things about subreports. First, subreports
>> > are
>> > just a report. Like all reports you can have report parameters (and it
>> > would be a rare subreport that didn't). Design and test your subreport
>> > stand alone and make sure it works.
>> > The second thing to understand is the act of embedding and hooking up
>> > the
>> > subreport. I just drag and drop the report previously created and
>> > tested
>> > onto my main report. You can drag it onto a list. You can drag it onto
>> > a
>> > cell of a table control. I do both. Then right mouse click onto the
>> > subreport and map your parameters. You can map your parameters to a
>> > field
>> > of a dataset, you can map them to a report parameter on the main
>> > report,
>> > you can map them to an expression.
>> > Subreports are very important in RS.
>> > --
>> > Bruce Loehle-Conger
>> > MVP SQL Server Reporting Services
>> > "frankie lo" <frankiec...@.hotmail.com> wrote in message
>> >news:OVq07ohCIHA.464@.TK2MSFTNGP02.phx.gbl...
>> >> Thanks Enrique
>> >> But can I use source 1's employee id list as pararmeter for source 2?
>> >> "EMartinez" <emartinez...@.gmail.com> wrote in message
>> >>news:1191892063.275037.327290@.g4g2000hsf.googlegroups.com...
>> >> On Oct 8, 7:53 pm, "frankie lo" <frankiec...@.hotmail.com> wrote:
>> >> Hi,
>> >> Does any one know how to combine the data as one report (rdl) from
>> >> accessing
>> >> two data sources (ODBC) without using integration services?
>> >> any extend method? as I know one rdl should be able to connect to
>> >> one
>> >> data
>> >> source only. pleaae help & advise
>> >> For example:
>> >> SQL2005:
>> >> source 1: mssql 2000: employee table: employee id, employee name
>> >> source 2: Oracle: employee address table: employee id, employee
>> >> address
>> >> I want join get the data from two sources such that a rdl report
>> >> shows
>> >> (employee id, employee name, employee address). Of-course my real
>> >> case
>> >> is
>> >> quite complex
>> >> I'm not sure if this will help, but you might try using a subreport
>> >> for a different datasource. That way a secondary report can have it's
>> >> own datasource and it can be included in the main report by way of a
>> >> subreport control. Hope this helps.
>> >> Regards,
>> >> Enrique Martinez
>> >> Sr. Software Consultant
>
> You're welcome. Also, one thing that Bruce did not mention. If you
> want to reference a single value from one dataset (i.e., ds1) in
> another (i.e., ds2) you can reference it via an aggregate expression
> similar to the following:
> =Max(Fields!employee_id.Value, "ds1")
> Also, you could try having one of the datasets used as a hidden multi-
> select parameter and then reference it from the other dataset via an
> expression:
> =Parameters!employee_id.Value
> Hope this helps further clarify things for you.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||On Oct 10, 1:37 am, "frankie lo" <frankiec...@.hotmail.com> wrote:
> Hi Enrique,
> Thanks for your suggestion, I will try
> "EMartinez" <emartinez...@.gmail.com> wrote in message
> news:1191980383.493910.75050@.g4g2000hsf.googlegroups.com...
> > On Oct 9, 7:37 pm, "frankie lo" <frankiec...@.hotmail.com> wrote:
> >> Hi Bruce,
> >> Thanks. it becomes clear that i think I am able to do & try. You are very
> >> cool. Thanks a lot.
> >> "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com> wrote in
> >> messagenews:us0p%23inCIHA.3884@.TK2MSFTNGP05.phx.gbl...
> >> > First, just to clear up a misconception you have. A report can have
> >> > multiple datasources. What it cannot do is join those data sources. For
> >> > instance, let's say you have a parameter on a report. One data source
> >> > returns a single record. You could put that in a table or list or
> >> > whatever. Next below that you want a bunch of detail. For that you have
> >> > a
> >> > second dataset to a different datasource. No problem whatsoever.
> >> > However
> >> > if you want master detail of the sense that you have multiple masters
> >> > and
> >> > for each master record you want to show detail then that cannot be done
> >> > with a single report. You have to use subreports. Master-detail reports
> >> > are exactly for what subreports are designed.
> >> > You need to understand two things about subreports. First, subreports
> >> > are
> >> > just a report. Like all reports you can have report parameters (and it
> >> > would be a rare subreport that didn't). Design and test your subreport
> >> > stand alone and make sure it works.
> >> > The second thing to understand is the act of embedding and hooking up
> >> > the
> >> > subreport. I just drag and drop the report previously created and
> >> > tested
> >> > onto my main report. You can drag it onto a list. You can drag it onto
> >> > a
> >> > cell of a table control. I do both. Then right mouse click onto the
> >> > subreport and map your parameters. You can map your parameters to a
> >> > field
> >> > of a dataset, you can map them to a report parameter on the main
> >> > report,
> >> > you can map them to an expression.
> >> > Subreports are very important in RS.
> >> > --
> >> > Bruce Loehle-Conger
> >> > MVP SQL Server Reporting Services
> >> > "frankie lo" <frankiec...@.hotmail.com> wrote in message
> >> >news:OVq07ohCIHA.464@.TK2MSFTNGP02.phx.gbl...
> >> >> Thanks Enrique
> >> >> But can I use source 1's employee id list as pararmeter for source 2?
> >> >> "EMartinez" <emartinez...@.gmail.com> wrote in message
> >> >>news:1191892063.275037.327290@.g4g2000hsf.googlegroups.com...
> >> >> On Oct 8, 7:53 pm, "frankie lo" <frankiec...@.hotmail.com> wrote:
> >> >> Hi,
> >> >> Does any one know how to combine the data as one report (rdl) from
> >> >> accessing
> >> >> two data sources (ODBC) without using integration services?
> >> >> any extend method? as I know one rdl should be able to connect to
> >> >> one
> >> >> data
> >> >> source only. pleaae help & advise
> >> >> For example:
> >> >> SQL2005:
> >> >> source 1: mssql 2000: employee table: employee id, employee name
> >> >> source 2: Oracle: employee address table: employee id, employee
> >> >> address
> >> >> I want join get the data from two sources such that a rdl report
> >> >> shows
> >> >> (employee id, employee name, employee address). Of-course my real
> >> >> case
> >> >> is
> >> >> quite complex
> >> >> I'm not sure if this will help, but you might try using a subreport
> >> >> for a different datasource. That way a secondary report can have it's
> >> >> own datasource and it can be included in the main report by way of a
> >> >> subreport control. Hope this helps.
> >> >> Regards,
> >> >> Enrique Martinez
> >> >> Sr. Software Consultant
> > You're welcome. Also, one thing that Bruce did not mention. If you
> > want to reference a single value from one dataset (i.e., ds1) in
> > another (i.e., ds2) you can reference it via an aggregate expression
> > similar to the following:
> > =Max(Fields!employee_id.Value, "ds1")
> > Also, you could try having one of the datasets used as a hidden multi-
> > select parameter and then reference it from the other dataset via an
> > expression:
> > =Parameters!employee_id.Value
> > Hope this helps further clarify things for you.
> > Regards,
> > Enrique Martinez
> > Sr. Software Consultant
You're welcome. Let me know if I can be of further assistance.
Regards,
Enrique Martinez
Sr. Software Consultant

how to combine text data rows into single row?

Hi!!!!

Need some quick help on SQL.....DTS packages.

I’ve loaded data from text files into a table which is in the following format.....

Seq_No

Row_No

Data

1

1

ABC

2

2

DEF

3

3

GHI

4

4

JKL

5

1

MNO

6

2

PQR

7

3

STU

8

4

VWX

Every four lines correspond to one single logical record. So, all the data in every 4 rows have to be combined into one big string which’ll then be loaded into one col of another temporary table .....structure below.....

Record_No

Data

1

ABCDEFGHIJKL

2

MNOPQRSTUVWX

I’m looking for a way to achieve this WITHOUT using Cursors (as it’ll slow down performance)......

Any suggestions will be highly appreciated.

what data type is the Data column?|||

One way is to use the CLR Aggregate function for string concatenation shown in the BOL

http://msdn2.microsoft.com/en-us/library/ms254508.aspx

and then run a query like this

select ((Seq_No-1) / 4)+1 as Record_No, dbo.Concatenate(Data) as Data
into dest
from source
group by ((Seq_No-1) / 4)+1
go

select * from dest
go

|||

hi pramy,

please refer to this post

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=586833&SiteID=1

regards

|||

hi Derek,

thanks for ur reply. the data type is of varchar.

|||

pramy, you still need help on this issue? if not please mark an answer.

thanks,

derek

|||

hi all,

thanks for ur response.

Derek, i've got the solution for my problem and the query is as given below.

select 'data' = convert(varchar(100),(a.data)+''+(b.data)+''+(c.data)+''+(d.data))

from test_data a,test_data b,test_data c,test_data d

where ((b.seqno=a.seqno+1 and b.rowno=a.rowno+1)and(c.seqno=b.seqno+1 and c.rowno=b.rowno+1)and(d.seqno=c.seqno+1 and d.rowno=c.rowno+1))

how to combine text data rows into single row?

Hi!!!!

Need some quick help on SQL.....DTS packages.

I’ve loaded data from text files into a table which is in the following format.....

Seq_No

Row_No

Data

1

1

ABC

2

2

DEF

3

3

GHI

4

4

JKL

5

1

MNO

6

2

PQR

7

3

STU

8

4

VWX

Every four lines correspond to one single logical record. So, all the data in every 4 rows have to be combined into one big string which’ll then be loaded into one col of another temporary table .....structure below.....

Record_No

Data

1

ABCDEFGHIJKL

2

MNOPQRSTUVWX

I’m looking for a way to achieve this WITHOUT using Cursors (as it’ll slow down performance)......

Any suggestions will be highly appreciated.

what data type is the Data column?|||

One way is to use the CLR Aggregate function for string concatenation shown in the BOL

http://msdn2.microsoft.com/en-us/library/ms254508.aspx

and then run a query like this

select ((Seq_No-1) / 4)+1 as Record_No, dbo.Concatenate(Data) as Data
into dest
from source
group by ((Seq_No-1) / 4)+1
go

select * from dest
go

|||

hi pramy,

please refer to this post

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=586833&SiteID=1

regards

|||

hi Derek,

thanks for ur reply. the data type is of varchar.

|||

pramy, you still need help on this issue? if not please mark an answer.

thanks,

derek

|||

hi all,

thanks for ur response.

Derek, i've got the solution for my problem and the query is as given below.

select 'data' = convert(varchar(100),(a.data)+''+(b.data)+''+(c.data)+''+(d.data))

from test_data a,test_data b,test_data c,test_data d

where ((b.seqno=a.seqno+1 and b.rowno=a.rowno+1)and(c.seqno=b.seqno+1 and c.rowno=b.rowno+1)and(d.seqno=c.seqno+1 and d.rowno=c.rowno+1))

How to Combine results from multiple records into one

Hello,

I have a table which has the following structure:

ID MessageText
001 Hello
001 There
001 Working
003 See
003 you
003 Next
003 Time

How to build a query or store procedure to return result like this:

ID MessageText
001 Hello There Working
003 See you Next Time

Your help/advice is greatly appreciated.

Thanks, FicisaYou don't have anything that would identify the order of the words in the sentence....|||The order is not important, as long as I can put them together into one field.|||USE Northwind
GO

CREATE TABLE myTable99(WordOrder int IDENTITY(1,1), [ID] char(3), MessageText varchar(255))
GO

INSERT INTO myTable99([ID], MessageText)
SELECT '001', 'Hello' UNION ALL
SELECT '001', 'There' UNION ALL
SELECT '001', 'Working' UNION ALL
SELECT '003', 'See' UNION ALL
SELECT '003', 'you' UNION ALL
SELECT '003', 'Next' UNION ALL
SELECT '003', 'Time'
GO

DECLARE @.messagetext varchar(2000), @.MAX_ID char(3), @.MIN_ID char(3)

DECLARE @.myTable99 TABLE ([ID] char(3), messagetext varchar(2000))

SELECT @.MAX_ID = MAX([ID])
, @.MIN_ID = MIN([ID])
FROM myTable99

SELECT @.messagetext = ''

WHILE @.MIN_ID <> @.MAX_ID
BEGIN
SELECT @.messagetext = COALESCE(@.messagetext+ ' ','') + MessageText
FROM myTable99
WHERE [ID] = @.MAX_ID

INSERT INTO @.myTable99([ID], MessageText)
SELECT @.MAX_ID, @.MessageText

SELECT @.MAX_ID = MAX([ID])
FROM myTable99
WHERE [ID] < @.MAX_ID

SELECT @.messagetext = ''
END

SELECT @.messagetext = COALESCE(@.messagetext+ ' ','') + MessageText
FROM myTable99
WHERE [ID] = @.MIN_ID

INSERT INTO @.myTable99([ID], MessageText)
SELECT @.MIN_ID, @.MessageText

SELECT * FROM @.myTable99
GO

DROP TABLE myTable99
GO|||aaaaaaaaaaaarggggh! code!!!

my eyes!!!!! they're BLEEEEEEEEEDING!!

hey, how's this for a solution:

select ID
, group_concat(MessageText) as MessageText
from atable
group
by ID

see GROUP BY functions (http://dev.mysql.com/doc/mysql/en/GROUP-BY-Functions.html)

not sql server?

pity, eh

:cool:|||That's mySQL not SQL Server|||Ok, how about the hybred approach:CREATE TABLE dbo.tMessages (
Id CHAR(3) NOT NULL
, MessageText VARCHAR(20) NOT NULL
)

INSERT dbo.tMessages (Id, MessageText)
SELECT '001', 'Hello'
UNION ALL SELECT '001', 'There'
UNION ALL SELECT '001', 'Working'
UNION ALL SELECT '003', 'See'
UNION ALL SELECT '003', 'you'
UNION ALL SELECT '003', 'Next'
UNION ALL SELECT '003', 'Time'

DROP FUNCTION dbo.fGrunge
GO
CREATE FUNCTION dbo.fGrunge (
@.id CHAR(3)
) RETURNS VARCHAR(4000) AS
BEGIN
DECLARE @.c VARCHAR(20), @.r VARCHAR(8000)
SET @.r = ''

DECLARE zfGrunge CURSOR FOR SELECT MessageText
FROM dbo.tMessages
WHERE id = @.id
ORDER BY MessageText

OPEN zfGrunge
FETCH zfGrunge INTO @.c

WHILE 0 = @.@.fetch_status
BEGIN
SET @.r = @.r + ' ' + @.c
FETCH zfGrunge INTO @.c
END

CLOSE zfGrunge
DEALLOCATE zfGrunge

RETURN SubString(@.r, 2, 8000)
END
GO

SELECT a.id, dbo.fGrunge(a.id)
FROM dbo.tMessages AS a
GROUP BY a.id
ORDER BY a.id

DROP TABLE dbo.tMessages-PatP|||Pat,

A cursor?

Damn, it's only Monday....|||Oh wow,

Perfect!!!

Brett's answer works great. There sure are some programming, it would be nice to have group_concat() function in MS SQL. Forgot I can create one of my own. Thanks very much Pat!!!

Now here's another question, I was trying to use cursor to solve it, like in Pat's function, does it have any disadvantage/advantage over Brett's solution?

Thank you a bunch!!!

ficisa|||I can do it with oodles of left joins, but that gets ugly too. While cursors are a good way to kill an application, they are intent on killing this one anyway.

Trawling the result set is better done on the middle tier or the client anyway. This is fundamentally poor design. I was just offering this as a solution to the stated problem, not necessarily advocating it!

-PatP|||How much data you talking about?

Why not perf bechmark both and let us know...(ya gotta do something...:D )

But I'm thinking (ok, ok, don't all be sooo amazed) that the cursor will be slower...|||Originally posted by Brett Kaiser
That's mySQL not SQL Server

dude, i knew that

(something about grandmothers and eggs)

that's why i posted it!!

so that you sql server guys could SEE HOW IT SHOULD BE DONE

:cool:|||Originally posted by r937
dude, i knew that

(something about grandmothers and eggs)

that's why i posted it!!

so that you sql server guys could SEE HOW IT SHOULD BE DONE

:cool:

Rudy, where is that in the ANSI guide?

I can't find it...

:D|||ansi?

shirley you jest

as if microsoftborg gave two figs for ansi|||Don't call me shirley...

I prefer laverne|||Ok, I tested with my function below and Pat's function using a cursor, with 3436 groups, both took same amount of time of 4 seconds to complete. Hmmm....

So how about this as the function?

CREATE FUNCTION dbo.Group_Concat_Message

(@.InstitutionNumber char(6) )

RETURNS varchar(5000)

AS

BEGIN

DECLARE @.messagetext varchar(5000)

SELECT @.messagetext = COALESCE(@.messagetext+ ' ','') + MessageText
FROM vInstWithMessage
WHERE InstitutionNumber = @.InstitutionNumber

Return @.messagetext

END|||Why not...

Still think you'll end up worrying about the order of the data at some point..

GOOD LUCK...

and OH MY GOD...it's after 5:00!

Later..

How to Combine Multiple Rows Data into single Record or String based on a common field.

Hellow Folks.

Here is the Original Data in my single SQL 2005 Table:

Department: Sells:

1 Meat

1 Rice

1 Orange

2 Orange

2 Apple

3 Pears

The Data I would like read separated by Semi-colon:

Department: Sells:

1 Meat;Rice;Orange

2 Orange;Apple

3 Pears

I would like to read my data via SP or VStudio 2005 Page . Any help will be appreciated. Thanks..

Hi,

you can use the following Function in SQL server:

USE NORTHWIND
GO
CREATE FUNCTION ProductList (@.CategoryIDINT)
RETURNSVARCHAR(1000)
AS
BEGIN
DECLARE @.ProductsVARCHAR(1000)
SELECT@.Products =COALESCE(@.Products +', ','') + ProductName
FROM Products
WHERE CategoryID = @.CategoryID
ORDER BY ProductNameASC
RETURN @.Products
END
GO
SELECTDISTINCT CategoryID, dbo.ProductList (CategoryID)AS ProductList
FROM Products
GO

and this is based on your table: 

USE NORTHWINDCS
GO
CREATE FUNCTION ProductList (@.CategoryIDINT)
RETURNSVARCHAR(1000)
AS
BEGIN
DECLARE @.ProductsVARCHAR(1000)
SELECT@.Products =COALESCE(@.Products +', ','') + sells
FROM table1
WHERE Department = @.CategoryID
ORDER BY sellsASC
RETURN @.Products
END
GO
SELECTDISTINCT Department, dbo.ProductList (department)AS ProductList
FROM table1
GO
 

thanks

|||

SharpGuy, your solution works. Thanks and have a great Thanksgiving...

How to combine Multiple Dataset result in each row of the report

Hi,

I need to produce a report in MS SQL Server Reporting Service 2005 which has some date fields & need to pass these date fields to find out the no_of_User on that date from another database. For example

Itemid

Availabilty_Start_Dt

Availabilty_End_Dt

User_On_St_DT (this info is in another DB & need to pass Availabilty_Start_Dt )

User_On_ED_DT (this info is in another DB & need to pass Availabilty_Start_Dt )

I am facing two problem here. Since hereis two different dataset needed how can I combine the dataset result to produce each row of the report.

REally appreciate if anyone can show me some light on it.

Regards,

write a strored procedure in the backend and try to write the two queries and combine the results and store in a temp table.

use the stored procedure in the reporting services. Backend is the best solution for it.

How to combine MAx statement with specific search?

Hello everyone!

I've got a table (of "Persons") with 2 columns: "Age" and "Name".
I make a procedure that get the MAX of "Age" of the table, like this:

SELECT
MAX (p.Age) MaxAge
FROM
Persons p

But I'll like to add a new column to my result table. That column corresponds to the "Age" of a person 'X'. 'X' is its 'Name' and i will pass it by parameter to the procedure.
Something like this:

SELECT

MAX (p.Age) MaxAge, --The highest age of the table
p.Age ConcretPersonAge -- The age of the person X

FROM

Persons p

That should returns a single row result table with the max of all 'Age" and the 'Age' of the person 'X'. Anyone can help me to do this?

Thank you to everyon, and a happy new year

Jonathan

You can use a subquery, a derived table or store the result in a variable.

i.e. the sub query one looks like this

select (select max(age) from person) as maxAge, age

from person

where name = @.name

|||

You can use the following query,

SELECT
(Selecct MAX (Age) From Persons) MaxAge, --The highest age of the table
p.Age ConcretPersonAge -- The age of the person X
FROM
Persons P

|||Tahnk you very much. But I think I've simplified too much my query problem. I've got a third column. That its' cities. The original table is actually "Age", "Person","City". I need the maximum age and the age of the person X in each city (multiple rows result table). Could you help me?

Thank you evryone, specially:Mani and Simon.|||

In which case a derived table is the best option

select maxAge.age

, person.age

, city

from (select max(age) age, city from person where name = @.name group by city ) maxage

join person on person.city = maxage.city

where name = @.name

How to Combine IN & LIKE

Hai All,
Could some one help me how to combine IN & LIKE in the query selection,
i'v try many syntac and i just have no find the way out

this is what I would like to write:

Select DSCRIPTN,ORMSTRID,ACTINDX
from T50001
where ACTINDX = 350
and DSCRIPTN Like in '%'+(select distinct rtrim(ORMSTRID) ORMSTRID from L10001)+'%'

hi,

you can not combine the two keywords... as you can see from BOL synopsis, LIKE expects an expression and not a multiple reusult.. and you can not combine the expression as you require..

you probably have to rewrite your subquery to retun something comparable with the main query result's primary key.. the LIKE operator should probably go inside the subquery..

regards

|||

Something like:

Select DSCRIPTN,ORMSTRID,ACTINDX
from T50001
where ACTINDX = 350
and EXISTS
(

select * FROM L10001 WHERE DSCRIPTN LIKE '%' + ORMSTRID +'%'

)

If you have multiple entries in the L10001 table you should use a subquery which create the DISTINCT values on the fly.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

how to combine fromdate and todate values in one field

Hi All,

I have to fetch FromDate and Todate values from the table like this.Suppose Fromdate value is 02-Feb-2007 and Todate Value is 04-Feb-2007,then my need is to get the date value like this.....Feb 2-4,2007or 2-4 Feb,2007.Can anybody know the syntax or code?.I am using sql Server and fromdate and todate values are stored in two different feilds in table.

Thanks and Regards

This seems to be a duplicate post? I posted an answer in the other post, here it is again. This assumes two columns: from_date and to_date. If it fixes your problem mark one or both as answered!

Selectconvert(varchar(2),DatePart(day, from_date)) +'-' +convert(varchar(2),DatePart(day, to_date))+' '+substring(convert(varchar(12), to_date, 106), 4, 8)from [yourtable]

How to combine different row into one row

Dear all,
I got a table with different columns of same client id as
follows:
e.g.
ClientID Name Age Country
1 Peter
1 32
1 China
Now I want to combine these three rows into one rows as
follows
ClientID Name Age Country
1 Peter 32 China
How can I do that by SQL statements. Just simple SQL
statements.
Not sotred procedures.
Thankshon123456 wrote:
> Dear all,
> I got a table with different columns of same client id as
> follows:
> e.g.
> ClientID Name Age Country
> 1 Peter
> 1 32
> 1 China
>
> Now I want to combine these three rows into one rows as
> follows
> ClientID Name Age Country
> 1 Peter 32 China
> How can I do that by SQL statements. Just simple SQL
> statements.
> Not sotred procedures.
>
> Thanks
What do the spaces in your example represent? What is the key of this
table? If those missing values are always just empty then do:
SELECT clientid,
MAX(name) AS name,
MAX(age) AS age,
MAX(country) AS country
FROM your_table
GROUP BY clientid ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Hi
CREATE TABLE #test (col1 INT, col2 CHAR(1),col3 INT,col4 CHAR(1))
INSERT INTO #Test VALUES (1,'a',NULL,NULL)
INSERT INTO #Test VALUES (1,NULL,32,NULL)
INSERT INTO #Test VALUES (1,NULL,NULL,'b')
SELECT MAX(col1),MAX(col2),MAX(col3),MAX(col4)
FROM #test
--or
SELECT col1,
MAX(CASE WHEN col2 IS NOT NULL THEN col2 end)as col2,
MAX(CASE WHEN col3 IS NOT NULL THEN col3 end)as col3,
MAX(CASE WHEN col4 IS NOT NULL THEN col4 end)as col4
FROM #test
GROUP BY col1
"hon123456" <peterhon321@.yahoo.com.hk> wrote in message
news:1140524835.788163.177040@.g43g2000cwa.googlegroups.com...
> Dear all,
> I got a table with different columns of same client id as
> follows:
> e.g.
> ClientID Name Age Country
> 1 Peter
> 1 32
> 1 China
>
> Now I want to combine these three rows into one rows as
> follows
> ClientID Name Age Country
> 1 Peter 32 China
> How can I do that by SQL statements. Just simple SQL
> statements.
> Not sotred procedures.
>
> Thanks
>|||SELECT ClientID, MIN(Name) as Name, Min(Age) as Age, Min(Country) as
Country
FROM Table
GROUP BY ClientID
Stu|||Thanks for you all.

How to combine different data regions in a single report?

Hi, all here,

I am trying to use report designer to create a single report with chart data region and table data region combined within the same report. Would anyone here please guide me how to work it out? Thanks a lot.

With best regards,

What do you mean by combined? You can put several data regions in a report and each can have their own datasource or share the same. If you want to have a chart data region inside a table data region, then I believe that cannot be done. If you could be a little more specific as to what you want to accomplish that would help to answer your question.|||Hi,Itzeld,thanks, got it done.

How to combine a calendar control?

This is a multi-part message in MIME format.
--=_NextPart_000_0052_01C4F56E.058CFCB0
Content-Type: text/plain;
charset="windows-1255"
Content-Transfer-Encoding: quoted-printable
If I include a date parameter in a report- in the title of the deployed = report appears a textbox in which the user should type the date in the = correct format with no mistakes (08/01/2004 ?.. 01/08/2004 ?.. 20040108 = ?..).
Is there a way to combine a standart windows calendar control instead? = how?
--=_NextPart_000_0052_01C4F56E.058CFCB0
Content-Type: text/html;
charset="windows-1255"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
If I include a date parameter in a = report- in the title of the deployed report appears a textbox in which the user should = type the date in the correct format with no mistakes (08/01/2004 ?.. = 01/08/2004 ?.. 20040108 ?..).
Is there a way to combine a standart = windows calendar control instead? how?
--=_NextPart_000_0052_01C4F56E.058CFCB0--This is a multi-part message in MIME format.
--=_NextPart_000_000F_01C4F624.EE666500
Content-Type: text/plain;
charset="windows-1255"
Content-Transfer-Encoding: quoted-printable
Hi Geri,
You'll need to provide your own user interface (eg. a web page) and =place the control on it to capture (and validate) the date before the =report submision is made.
Usuallly, it's best to create a web page that will call RS to determine =what parameters are required for the report at runtime and dynamically =show the user control that best fists the parameter datatype.
This generic web page can then be used over and over to provide =parameter entry / validation support for other reports as well.
- peteZ
"Geri Reshef" <GeriReshef@.Yahoo.com> wrote in message =news:uOMd81V9EHA.2032@.tk2msftngp13.phx.gbl...
If I include a date parameter in a report- in the title of the =deployed report appears a textbox in which the user should type the date =in the correct format with no mistakes (08/01/2004 ?.. 01/08/2004 ?.. =20040108 ?..).
Is there a way to combine a standart windows calendar control instead? =how?
--=_NextPart_000_000F_01C4F624.EE666500
Content-Type: text/html;
charset="windows-1255"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Hi Geri,
You'll need to provide your own user interface (eg. =a web page) and place the control on it to capture (and validate) the date before the report submision is made.
Usuallly, it's best to create a web page that will =call RS to determine what parameters are required for the report at runtime and =dynamically show the user control that best fists the parameter =datatype.
This generic web page can then be used over and over =to provide parameter entry / validation support for other reports as well.
- peteZ
"Geri Reshef" =wrote in message news:uOMd81V9EHA.2032=@.tk2msftngp13.phx.gbl...
If I include a date parameter in a =report- in the title of the deployed report appears a textbox in which the user =should type the date in the correct format with no mistakes (08/01/2004 ?.. 01/08/2004 ?.. 20040108 ?..).
Is there a way to combine a standart =windows calendar control instead? how?

---

--=_NextPart_000_000F_01C4F624.EE666500--|||This is a multi-part message in MIME format.
--=_NextPart_000_01C5_01C4F61F.AA3585F0
Content-Type: text/plain;
charset="windows-1255"
Content-Transfer-Encoding: quoted-printable
"PeteZ" <peteZ@.aol.com> wrote in message =news:%23r4R4ic9EHA.3940@.tk2msftngp13.phx.gbl...
Hi Geri,
You'll need to provide your own user interface (eg. a web page) and =place the control on it to capture (and validate) the date before the =report submision is made.
Usuallly, it's best to create a web page that will call RS to =determine what parameters are required for the report at runtime and =dynamically show the user control that best fists the parameter =datatype.
This generic web page can then be used over and over to provide =parameter entry / validation support for other reports as well.
- peteZ
"Geri Reshef" <GeriReshef@.Yahoo.com> wrote in message =news:uOMd81V9EHA.2032@.tk2msftngp13.phx.gbl...
If I include a date parameter in a report- in the title of the =deployed report appears a textbox in which the user should type the date =in the correct format with no mistakes (08/01/2004 ?.. 01/08/2004 ?.. =20040108 ?..).
Is there a way to combine a standart windows calendar control =instead? how?
--=_NextPart_000_01C5_01C4F61F.AA3585F0
Content-Type: text/html;
charset="windows-1255"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
"PeteZ" wrote in message news:%23r4R4ic9EHA.=3940@.tk2msftngp13.phx.gbl...
Hi Geri,

You'll need to provide your own user interface =(eg. a web page) and place the control on it to capture (and validate) the date before the report submision is made.

Usuallly, it's best to create a web page that will =call RS to determine what parameters are required for the report at runtime =and dynamically show the user control that best fists the parameter datatype.

This generic web page can then be used over and =over to provide parameter entry / validation support for other reports as well.

- peteZ

"Geri Reshef" =wrote in message news:uOMd81V9EHA.2032=@.tk2msftngp13.phx.gbl...
If I include a date parameter in a =report- in the title of the deployed report appears a textbox in which the user =should type the date in the correct format with no mistakes (08/01/2004 ?.. = 01/08/2004 ?.. 20040108 ?..).
Is there a way to combine a =standart windows calendar control instead? how?

---

--=_NextPart_000_01C5_01C4F61F.AA3585F0--|||You'd better combine a web calendar control in a web application ,which call
Reporting Service as a web service.I.ve implemented it .
"Geri Reshef" wrote:
> If I include a date parameter in a report- in the title of the deployed report appears a textbox in which the user should type the date in the correct format with no mistakes (08/01/2004 ?.. 01/08/2004 ?.. 20040108 ?..).
> Is there a way to combine a standart windows calendar control instead? how?

How to combine 3 SQL statements into 1?

I have the following 3 SQL statements that need to be combined, if
possible. The output of one feeds the input of the next. I need to view
all of the defined output fields (the output needs to be used in a
Crystal Report).

The SQL Follows:
/* Input is ISBN (vendor_part_number) */
QUERY_1 - returns 1 record
select p.product_id, v.name, m.description, p.author, p.title,
p.revision_number, p.copyright_edition, p.vendor_part_number,
p.conforming_flag,
m.code, mp.unit_price_product, mm.quota_price
from T_PRODUCT p, T_VENDOR v, T_PRODUCT_VENDOR pv,
T_MULTILIST_PRODUCT mp, T_MULTILIST m,
T_MULTILIST_MEMBERSHIP mm where
/* p.vendor_part_number == input */
p.vendor_part_number = '0153364475' and p.medium_type =
'TEXTBOOK' and
p.product_id = pv.product_id and pv.type = 'CONTRACT' and
pv.vendor_id = v.id and
p.product_id = mp.product_id and
m.code = mp.multilist_code and m.proclamation_year =
mp.proclamation_year
and m.proclamation_seq_id = mp.proclamation_seq_id and
m.code = mm.multilist_code and m.proclamation_year =
mm.proclamation_year
and m.proclamation_seq_id = mm.proclamation_seq_id
/* The above should return a single record */

QUERY_2 - returns 2 records
select p.product_id, p.consumable, p.title, p.copyright_edition,
p.vendor_part_number, p.product_type,
p.item_type, p.hardware_required
from T_PRODUCT p, T_PRODUCT_RELATION pr where (pr.relationship_type =
'AID'
or pr.relationship_type = 'KIT') and
p.product_id = pr.child_product_id and pr.parent_product_id =
90321
/* 90321 = result from above: pr.parent_product_id = p.product_id
*/

QUERY_3 - returns 18 records
select p.product_id, p.consumable, p.title, p.copyright_edition,
p.vendor_part_number, p.product_type,
p.item_type, p.hardware_required
from T_PRODUCT p, T_PRODUCT_RELATION pr where (pr.relationship_type =
'AID'
or pr.relationship_type = 'KIT') and
p.product_id = pr.child_product_id and pr.parent_product_id in
(90322, 90323)
/* 90322, 90323 = result from QUERY_2: pr.parent_product_id =
p.product_id */

Only 21 records are returned from these combined queries. I need access
to all of them even though there are 3 different resultsets, 2 of which
contain the same fields. Is there a way to simplify this into a stored
procedure or a view that can take 1 input parameter? It needs to be
used in a Crystal Report, which is limited in its handling of these
types of complex queries.Hi,

Cant you use Derived tables? I havent gone through the queries though.
But generally when I need to query on results from a query, I use a
derived table.

Its something like this...

select * from
(select * from Employee) A

Dont know whether it will work in your case...

Kart

MackTheKnife wrote:

Quote:

Originally Posted by

I have the following 3 SQL statements that need to be combined, if
possible. The output of one feeds the input of the next. I need to view
all of the defined output fields (the output needs to be used in a
Crystal Report).
>
The SQL Follows:
/* Input is ISBN (vendor_part_number) */
QUERY_1 - returns 1 record
select p.product_id, v.name, m.description, p.author, p.title,
p.revision_number, p.copyright_edition, p.vendor_part_number,
p.conforming_flag,
m.code, mp.unit_price_product, mm.quota_price
from T_PRODUCT p, T_VENDOR v, T_PRODUCT_VENDOR pv,
T_MULTILIST_PRODUCT mp, T_MULTILIST m,
T_MULTILIST_MEMBERSHIP mm where
/* p.vendor_part_number == input */
p.vendor_part_number = '0153364475' and p.medium_type =
'TEXTBOOK' and
p.product_id = pv.product_id and pv.type = 'CONTRACT' and
pv.vendor_id = v.id and
p.product_id = mp.product_id and
m.code = mp.multilist_code and m.proclamation_year =
mp.proclamation_year
and m.proclamation_seq_id = mp.proclamation_seq_id and
m.code = mm.multilist_code and m.proclamation_year =
mm.proclamation_year
and m.proclamation_seq_id = mm.proclamation_seq_id
/* The above should return a single record */
>
QUERY_2 - returns 2 records
select p.product_id, p.consumable, p.title, p.copyright_edition,
p.vendor_part_number, p.product_type,
p.item_type, p.hardware_required
from T_PRODUCT p, T_PRODUCT_RELATION pr where (pr.relationship_type =
'AID'
or pr.relationship_type = 'KIT') and
p.product_id = pr.child_product_id and pr.parent_product_id =
90321
/* 90321 = result from above: pr.parent_product_id = p.product_id
*/
>
QUERY_3 - returns 18 records
select p.product_id, p.consumable, p.title, p.copyright_edition,
p.vendor_part_number, p.product_type,
p.item_type, p.hardware_required
from T_PRODUCT p, T_PRODUCT_RELATION pr where (pr.relationship_type =
'AID'
or pr.relationship_type = 'KIT') and
p.product_id = pr.child_product_id and pr.parent_product_id in
(90322, 90323)
/* 90322, 90323 = result from QUERY_2: pr.parent_product_id =
p.product_id */
>
>
Only 21 records are returned from these combined queries. I need access
to all of them even though there are 3 different resultsets, 2 of which
contain the same fields. Is there a way to simplify this into a stored
procedure or a view that can take 1 input parameter? It needs to be
used in a Crystal Report, which is limited in its handling of these
types of complex queries.

How to combine 2 records into 1 unique record

Hi all,

We have an app that uses SQL 2000. I am trying to track when a code field
(selcode) is changed on an order which then causes a status field (status)
to change. I tried a trigger but the app may use 2 different update
statements to change these fields depending on what the user does. When the
trigger fires (on update to selcode), the status field has already been
changed. So my trigger to record the changes from inserted and deleted do
not get the true 'before' value of the status field.

The app does use a log table that tracks these changes. The problem I am
having is that 2 records are created, one for the change to selcode and
another for the change to status.

I am looking for help with a script to combine the existence of these 2 log
records into 1 unique record or occurance that I can track.

example:
ordlog: table that logs order changes
ordernr: order number
varname: name of field being changed
old_value: contents of field before change
new_value: contents of field after change
syscreated: date/time of log entry

SELECT ordernr, varname, old_value, new_value, syscreated
FROM ordlog
where varname = 'selcode' and ordernr = '10580'

SELECT ordernr, varname, old_value, new_value, syscreated
FROM ordlog
where varname = 'status' and ordernr = '10580' and old_value = 'A' and
new_value = 'O'

So I need a way to combine these 2 log entries into a unique occurance. The
ordernr and syscreated could be used to link records. syscreated always
appears to be the same for the 2 log entries down to the second. Selcode
can change from NULL to a number of different values or back to NULL.Status
is either 'A' for approved or 'O' for open. An order can have many log
entries during its life. The selcode may be changed several times for the
same order.

Ideally, I would like a result that links 2 log entries and shows the status
changed from 'A' to 'O' when selcode changed.

Thanks for your time.rdraider (rdraider@.sbcglobal.net) writes:

Quote:

Originally Posted by

SELECT ordernr, varname, old_value, new_value, syscreated
FROM ordlog
where varname = 'selcode' and ordernr = '10580'
>
>
SELECT ordernr, varname, old_value, new_value, syscreated
FROM ordlog
where varname = 'status' and ordernr = '10580' and old_value = 'A' and
new_value = 'O'
>
>
So I need a way to combine these 2 log entries into a unique occurance.
The ordernr and syscreated could be used to link records. syscreated
always appears to be the same for the 2 log entries down to the second.
Selcode can change from NULL to a number of different values or back to
NULL.Status is either 'A' for approved or 'O' for open. An order can
have many log entries during its life. The selcode may be changed
several times for the same order.
>
Ideally, I would like a result that links 2 log entries and shows the
status changed from 'A' to 'O' when selcode changed.

Could this do:

SELECT a.ordernr, a.syscreated,
oldselcode = a.old_value, newselcode = a.new_value,
oldstatus = b.old_value, newstatus = b.new_value
FROM ordlog a
JOIN ordlog b ON a.ordernr = b.ordernr
AND datediff(seconds, a.syscreated, b.syscreated) <= 1
WHERE a.varname = 'selcode'
AND b.varname = 'status'
AND coalesce(a.old_value, '') <coalesce(a.new_value, '')
AND a.old_value = 'A'
AND b.new_value = 'B'

Note: this is an untested query.

If the does not return the expected results, I suggest that you post:

o CREATE TABLE statments for the involved table(s).
o INSERT statements with sample data.
o The desired output given the sample.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx