Query written in CODE part is not working

Hi
I have a report in which the Dataset is filled using MDX query and for every row in the DataSet the Function in the CODE is called, which in turn Connects to the DB and Selects a particular value. The data set is getting filled but the CODE (which contains the SQL Select query) is not getting executed.
I have references System.data, System.data.SQLClient, System.data.Xml dlls for a RDL and have written a connection string with UID and Pwd and the userid has sufficient permissions.
When i Preview it(using visual studio), the value(O/P, the expected value) is shown in the report but i dont see any query executed in the SQL Profiler. Also when i preview this report through the report viewer control from an Window Application, i dont get the output. I dont seem to figure out what the problem is. Please HELP!!

Thanking you in advance.
Regards
Sai
Not sure if I undestand your scenario well. Do you have a VB.NET function embedded in the report? If so, most likely the function is erroring out or you are facing a security issue. What I'd suggest is moving the code to an external .NET assembly. Then, set your report as a startup item on the project properties. Put a breakpoint in the custom function and hit F5 to load the report in the Report Host. When the report is run, the breakpoint should be hit from the first dataset row and you should be able to troubleshoot what's wrong.sql

Query written in CODE part is not working

Hi
I have a report in which the Dataset is filled using MDX query and for every row in the DataSet the Function in the CODE is called, which in turn Connects to the DB and Selects a particular value. The data set is getting filled but the CODE (which contains the SQL Select query) is not getting executed.

I have references System.data, System.data.SQLClient, System.data.Xml

dlls for a RDL and have written a connection string with UID and Pwd

and the userid has sufficient permissions.
When i Preview it(using visual studio), the value(O/P, the expected value) is shown in the report but i dont see any query executed in the SQL Profiler. Also when i preview this report through the report viewer control from an Window Application, i dont get the output. I dont seem to figure out what the problem is. Please HELP!!

Thanking you in advance.
Regards
SaiNot sure if I undestand your scenario well. Do you have a VB.NET function embedded in the report? If so, most likely the function is erroring out or you are facing a security issue. What I'd suggest is moving the code to an external .NET assembly. Then, set your report as a startup item on the project properties. Put a breakpoint in the custom function and hit F5 to load the report in the Report Host. When the report is run, the breakpoint should be hit from the first dataset row and you should be able to troubleshoot what's wrong.

query without using cursor

hi friends,

i want to get that row's startdatetime where sum of duration becomes equal
to or greater than 1000 without using cursor.
create table test
(
duration int,
startdatetime bigint primary key,
userid int
)
go
insert into practise
select 400, 500, 1
union all
select 500, 600, 1
union all
select 100, 650, 1
union all
select 100, 700, 1
go

thnks in adv.,
chakriWould the answer in this case be 650?|||it will be 650 and 700 according to user id|||Sorry - you'll have to explain to me:
What does the user ID have to do with it?
Where does 60 come from?
And why does the answer turn out to be 700?

My reading of the problem was that:
You intend to order the set by startdatetime ASC. Starting from the first record read the duration. If >= 1000 then the first starttime is the result. If not, add the next duration. If >= 1000 then the second starttime is the result. And so on. That's how I got 650 (400 + 500 + 100 = 1000).|||Ok - you edited your typo while I was posting :D

So - why two answers?|||ya sorry i typed wrong.. it should return equal to or greater than 1000. so 650 and 700 are the records as per the data. so it should return those. as i need all the records which lay according to the condition.|||Got you.

Well - the good news is - you don't need to use a cursor.
The bad news is the best you can do is replace it with a loop. Which isn't any better.

There probably is a set based answer to this however I believe that set based solutions to the running total problem don't tend to compare well even to cursors (http://www.sql-server-performance.com/mm_cursor_friendly_problem.asp - I can't but think that the author got a bit confused during his summary though as it doesn't seem to corrolate with his observations).

HTH|||i need to work it.. i am studying your link.. anyway thanks for this and could you help me how to Generate a Fixed length text file as i mean from a table i want specific columns into a .txt file. how to do this. could you guide me.|||What determines the order of the rows for the running total? All of the user id values are 1 in your example, how do you get two answers? I'm pretty sure that there is a set based solution, but I don't understand the problem well enough to solve it at all, much less find a good solution!

-PatP

query with a column per row of a linked table - dynamic sql

I've thought about this a bit more and relised that it can be done using
dynamic SQL, but I would like to avoid this if possible. What are the other
options?
"WCL" <WCL@.nospam.nospam> wrote in message
news:usRAcZGIGHA.1312@.TK2MSFTNGP09.phx.gbl...
> Is it possible to have query result to have a column per row of a table?
>
> e.g.
> Employees table
> ID (identity)
> FirstName
> Ref
>
> containing
> ID, FirstName
> 1 Tom
> 2 Dick
> 3 Harry
>
>
> Project table
> ID (identity)
> Name
>
> containing
> ID, Name
> 1 Client A
> 2 Client B
> 3 Client C
>
> Timesheets
> ID (identity)
> Employees_ID
> Project_ID
> Hours
>
> containing 6 rows
> ID
> Employee_ID
> Project_ID
> Hours
> 1
> 1
> 1
> 5
> 2
> 1
> 2
> 15
> 3
> 2
> 1
> 2
> 4
> 2
> 2
> 4
> 5
> 3
> 1
> 8
> 6
> 3
> 2
> 8
>
> NB - No records for client C
>
> I can get three columns (name, client, hours) with nine rows no problem,
> but how do I get 3 rows with a column per Client, like this:?
>
> Name
> Client A
> Client B
> Client C
> Tom
> 5
> 15
> 0 or NULL
> Dick
> 2
> 4
> 0 or NULL
> Harry
> 8
> 8
> 0 or NULL
>
>
>No, it is not really useful from a SQL standpoint to do this, so it is not a
part of SQL (columns should contain like things, not multiple different
things.)
I think you can do it using several UNION ALLs:
SELECT client, cast(client as varchar(30)), 1 as sorting
FROM table
UNION ALL
SELECT client,cast(value1 as varchar(30)), 2 as sorting
FROM table
order by client, sorting --maybe something different than key
I would suggest using the client tool to format data like this.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"WCL" <WCL@.nospam.nospam> wrote in message
news:u4q6ipGIGHA.3176@.TK2MSFTNGP12.phx.gbl...
> I've thought about this a bit more and relised that it can be done using
> dynamic SQL, but I would like to avoid this if possible. What are the
> other options?
> "WCL" <WCL@.nospam.nospam> wrote in message
> news:usRAcZGIGHA.1312@.TK2MSFTNGP09.phx.gbl...
>

query with a column per row of a linked table

Is it possible to have query result to have a column per row of a table?
e.g.
Employees table
ID (identity)
FirstName
Ref
containing
ID, FirstName
1 Tom
2 Dick
3 Harry
Project table
ID (identity)
Name
containing
ID, Name
1 Client A
2 Client B
3 Client C
Timesheets
ID (identity)
Employees_ID
Project_ID
Hours
containing 6 rows
ID
Employee_ID
Project_ID
Hours
1
1
1
5
2
1
2
15
3
2
1
2
4
2
2
4
5
3
1
8
6
3
2
8
NB - No records for client C
I can get three columns (name, client, hours) with nine rows no problem, but
how do I get 3 rows with a column per Client, like this:?
Name
Client A
Client B
Client C
Tom
5
15
0 or NULL
Dick
2
4
0 or NULL
Harry
8
8
0 or NULLWCL wrote:
> Is it possible to have query result to have a column per row of a table?
This is a common problem, known as "crosstab query" (hint: Google that).
First, you will want to read this:
http://www.stephenforte.net/owdasbl...>
15d6d813eeb8
This is harder to do when the number of ouput columns isn't static. I am
not aware of any ways to do that without dynamic SQL on SQL Server 2000 and
below. SQL Server 2005 provides PIVOT functionality -- which I have yet to
play with myself, but believe does exactly that.
Chris Priede|||"Chris Priede" <priede@.panix.com> wrote in message
news:%235a8RMHIGHA.676@.TK2MSFTNGP10.phx.gbl...
> WCL wrote:
> This is a common problem, known as "crosstab query" (hint: Google that).
> First, you will want to read this:
>
http://www.stephenforte.net/owdasbl...>
15d6d813eeb8
I do not think this is the CASE at all:)

> This is harder to do when the number of ouput columns isn't static. I am
> not aware of any ways to do that without dynamic SQL on SQL Server 2000
and
> below. SQL Server 2005 provides PIVOT functionality -- which I have yet
to
> play with myself, but believe does exactly that.
You be confusing your 'belief' with your 'wish':)
An alternative may be found @.
www.rac4sql.net|||Hi,
05ponyGT wrote:
> I do not think this is the CASE at all:)
> You be confusing your 'belief' with your 'wish':)
> An alternative may be found @.
> www.rac4sql.net
The absence of any technical insight to accompany your assertions led me to
Google your posting name. Of the 11 results returned, 11 are pushing this
particular product.
In addition, I couldn't help but notice that both the Rac "F.A.Q." and "What
can Rac do" section of documentation suffer from multiple instances of
incorrect usage of "your" vs. "you're", as well as other grammatical
sloppyness. It may help your advertising efforts to fix those first. :)
Chris Priede|||"Chris Priede" <priede@.panix.com> wrote in message
news:O7kZP3HIGHA.2472@.TK2MSFTNGP10.phx.gbl...
> Hi,
> 05ponyGT wrote:
> The absence of any technical insight to accompany your assertions led me
to
> Google your posting name. Of the 11 results returned, 11 are pushing this
> particular product.
That is extremely thin!
I'm aware my reply may have been a bit ponderous but just what
didn't you understand?Talk about insight:)

> In addition, I couldn't help but notice that both the Rac "F.A.Q." and
"What
> can Rac do" section of documentation suffer from multiple instances of
> incorrect usage of "your" vs. "you're", as well as other grammatical
> sloppyness. It may help your advertising efforts to fix those first. :)
You are right.Content always comes in second:)|||> http://www.stephenforte.net/owdasbl...
5-15d6d813eeb8
vey usefull link , then I googled for 'crosstab query.
Found some info on a 'dynamic cross tab', I'll give it a go and post with
the results.
"Chris Priede" <priede@.panix.com> wrote in message
news:%235a8RMHIGHA.676@.TK2MSFTNGP10.phx.gbl...
> WCL wrote:
> This is a common problem, known as "crosstab query" (hint: Google that).
> First, you will want to read this:
> http://www.stephenforte.net/owdasbl...
5-15d6d813eeb8
> This is harder to do when the number of ouput columns isn't static. I am
> not aware of any ways to do that without dynamic SQL on SQL Server 2000
> and below. SQL Server 2005 provides PIVOT functionality -- which I have
> yet to play with myself, but believe does exactly that.
>
> --
> Chris Priede
>

Query with 5 tables, grouping by year

How would I group results from 4 tables, each with a year field, so the
results are one row for each year, all based on one clientid from the client
table
For instance, my tables:
Client table: fields ID, ClientName
Tables 1 through 4 all have the same fields, in addition to others: Fields
ID, ClientID (fk to Client table), ValidYear, Data ...
I want my results to be one row per year (we'll be querying only one
client), for example
Year Table1Data Table2Data Table3Data Table4Data
2001 1,000 3,300 15,000 445
2002 1,212 etc.
I've started with:
Select Table1.Data, Table1.ValidYear,
Table2.Data, Table2.ValidYear,
Table3.Data, Table3.ValidYear,
Table4.Data, Table4.ValidYear,
tblClient.ID, tblClient.ClientName
from Table1
inner join tblClient as a on a.ID = Table1.ClientID
inner join tblClient as b on b.ID = Table1.ClientID
inner join tblClient as c on c.ID = Table1.ClientID
inner join tblClient as d on d.ID = Table1.ClientID
and that's as far as I got, as soon as I enter criteria for ValidYear, I get
either too many rows of data or none at all. Not all data tables have data
for all years, by the way.
Thanks very much for your help.Hi,
would you like to check out he usage of DATEPART?
In your SELECT statement, you actually can put DatePart(year,
Table2.ValidYear) instead of Table2.ValidYear.
In the end of your SELECT statement, you just need to put GROUP BY
Table1.Data, Table2.Data etc to get a distinct value.
I hope this is what you are looking for.
Leo Leong
"et" wrote:

> How would I group results from 4 tables, each with a year field, so the
> results are one row for each year, all based on one clientid from the clie
nt
> table
> For instance, my tables:
> Client table: fields ID, ClientName
> Tables 1 through 4 all have the same fields, in addition to others: Field
s
> ID, ClientID (fk to Client table), ValidYear, Data ...
> I want my results to be one row per year (we'll be querying only one
> client), for example
> Year Table1Data Table2Data Table3Data Table4Data
> 2001 1,000 3,300 15,000 445
> 2002 1,212 etc.
> I've started with:
> Select Table1.Data, Table1.ValidYear,
> Table2.Data, Table2.ValidYear,
> Table3.Data, Table3.ValidYear,
> Table4.Data, Table4.ValidYear,
> tblClient.ID, tblClient.ClientName
> from Table1
> inner join tblClient as a on a.ID = Table1.ClientID
> inner join tblClient as b on b.ID = Table1.ClientID
> inner join tblClient as c on c.ID = Table1.ClientID
> inner join tblClient as d on d.ID = Table1.ClientID
> and that's as far as I got, as soon as I enter criteria for ValidYear, I g
et
> either too many rows of data or none at all. Not all data tables have dat
a
> for all years, by the way.
> Thanks very much for your help.
>
>

Query which returns only every 5th Row with a Openquery

Hi @.all,
I'm going crazy here.
I need to modify this Query so that it returns only every 5th Row
because I have to generate Chart with that data and it would take to
long to get 50000 rows from the DB (at the moment it takes about 2
minutes only for retrieving the data).
The problem is that I have to use the Openquery command.
the command I use now is like that:
SELECT *
FROM OpenQuery( INSQL, '
SELECT DateTime, identifier,meter, x1, x2, x3, x4, x5 FROM WideHistory
WHERE DateTime >= "2006-05-29 12:00:00"
AND DateTime <= "2006-05-29 19:00:00" and identifier is not null')
I used to try some hints that I found here but they unfortunaly don't
work.
I tried the following which would be perfect for me but it end up with
full processor load for over 10 minutes. (I think the OLEDB provider
just crashed)
SELECT
Meter, x1, x2, x3, x4, x5 ISNULL(
(SELECT MIN(datetime)
FROM OpenQuery( INSQL, 'SELECT datetime,identifier, meter, x1,
x2, x3, x4, x5 FROM Runtime.WideHistory WHERE DateTime >=
"2006-05-29 17:00:00" AND DateTime <= "2006-05-29 19:00:00" and
identifier is not null') AS S3
WHERE S3.datetime >= S1.datetime
AND ISNULL(
DATEDIFF(
SECOND,
S3.datetime,
(SELECT MIN(datetime)
FROM OpenQuery( INSQL, 'SELECT datetime,identifier,
meter, x1, x2, x3, x4, x5 FROM Runtime.WideHistory WHERE DateTime >=
"2006-05-29 17:00:00" AND DateTime <= "2006-05-29 19:00:00" and
identifier is not null') AS S4
WHERE S4.datetime > S3.datetime)), 10) <= 10),datetime)
AS endtime
FROM OpenQuery( INSQL, 'SELECT datetime,identifier, meter, x1, x2,
x3, x4, x5 FROM Runtime.WideHistory WHERE DateTime >= "2006-05-29
17:00:00" AND DateTime <= "2006-05-29 19:00:00" and identifier is
not null') AS S1
WHERE ISNULL(
DATEDIFF(
SECOND,
(SELECT MAX(datetime)
FROM OpenQuery( INSQL, 'SELECT datetime,identifier, meter,
x1, x2, x3, x4, x5 FROM Runtime.WideHistory WHERE DateTime >=
"2006-05-29 17:00:00" AND DateTime <= "2006-05-29 19:00:00" and
identifier is not null') AS S2
WHERE S2.datetime < S1.datetime),
S1.datetime),
10) <= 10)
Thanks for any help or hint.
Btw: I'm using C# for the frontendHi there,
here is example of the result from the above query:
DateTime identifier meters x1 x2 x3 x4
2006-05-29
17:00:00.000 rollcode901234filmcode901234ref<1678 41213.57421875 233.0 234.0
235.0 236.0
2006-05-29
17:00:00.063 rollcode901234filmcode901234ref<1678 41216.11328125 233.0 234.0
235.0 236.0
2006-05-29
17:00:00.267 rollcode901234filmcode901234ref<1678 41218.14453125 233.0 234.0
235.0 236.0
2006-05-29
17:00:00.467 rollcode901234filmcode901234ref<1678 41218.14453125 234.0 235.0
236.0 237.0
2006-05-29
17:00:00.563 rollcode901234filmcode901234ref<1678 41220.07421875 234.0 235.0
236.0 237.0
2006-05-29
17:00:00.767 rollcode901234filmcode901234ref<1678 41222.20703125 234.0 235.0
236.0 237.0
2006-05-29
17:00:00.967 rollcode901234filmcode901234ref<1678 41222.20703125 235.0 236.0
237.0 238.0
2006-05-29
17:00:01.063 rollcode901234filmcode901234ref<1678 41224.64453125 235.0 236.0
237.0 238.0
2006-05-29
17:00:01.280 rollcode901234filmcode901234ref<1678 41227.18359375 235.0 236.0
237.0 238.0
2006-05-29
17:00:01.467 rollcode901234filmcode901234ref<1678 41227.18359375 236.0 237.0
238.0 239.0
2006-05-29
17:00:01.577 rollcode901234filmcode901234ref<1678 41229.11328125 236.0 237.0
238.0 239.0
2006-05-29
17:00:01.767 rollcode901234filmcode901234ref<1678 41231.75390625 237.0 238.0
239.0 240.0
2006-05-29
17:00:02.063 rollcode901234filmcode901234ref<1678 41233.88671875 237.0 238.0
239.0 240.0
2006-05-29
17:00:02.280 rollcode901234filmcode901234ref<1678 41236.52734375 237.0 238.0
239.0 240.0
2006-05-29
17:00:02.467 rollcode901234filmcode901234ref<1678 41236.52734375 238.0 239.0
240.0 241.0
2006-05-29
17:00:02.563 rollcode901234filmcode901234ref<1678 41238.86328125 238.0 239.0
240.0 241.0
2006-05-29
17:00:02.767 rollcode901234filmcode901234ref<1678 41240.89453125 238.0 239.0
240.0 241.0
2006-05-29
17:00:02.967 rollcode901234filmcode901234ref<1678 41240.89453125 239.0 240.0
241.0 242.0|||Where is the database that you are accessing via open query?
Ideally, you want to do as much of the processing as possible on the source
database. SQL Server can only do so much tuning when it is querying a table
from another database, and it gets much worse when you include more than one
remote table.
You may be much better off building this query on the remote database, or
selecting all the rows and letting C# filter so only every 5th row is kept.
<benwilliams269@.gmail.com> wrote in message
news:1149149017.711607.281380@.i40g2000cwc.googlegroups.com...
> Hi @.all,
> I'm going crazy here.
> I need to modify this Query so that it returns only every 5th Row
> because I have to generate Chart with that data and it would take to
> long to get 50000 rows from the DB (at the moment it takes about 2
> minutes only for retrieving the data).
> The problem is that I have to use the Openquery command.
> the command I use now is like that:
> SELECT *
> FROM OpenQuery( INSQL, '
> SELECT DateTime, identifier,meter, x1, x2, x3, x4, x5 FROM WideHistory
> WHERE DateTime >= "2006-05-29 12:00:00"
> AND DateTime <= "2006-05-29 19:00:00" and identifier is not null')
> I used to try some hints that I found here but they unfortunaly don't
> work.
> I tried the following which would be perfect for me but it end up with
> full processor load for over 10 minutes. (I think the OLEDB provider
> just crashed)
> SELECT
> Meter, x1, x2, x3, x4, x5 ISNULL(
> (SELECT MIN(datetime)
> FROM OpenQuery( INSQL, 'SELECT datetime,identifier, meter, x1,
> x2, x3, x4, x5 FROM Runtime.WideHistory WHERE DateTime >=
> "2006-05-29 17:00:00" AND DateTime <= "2006-05-29 19:00:00" and
> identifier is not null') AS S3
> WHERE S3.datetime >= S1.datetime
> AND ISNULL(
> DATEDIFF(
> SECOND,
> S3.datetime,
> (SELECT MIN(datetime)
> FROM OpenQuery( INSQL, 'SELECT datetime,identifier,
> meter, x1, x2, x3, x4, x5 FROM Runtime.WideHistory WHERE DateTime >=
> "2006-05-29 17:00:00" AND DateTime <= "2006-05-29 19:00:00" and
> identifier is not null') AS S4
> WHERE S4.datetime > S3.datetime)), 10) <= 10),datetime)
> AS endtime
> FROM OpenQuery( INSQL, 'SELECT datetime,identifier, meter, x1, x2,
> x3, x4, x5 FROM Runtime.WideHistory WHERE DateTime >= "2006-05-29
> 17:00:00" AND DateTime <= "2006-05-29 19:00:00" and identifier is
> not null') AS S1
> WHERE ISNULL(
> DATEDIFF(
> SECOND,
> (SELECT MAX(datetime)
> FROM OpenQuery( INSQL, 'SELECT datetime,identifier, meter,
> x1, x2, x3, x4, x5 FROM Runtime.WideHistory WHERE DateTime >=
> "2006-05-29 17:00:00" AND DateTime <= "2006-05-29 19:00:00" and
> identifier is not null') AS S2
> WHERE S2.datetime < S1.datetime),
> S1.datetime),
> 10) <= 10)
>
> Thanks for any help or hint.
> Btw: I'm using C# for the frontend
>|||Here is a rewrite of your query, set to run only against the original
database. See how long it takes to run when run directly against the
source. You may need to tweak some of the syntax, depending on what type of
database you are dealing with. I think the speed will be much, much better
this way.
However, if you want to get every 5th record, I suggest looking into various
paging/ranking techniques to assign row numbers and then retrieve every 5th
row. Try this link to start:
http://www.aspfaq.com/show.asp?id=2427
SELECT
S1.Meter
, S1.x1
, S1.x2
, S1.x3
, S1.x4
, S1.x5
, ISNULL
(
(
SELECT min(S3.datetime)
FROM Runtime.WideHistory AS S3
WHERE S3.DateTime >= "2006-05-29 17:00:00"
AND S3.DateTime <= "2006-05-29 19:00:00"
and S3.identifier is not null
and S3.datetime >= S1.datetime
AND ISNULL
(
DATEDIFF
(
SECOND,
S3.datetime,
(
SELECT MIN(S4.datetime) as datetime
FROM Runtime.WideHistory AS S4
WHERE S4.DateTime >= "2006-05-29 17:00:00"
AND S4.DateTime <= "2006-05-29 19:00:00"
and S4.identifier is not null
and S4.datetime > S3.datetime
)
), 10
) <= 10
),datetime
) AS endtime
FROM Runtime.WideHistory AS S1
WHERE S1.DateTime >= "2006-05-29 17:00:00"
AND S1.DateTime <= "2006-05-29 19:00:00"
and S1.identifier is not null
and ISNULL
(
DATEDIFF
(SECOND,
(
SELECT MAX(S2.datetime) as datetime
FROM Runtime.WideHistory AS S2
WHERE S2.DateTime >= "2006-05-29 17:00:00"
AND S2.DateTime <= "2006-05-29 19:00:00"
and S2.identifier is not null
and S2.datetime < S1.datetime
),S1.datetime
),10
)
<= 10
)
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:OlAWNyZhGHA.412@.TK2MSFTNGP05.phx.gbl...
> Where is the database that you are accessing via open query?
> Ideally, you want to do as much of the processing as possible on the
source
> database. SQL Server can only do so much tuning when it is querying a
table
> from another database, and it gets much worse when you include more than
one
> remote table.
> You may be much better off building this query on the remote database, or
> selecting all the rows and letting C# filter so only every 5th row is
kept.
>
> <benwilliams269@.gmail.com> wrote in message
> news:1149149017.711607.281380@.i40g2000cwc.googlegroups.com...
>

query to xml question

I have a table that contains 3 columns SiteID, Results and date. the table has 4 rows. I want to query the table and end up with 1 row that combines all the field into in the Results Column.

so in table form it looks like

627 test 3/3/7
627 bob 3/3/7
627 tom 3/9/7
627 rob 3/8/7

I want the resulting query to bring back one row:

test,bob,tom,rob

the following query will do 90% of what I want,

SELECT test +','
FROM #temp1
FORXMLPATH('')

BUT I cannot figure out how to provide a column name for the query result. instead I appear to get a guid of XML_F52E2B61-18A1-11d1-B105-00805F49916B

Q - is there a way to name the column, or is there a different way to create this query result without using XML?

I am us

Jim:

The best way is to make your select statement into a "derived table" or a correlated subquery; For example:

create table #temp1
( SiteID integer,
Results varchar(10),
date datetime
)
insert into #temp1 values (627, 'billy joe', '3/3/7')
insert into #temp1 values (627, 'bob', '3/3/7')
insert into #temp1 values (627, 'tom', '3/9/7')
insert into #temp1 values (627, 'rob', '3/8/7')

select distinct
siteId,
replace(replace(
( select replace (x.results, ' ', '~') as [data()]
from #temp1 x
where x.siteId = x.siteId
order by date
for xml path ('')
), ' ', ','), '~', ' ') as dataLabel
from #temp1 a

-- siteId dataLabel
-- --
-- 627 billy joe,bob,rob,tom

go

drop table #temp1
go

|||

selectcast((SELECT test +','FROM #temp1 FORXMLPATH(''))asvarchar(max))as YourName

|||I think I like Konstantin's better.|||

Thanks to both of you for the quick reply, they both work, but think I will use Konstantin's

|||

Actually I now have a different problem:

I am using

select siteid, Cast((SELECT Anomalies+ ',' FROM #temp1 FOR XML PATH('')) as varchar(max) ) as Anomaly from #temp1

this does work, sort of.... But it creates the result for all records in the table. I need it to create a seperate record for each siteid, otherwise all the resulting data is the same for all siteids?

any ideas?

|||Just add filter to subquery:
select siteid, Cast((SELECT Anomalies+ ',' FROM #temp1 where siteid=t.siteid FOR XML PATH('')) as varchar(max) ) as Anomaly from #temp1 t
sql

Query to Retrieve Latest Row from each group !!

Hi SQL Query Expert,
My table looks like this:
CONTRACT_PK PARENT_PK CONTRACTOR_NAME CREATED_DATE
1 <NULL> ABC Company 4/7/2005
11:10:10 a.m.
2 1 XYZ Company
4/8/2005 10:10:12 a.m.
3 1 AAA Company
4/8/2005 12:10:00 p.m.
4 <NULL> BBB Company 4/8/2005
1:00:00 p.m.
5 4 CCC Company
4/8/2005 2:00:00 p.m.
6 <NULL> DDD Company 4/8/2005
3:00:00 p.m.
Basically record 2 and 3 are childs of record 1. Record 5 is child of record
4. Record 6 is a parent.
Could you please give me an example on how to retrieve rows with CONTRACT_PK
equals to 3, 5 and 6 from the above table?
My goal is to retrieve the latest child row if the parent has children. If
the parent doesn't have child(s), then it retrieves parent row. Here 3 and
5
are all latest child of parent 1 and 4. Parent 6 doesn't have child, so, i
t
should get retrieved too.
The table could have 1000 rows and they all fall in the same pattern for the
records retrieval.
Thank you so much!!!
-adamTry This
Select IsNull(C.CONTRACT_PK, P.CONTRACT_PK) ContractPK,
IsNull(C.PARENT_PK, P.PARENT_PK) ParentPK,
IsNull(C.CONTRACTOR_NAME, P.CONTRACTOR_NAME) Contractor,
IsNull(C.CREATED_DATE, P.CREATED_DATE) CreatedDate
From Table P
Left Join Table C
On C.Parent_PK = P.Contract_PK
And C.Created_Date = (Select Max(Created_Date)
From Table
Where Parent_PK =
C.Parent_PK)
"adam" wrote:

> Hi SQL Query Expert,
> My table looks like this:
> CONTRACT_PK PARENT_PK CONTRACTOR_NAME CREATED_DATE
> 1 <NULL> ABC Company 4/7/200
5
> 11:10:10 a.m.
> 2 1 XYZ Company
> 4/8/2005 10:10:12 a.m.
> 3 1 AAA Company
> 4/8/2005 12:10:00 p.m.
> 4 <NULL> BBB Company 4/8/20
05
> 1:00:00 p.m.
> 5 4 CCC Company
> 4/8/2005 2:00:00 p.m.
> 6 <NULL> DDD Company 4/8/200
5
> 3:00:00 p.m.
> Basically record 2 and 3 are childs of record 1. Record 5 is child of reco
rd
> 4. Record 6 is a parent.
> Could you please give me an example on how to retrieve rows with CONTRACT_
PK
> equals to 3, 5 and 6 from the above table?
> My goal is to retrieve the latest child row if the parent has children. I
f
> the parent doesn't have child(s), then it retrieves parent row. Here 3 an
d 5
> are all latest child of parent 1 and 4. Parent 6 doesn't have child, so,
it
> should get retrieved too.
> The table could have 1000 rows and they all fall in the same pattern for t
he
> records retrieval.
> Thank you so much!!!
> -adam
>|||adam,
Do not post the same problem twice, it does not help. Check your first threa
d.
AMB
"adam" wrote:

> Hi SQL Query Expert,
> My table looks like this:
> CONTRACT_PK PARENT_PK CONTRACTOR_NAME CREATED_DATE
> 1 <NULL> ABC Company 4/7/200
5
> 11:10:10 a.m.
> 2 1 XYZ Company
> 4/8/2005 10:10:12 a.m.
> 3 1 AAA Company
> 4/8/2005 12:10:00 p.m.
> 4 <NULL> BBB Company 4/8/20
05
> 1:00:00 p.m.
> 5 4 CCC Company
> 4/8/2005 2:00:00 p.m.
> 6 <NULL> DDD Company 4/8/200
5
> 3:00:00 p.m.
> Basically record 2 and 3 are childs of record 1. Record 5 is child of reco
rd
> 4. Record 6 is a parent.
> Could you please give me an example on how to retrieve rows with CONTRACT_
PK
> equals to 3, 5 and 6 from the above table?
> My goal is to retrieve the latest child row if the parent has children. I
f
> the parent doesn't have child(s), then it retrieves parent row. Here 3 an
d 5
> are all latest child of parent 1 and 4. Parent 6 doesn't have child, so,
it
> should get retrieved too.
> The table could have 1000 rows and they all fall in the same pattern for t
he
> records retrieval.
> Thank you so much!!!
> -adam
>

Query to combine several "records/rows" into one "record/row"?

Im new to SQL, Ive been getting by building lists from our student database by building or teacher (etc.). These have been all records that exist on only one row, so its been easy (so far).
Heres what Im trying to do and I dont even know what to call it, so I'm not even sure what to search for...

Ive got a MS SQL 6.5 database with the following:
ACTIVE students: each student has ID_NUM[8 digits], NAME, GRADE, SCHOOL with one rowof 4 data items per student.

SCHEDULE of courses with (student) ID_NUM[8 digits], SEMESTER[S1 or S2], HOUR[1-7], COURSE_NAME, ROOM_NUM with 14 records (rows) with these 5 items, in this SCHEDULE database for each student.

My mission is to combine then in to one row using the student ID_NUM as the key. (This is to help me with several things, spreadsheets/database for others to easily use, export to simple databases for teacher handhelds.).

Id like one row of the 75 items combined, resulting in 32 items (ACTIVE 4 items + 14 * 2 SCHEDULE items [COURSE_NAME, ROOM_NUM]) since I want stuff plugged into the correct field, for each student. I'd refer to this as COMBINEDRECORD and Id turn the field names into the following:
ID_NUM[8 digits], NAME, GRADE, SCHOOL, S1HOUR1_NAME, S1HOUR1_ROOM_NUM, S1HOUR2_NAME, S1HOUR2_ROOM_NUM, S1HOUR3_NAME, S1HOUR3_ROOM_NUM, S1HOUR4_NAME, S1HOUR4_ROOM_NUM, S1HOUR5_NAME, S1HOUR5_ROOM_NUM, S1HOUR6_NAME, S1HOUR6_ROOM_NUM, S1HOUR7_NAME, S1HOUR7_ROOM_NUM, S2HOUR1_NAME, S2HOUR1_ROOM_NUM, S2HOUR2_NAME, S2HOUR2_ROOM_NUM, S2HOUR3_NAME, S2HOUR3_ROOM_NUM, S2HOUR4_NAME, S2HOUR4_ROOM_NUM, S2HOUR5_NAME, S2HOUR5_ROOM_NUM, S2HOUR6_NAME, S2HOUR6_ROOM_NUM, S2HOUR7_NAME, S2HOUR7_ROOM_NUM

I dont care if there are any blanks I just want to get the data if
SEMESTER='S2', HOUR='5' & COURSE_NAME='Basketweaving' & ROOM_NUM='Pool'
to end up being in the right spot (S2 and Hour 5) in the new COMBINEDRECORD row with
S2HOUR5_NAME='Basketweaving' & S2HOUR5_ROOM_NUM='Pool' for the correct student ID_NUM. Of course with the correct ACTIVE student info into the same "row"

Does that make sense? It might not be the best way, but itll make the data more accessible to everyone and some programs we already use with our old student system. Obviously theres more data than that but I think this is enough to explain my issue and give me enough to work with

Any help, directions to a webpage or book with the correct terms to look up would be very helpful.

Thank you for any help or direction you can give,
GarySounds like a join. Are you trying to creat a new table or just do a report?|||Or a view with a join if you want to leave the existing tables alone.|||Originally posted by barneyrubble318
Or a view with a join if you want to leave the existing tables alone.

I'll probably be doing two (similar) things:

1) An SQL query that just puts my COMBINEDRECORD table into an Excel Spreadsheet. (Why Excel? Everyone here knows how to merge from it, so they can then manipulate it how they want.)

2) An SQL query from Desktop2MobileDB which will convert the COMBINEDRECORD table into a Palm OS (MobileDB) database so principals and teachers can have more data on hand. (They can see where the kid in the hall is really supposed to be...)

I pretty much do the above two things with data now, the problem is the multi-line data from the SCHEDULE/

If I have to create a new table and then access it from there, I guess I can do that. I might not be able to automate it as easily though...

Thanks,
Gary