db time conversion: rows

Showing posts with label rows. Show all posts

Friday, March 30, 2012

Query about Ansi-Syntax

I need to write a stored procedure where I need to joing 3 tables A, B and C having approximately 200K, 500K, 800K rows respectively.

Query:
1) If I use ansi-syntax (inner join) as against non-ansi syntax (A.col1 = B.col1), I get a better performance.
Any idea why?

2) If I write a query (shown below), it tries to join table A and B returning large number of rows.

Select A.Col1, A.Col2
from A, B
where A.Col3 = 'xyz'

Why does it try to join the table B with A though there is no join specified.As far as the second query is concerned this is the same as writing

Select A.Col1, A.Col2
from A join B
where A.Col3 = 'xyz'

Basically, don't put the table in the from clause if you are not going to use it because it makes the query REALLY inefficient. This is a cross join and will probably give you heaps of extra records.|||actually, ejustuss, your query generates an error

if we want a cross join, we have to say CROSS JOIN, not just JOIN

and yes, there are many situations where we want to join one table to another, and yet select columns only from one|||This old school syntax can cause a lot of heartburn when you get into multiple outer joins. I do not think MS SQL 2K supports *=

Select A.Col1, A.Col2
from A, B
where A.Col3 = 'xyz'|||I do not think MS SQL 2K supports *=yes, it (still) does
create table Oldschool1
( id tinyint not null primary key identity
, foo varchar(9)
)
insert into Oldschool1 (foo) values ('Curly')
insert into Oldschool1 (foo) values ('Larry')
insert into Oldschool1 (foo) values ('Moe')
insert into Oldschool1 (foo) values ('Shemp')
insert into Oldschool1 (foo) values ('Joe')
insert into Oldschool1 (foo) values ('Curly Joe')

create table Oldschool2
( id tinyint not null primary key identity
, bar varchar(9)
)
insert into Oldschool2 (bar) values ('Curly')
insert into Oldschool2 (bar) values ('Larry')
insert into Oldschool2 (bar) values ('Moe')

select t1.id,t1.foo,t2.id,t2,bar
from Oldschool1 as t1
, Oldschool2 as t2
where t1.foo *= t2.bar
order by 1 :)|||As far as your first question,

The difference between ANSI (inner join) syntax and non-ANSI is that the optimizer will not try to derive joins that are ANSI compliant. That's because you've explicitly defined the joins. The optimizer will have to derive non-ANSI compliant joins. In many cases the query plans will be the same. However there's a greater risk the optimizer won't use the optimal plan if it has to derive the joins.

There's probably some people here that can explain this a lot better than me. Hope this helps.|||As far as your second question,

The results you're getting from that query are called a cartesian product. Since a join hasn't been specified it will match each record from on table to each record from the other. Thus if you have two tables with 100 rows each 10,000 rows will be returned.|||What I was trying to say was that in example 2 of the original post the join was implicit. Sorry about the error in syntax. Where would you want a query of the form

Select A.Col1, A.Col2
from A cross join B
where A.Col3 = 'xyz'

?|||where would you want a cross join?

1. to generate test data, e.g. a large range of dates from integers

2. with a left outer join to find missing many-to-many rows

3. to join a one-row table with application constants such as today's interest rate|||I was actually refering to the query as originally put with no reference to any column in the second table AT ALL. The generation of test data seems like a legitimate use though the other 2 surely require a reference to a column from the 2nd table in the the query somewhere even if only in the join condition.

Its just that I have seen queries with additional tables in the from clause which were unnecessary and unused and actually increased the amount of time taken to run the query substanitially.|||oh, that question

okay, well, the join would be used to ensure existence of a related row, even if you didn't need to actually return any data from the second table

for example, assume car owners are in one table, and parking tickets are in another, you might say "give me the names of every owner who had a parking ticket" and not return any row from the parking ticket table at all|||Well in this case would you not say something along the lines of

select car.owner
from car,ticket
where car.owner = ticket.reciever

?

my point being that you are actually referencing a column from the ticket table in the where clause (or join condition depending on how you write the query) - which is not what is happening in the original example.|||...which is not what is happening in the original example.that's right, it's a cross join

query a sorted view by rows

how can I query a sorted view by rows - show rows 50 to 75
or show last 25 rows ?
thanksSam wrote:
> how can I query a sorted view by rows - show rows 50 to 75
> or show last 25 rows ?
> thanks
Views are NOT sorted. If you are using unsupported and unreliable
tricks like TOP 100 PERCENT ... ORDER BY then you'll find that they
break in SQL Server 2005 or maybe even in 2000 under some future fix or
SP.
The solution is to sort when you query the view:
SELECT A1.au_id, A1.au_lname, A1.au_fname
FROM pubs.dbo.authors AS A1
WHERE
(SELECT COUNT(*)
FROM pubs.dbo.authors AS A2
WHERE A1.au_id >= A2.au_id)
BETWEEN 10 AND 15
ORDER BY A1.au_id ;
If you want some more sophisticated paging examples then take a look
at:
http://www.aspfaq.com/show.asp?id=2120
Hope this helps.
David Portas
SQL Server MVP
--|||First of all, there's no need to create an ordered view. Sort your rows when
querying the view, or on the client.
Look up the TOP keyword in Books Online.
http://msdn.microsoft.com/library/d...r />
_13ec.asp
Also lookup RANK(), ROWNUMBER() and DENSE_RANK() in Books Online 2005, if
you're using SQL Server 2005.
http://msdn2.microsoft.com/en-us/library/ms176102.aspx
http://msdn2.microsoft.com/en-us/library/ms186734.aspx
http://msdn2.microsoft.com/en-us/library/ms173825.aspx
In SQL 2000 these functions do not exist, so you could find a paging method
here:
http://www.aspfaq.com/show.asp?id=2427 (and in the rest of the really nice
neighbourhood of aspfaq.com).
ML

Wednesday, March 28, 2012

Query ... Distinct rows

I have a table as follows
ORDER_ID CODE STATUS
1000 XA3 5
1000 XA1 4
1000 XA7 5
1001 X35 5
1001 XA3 5
I want to run a query that will return the distinct ORDER_ID that is Status
= 5. If any records have Status <> 5, I dont want that ORDER_ID returned.
For example above, the result set will be 1001 only (as 1000 has one record
with Status of 4).
I have tried using 'HAVING MIN(Status) = 5 AND MAX(Status = 5) but it doesnt
appear to work :-(
Thanks in advance!
Wez
On Fri, 17 Jun 2005 03:30:02 -0700, Wez wrote:

> I have a table as follows
>ORDER_ID CODE STATUS
>1000 XA3 5
>1000 XA1 4
>1000 XA7 5
>1001 X35 5
>1001 XA3 5
>I want to run a query that will return the distinct ORDER_ID that is Status
>= 5. If any records have Status <> 5, I dont want that ORDER_ID returned.
>For example above, the result set will be 1001 only (as 1000 has one record
>with Status of 4).
>I have tried using 'HAVING MIN(Status) = 5 AND MAX(Status = 5) but it doesnt
>appear to work :-(
>Thanks in advance!
>Wez
Hi Wez,
This one should work, actually:
SELECT Order_ID
FROM YourTable
GROUP BY Order_ID
HAVING MIN(Status) = 5 AND MAX(Status) = 5
What eexactly does "doesn't appear to work" mean? Error messages? Wrong
results? Blue smoke in the server room? It's hard to help you without
knowing what's happening!
BTW, here's another query that should also work:
SELECT DISTINCT t1.Order_ID
FROM YourTable AS t1
WHERE NOT EXISTS (SELECT *
FROM YourTable AS t2
WHERE t2.Order_ID = t1.Order_ID
AND t2.Status <> 5)
/* Adding the line below might improve performance
AND t1.Status = 5
*/
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Hi Hugo,
when i say didnt work I mean I was getting wrong results i.e. orders
appearing in the result set that had lines not yet equal to status '5'.
However your alternative method has worked well :-)
Thanks,
Wez

Query (IMPORTANT)

Hi,
I would like to query and view returned rows using MSDE.
Can I accomplish this? How about with the bcp.exe utility? How about with
MS-ACCESS?
Thanks,
Yama
I think we need a bit more detail here. There are a ton of examples of
writing queries of all kinds online, in books and magazines. Why won't these
work?
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
"Yama" <ykamyar@.stbernard.com> wrote in message
news:OzN6mlpmEHA.2500@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I would like to query and view returned rows using MSDE.
> Can I accomplish this? How about with the bcp.exe utility? How about with
> MS-ACCESS?
> Thanks,
> Yama
>

Query

Hi All

Thnks

I have a Datatable in which the rows have a unique id no appended to it

Example ColumnName : Manufacturer

Rows: Item1{6319c77a-b2c6-4b26-9528-99685de50d41}

Item2{e919c77a-b2c6-4b26-9528-99685de50d41}

Item3{ab19c77a-b2c6-4b26-9528-99685de50d41}

I need to insert into database only the unique id of the rows

6319c77a-b2c6-4b26-9528-99685de50d41 ie the num within {} brakets

How do I do this ?

Cheers

use this sample,

Code Snippet

Create Table #data (

[Value] Varchar(100)

);

Insert Into #data Values('Item1{6319c77a-b2c6-4b26-9528-99685de50d41}');

Insert Into #data Values('Item2{e919c77a-b2c6-4b26-9528-99685de50d41}');

Insert Into #data Values('Item3{ab19c77a-b2c6-4b26-9528-99685de50d41}');

Create Table #Targetdata (

[Value] UniqueIdentifier

);

insert into #Targetdata

Select Substring(Value,Charindex('{',Value)+1,CharIndex('}',Value)-1) From #Data

|||

Perhaps this will give you the direction you seek.

Code Snippet

SET NOCOUNT ON

DECLARE @.Manufacturer table
( [Rows] varchar(100) )

INSERT INTO @.Manufacturer VALUES ( 'Item1{6319c77a-b2c6-4b26-9528-99685de50d41}' )
INSERT INTO @.Manufacturer VALUES ( 'Item2{e919c77a-b2c6-4b26-9528-99685de50d41}' )
INSERT INTO @.Manufacturer VALUES ( 'Item3{ab19c77a-b2c6-4b26-9528-99685de50d41}' )

SELECT [ID] = substring( [Rows], 7, 36 )
FROM @.Manufacturer

ID
6319c77a-b2c6-4b26-9528-99685de50d41
e919c77a-b2c6-4b26-9528-99685de50d41
ab19c77a-b2c6-4b26-9528-99685de50d41

|||

Thanks a lot this works just fine .

|||

Thanks a lot this works just fine .

Query

Hi!

I need to create a query(stored procedure) that will select all rows from the second table and only the rows from table 1 where the maxValues for Col1 and Col2 do not exist in table 2 so that the result would look something like table 3

table 1

id	Col1	Col2	maxValues
1	aa	bb	10
2	cc	dd	10
3	ee	ff	10
4	gg	hh	10
5	jj	kk	10

table 2

id	Col1	Col2	CurrentValue
1	cc	dd	7
2	gg	hh	3
3	jj	kk	5

table 3

id	Col1	Col2	AvailableEntries
1	aa	bb	10
2	cc	dd	7
3	ee	ff	10
4	gg	hh	3
5	jj	kk	5

Thanks for your help.

If ALL values in table2 are also in table1, and maxValue always >= currentValue, then this should work:
select col1, col2, MIN(theValues)
from (
select col1, col2, maxValues as 'theValues'
from table1
UNION
select col1, col2, currentValue as 'theValues'
from table2
) as tempTable
group by col1, col2
|||It is perfect, thank you Smile [:)]

query

damn touchpads......
I have a query:
SELECT id, name, rank, serial_number from status
this will return many rows...if all values are null for a single row, i need
to be able to assign a unique "id" for that row (PK constraint on another
table) is there a way to do this so that for each row that has NULL values
across all columns. This way I can populate with an incrementing or otherwis
e
unique value (using d1, d2, d3... here)
id name rank serial_number
--
d1 null null null
d2 null null null
34r bill 0-6 4420
d3 null null null
d4 null null null
d5....
TIA!You can try using the following:
NOTE: This recreates the status table (truncate + re-populate)
-- BEGIN SCRIPT
create table status
(id varchar(10),
[name] varchar(10),
rank varchar(10),
serial_number varchar(10))
insert into status
values (null,null,null,null)
insert into status
values ('34r','bill','0-6','4420')
insert into status
values (null,null,null,null)
insert into status
values (null,null,null,null)
insert into status
values (null,null,null,null)
create table #status
(RowID int identity (1,1),
id varchar(10),
[name] varchar(10),
rank varchar(10),
serial_number varchar(10))
create table #status2
(RowID int identity (1,1),
id varchar(10),
[name] varchar(10),
rank varchar(10),
serial_number varchar(10))
insert into #status
select * from status
insert into #status2
select * from status
update #status
set id = 'd'+convert(varchar(10), s.RowID)
from #status s, #status2 s2
where s.RowID = s2.RowID
and s.id is null
truncate table status
insert into status
SELECT id, name, rank, serial_number from #status
select * from status
drop table #status
drop table #status2
Hope it helps
"JMNUSS" wrote:

> damn touchpads......
> I have a query:
> SELECT id, name, rank, serial_number from status
> this will return many rows...if all values are null for a single row, i ne
ed
> to be able to assign a unique "id" for that row (PK constraint on another
> table) is there a way to do this so that for each row that has NULL values
> across all columns. This way I can populate with an incrementing or otherw
ise
> unique value (using d1, d2, d3... here)
> id name rank serial_number
> --
> d1 null null null
> d2 null null null
> 34r bill 0-6 4420
> d3 null null null
> d4 null null null
> d5....
> TIA!
>

Monday, March 26, 2012

query

hi
i have a doubt in sqlserver.
how to delete the duplicate rows with a query in sqlserver
thank youOne choice is to use Cursor.
But remember that Cursors are very expensive to SQL Server.

Else,

You may try to use the UNION (not UNION ALL), with the same table. Put all the returned data into a temporary Table, then you delete your data from original table and insert the values from the temporary table into it.|||First of all, what is structure of your table?
If your table does not have a primary key - too bad... Use Diogo's advice and then check a normalization rules.
If it does - you can try next method:

drop table test
go
create table test (id int primary key,
code varchar(10))
go
insert test values(1,'code1')
insert test values(2,'code2')
insert test values(3,'code1')
insert test values(4,'code3')
insert test values(5,'code4')
insert test values(6,'code5')
insert test values(7,'code6')
insert test values(8,'code3')

select *
--delete
from test
where id in
(select min(Id) from test group by code having count(*)>1)

May be it needs to run last query couple times....

Friday, March 23, 2012

query

Hi,
There is a table which contains the rows and I would like to create a query
that can show the below result. Can a query do that?
Thanks
Table
--
Owner Cat Name
1 1 Kitap
1 2 Defter
1 3 Kalem
1 2 Dergi
2 1 Ceket
2 1 Gömlek
2 2 Kravat
2 3 Çorap
2 3 Pantolon
---
The Query result
Owner Cat Name
1 1 Kitap
1 2 Defter
1 3 Kalem
2 1 Ceket
2 2 Kravat
2 3 Çorap
---A query can probably do this, but you will have to tell us
how you decide which Name to return when there is more
than one row with the same Owner and Cat values. Here,
it looks like you are choosing the one that appears first in
the list of all table rows, but in order to do this with a query,
you need to define the row you want in terms of the column
values, not a particular output order that you can't rely on.
Steve Kass
Drew University
tolgay wrote:
>Hi,
>There is a table which contains the rows and I would like to create a query
>that can show the below result. Can a query do that?
>Thanks
>
>Table
>--
> Owner Cat Name
> 1 1 Kitap
> 1 2 Defter
> 1 3 Kalem
> 1 2 Dergi
> 2 1 Ceket
> 2 1 Gé?£lek
> 2 2 Kravat
> 2 3 ã'rap
> 2 3 Pantolon
>---
>The Query result
> Owner Cat Name
> 1 1 Kitap
> 1 2 Defter
> 1 3 Kalem
> 2 1 Ceket
> 2 2 Kravat
> 2 3 ã'rap
>---
>
>|||Actually it doesn't matter which row comes with the query. But the main
point is the query must get one of the rows.
Thanks
"Steve Kass" <skass@.drew.edu> wrote in message
news:u6PVjiFcGHA.3380@.TK2MSFTNGP04.phx.gbl...
> A query can probably do this, but you will have to tell us
> how you decide which Name to return when there is more
> than one row with the same Owner and Cat values. Here,
> it looks like you are choosing the one that appears first in
> the list of all table rows, but in order to do this with a query,
> you need to define the row you want in terms of the column
> values, not a particular output order that you can't rely on.
> Steve Kass
> Drew University
> tolgay wrote:
> >Hi,
> >
> >There is a table which contains the rows and I would like to create a
query
> >that can show the below result. Can a query do that?
> >Thanks
> >
> >
> >Table
> >--
> > Owner Cat Name
> > 1 1 Kitap
> > 1 2 Defter
> > 1 3 Kalem
> > 1 2 Dergi
> > 2 1 Ceket
> > 2 1 G?lek
> > 2 2 Kravat
> > 2 3 ?rap
> > 2 3 Pantolon
> >
> >---
> >The Query result
> >
> > Owner Cat Name
> > 1 1 Kitap
> > 1 2 Defter
> > 1 3 Kalem
> > 2 1 Ceket
> > 2 2 Kravat
> > 2 3 ?rap
> >
> >---
> >
> >
> >
> >|||in that case...
Select owner
, cat
, max(name)
from CatTable
group by owner
, cat
However, this table structure needs keys to prevent duplicates. Two rows as
shown here should never exist. You probably want to change your database
structures before doing anything else. You can guarantee that the name you
pull back will be wrong half the time.
"tolgay" <tgul@.tgul.com> wrote in message
news:%23DJTKpFcGHA.1276@.TK2MSFTNGP03.phx.gbl...
> Actually it doesn't matter which row comes with the query. But the main
> point is the query must get one of the rows.
> Thanks
> "Steve Kass" <skass@.drew.edu> wrote in message
> news:u6PVjiFcGHA.3380@.TK2MSFTNGP04.phx.gbl...
> > A query can probably do this, but you will have to tell us
> > how you decide which Name to return when there is more
> > than one row with the same Owner and Cat values. Here,
> > it looks like you are choosing the one that appears first in
> > the list of all table rows, but in order to do this with a query,
> > you need to define the row you want in terms of the column
> > values, not a particular output order that you can't rely on.
> >
> > Steve Kass
> > Drew University
> >
> > tolgay wrote:
> >
> > >Hi,
> > >
> > >There is a table which contains the rows and I would like to create a
> query
> > >that can show the below result. Can a query do that?
> > >Thanks
> > >
> > >
> > >Table
> > >--
> > > Owner Cat Name
> > > 1 1 Kitap
> > > 1 2 Defter
> > > 1 3 Kalem
> > > 1 2 Dergi
> > > 2 1 Ceket
> > > 2 1 G?lek
> > > 2 2 Kravat
> > > 2 3 ?rap
> > > 2 3 Pantolon
> > >
> > >---
> > >The Query result
> > >
> > > Owner Cat Name
> > > 1 1 Kitap
> > > 1 2 Defter
> > > 1 3 Kalem
> > > 2 1 Ceket
> > > 2 2 Kravat
> > > 2 3 ?rap
> > >
> > >---
> > >
> > >
> > >
> > >
>|||Jim has offered a solution, and I'll just add that while
it doesn't matter to you, you still have to tell SQL Server
what to do. There's no ANY_OLD_ONE aggregate
in SQL. :)
SK
tolgay wrote:
>Actually it doesn't matter which row comes with the query. But the main
>point is the query must get one of the rows.
>Thanks
>"Steve Kass" <skass@.drew.edu> wrote in message
>news:u6PVjiFcGHA.3380@.TK2MSFTNGP04.phx.gbl...
>
>>A query can probably do this, but you will have to tell us
>>how you decide which Name to return when there is more
>>than one row with the same Owner and Cat values. Here,
>>it looks like you are choosing the one that appears first in
>>the list of all table rows, but in order to do this with a query,
>>you need to define the row you want in terms of the column
>>values, not a particular output order that you can't rely on.
>>Steve Kass
>>Drew University
>>tolgay wrote:
>>
>>Hi,
>>There is a table which contains the rows and I would like to create a
>>
>query
>
>>that can show the below result. Can a query do that?
>>Thanks
>>
>>Table
>>--
>> Owner Cat Name
>> 1 1 Kitap
>> 1 2 Defter
>> 1 3 Kalem
>> 1 2 Dergi
>> 2 1 Ceket
>> 2 1 G?lek
>> 2 2 Kravat
>> 2 3 ?rap
>> 2 3 Pantolon
>>---
>>The Query result
>> Owner Cat Name
>> 1 1 Kitap
>> 1 2 Defter
>> 1 3 Kalem
>> 2 1 Ceket
>> 2 2 Kravat
>> 2 3 ?rap
>>---
>>
>>
>>
>
>|||thanks a lot you saved my day :)
"tolgay" <tgul@.tgul.com> wrote in message
news:un$oOaFcGHA.3856@.TK2MSFTNGP03.phx.gbl...
> Hi,
> There is a table which contains the rows and I would like to create a
> query
> that can show the below result. Can a query do that?
> Thanks
>
> Table
> --
> Owner Cat Name
> 1 1 Kitap
> 1 2 Defter
> 1 3 Kalem
> 1 2 Dergi
> 2 1 Ceket
> 2 1 Gömlek
> 2 2 Kravat
> 2 3 Çorap
> 2 3 Pantolon
> ---
> The Query result
> Owner Cat Name
> 1 1 Kitap
> 1 2 Defter
> 1 3 Kalem
> 2 1 Ceket
> 2 2 Kravat
> 2 3 Çorap
> ---
>

query

SK
tolgay wrote:

>Actually it doesn't matter which row comes with the query. But the main
>point is the query must get one of the rows.
>Thanks
>"Steve Kass" <skass@.drew.edu> wrote in message
>news:u6PVjiFcGHA.3380@.TK2MSFTNGP04.phx.gbl...
>
>query
>
>
>|||thanks a lot you saved my day

"tolgay" <tgul@.tgul.com> wrote in message
news:un$oOaFcGHA.3856@.TK2MSFTNGP03.phx.gbl...
> Hi,
> There is a table which contains the rows and I would like to create a
> query
> that can show the below result. Can a query do that?
> Thanks
>
> Table
> --
> Owner Cat Name
> 1 1 Kitap
> 1 2 Defter
> 1 3 Kalem
> 1 2 Dergi
> 2 1 Ceket
> 2 1 Gmlek
> 2 2 Kravat
> 2 3 orap
> 2 3 Pantolon
> ---
> The Query result
> Owner Cat Name
> 1 1 Kitap
> 1 2 Defter
> 1 3 Kalem
> 2 1 Ceket
> 2 2 Kravat
> 2 3 orap
> ---
>

querry: rows must be displayed as columns

hi all,

currently i have a table:
studentId course score year
6.1 math 9 2007
6.1 french 5 2007
6.1 science 8 2007
6.1 math 6 2006
6.1 french 5 2006
6.1 science 5 2006
6.2 math 4 2007
6.2 french 6 2007

is it possible to have a query that gives this as result:
student year mathscore frenchscore sciencescore
6.1 2007 9 5 8
6.1 2006 6 5 5
6.2 2007 4 6 NULL

use PIVOT operator (if you SQL Server 2005).

Code Snippet

Create Table #score (

[studentId] Varchar(100) ,

[course] Varchar(100) ,

[score] int ,

[year] int

);

Insert Into #score Values('6.1','math','9','2007');

Insert Into #score Values('6.1','french','5','2007');

Insert Into #score Values('6.1','science','8','2007');

Insert Into #score Values('6.1','math','6','2006');

Insert Into #score Values('6.1','french','5','2006');

Insert Into #score Values('6.1','science','5','2006');

Insert Into #score Values('6.2','math','4','2007');

Insert Into #score Values('6.2','french','6','2007');

Select

[studentId]

, [year]

, [math]

, [french]

, [science]

From

#score S

Pivot(

Max([score]) For

[course] in ([math], [french], [science])

) as Pvt

|||

You could ue PIVOT function:

Code Snippet

create table #Scores

(

studentid int,

course varchar(10),

score int,

year int

)

insert into #Scores values(6.1,'math', 9, 2007)

insert into #Scores values(6.1,'french', 5, 2007)

insert into #Scores values(6.1,'science', 8, 2007)

insert into #Scores values(6.1,'math', 6, 2006)

insert into #Scores values(6.1,'french', 5, 2006)

insert into #Scores values(6.1,'science', 5, 2006)

insert into #Scores values(6.2,'math', 4, 2007)

insert into #Scores values(6.2,'french', 6, 2007)

select studentid, year, [math],[french], [science]

FROM #Scores PIVOT( MAX(SCORE) FOR course IN ([math],[french], [science]) ) as pvt

|||hi try this..

DECLARE @.Subjects TABLE (
studentId numeric(10,1)
, course varchar(20)
, score numeric
, [year] int
)

INSERT
INTO @.Subjects
SELECT 6.1, 'math', 9, 2007 UNION ALL
SELECT 6.1, 'french', 5, 2007 UNION ALL
SELECT 6.1, 'science', 8, 2007 UNION ALL
SELECT 6.1, 'math', 6, 2006 UNION ALL
SELECT 6.1, 'french', 5, 2006 UNION ALL
SELECT 6.1, 'science', 5, 2006 UNION ALL
SELECT 6.2, 'math', 4, 2007 UNION ALL
SELECT 6.2, 'french', 6, 2007

SELECT DISTINCT
s.studentId
, s.[year]
, m.score as mathscore
, f.score as frenchscore
, c.score as sciencescore
FROM @.Subjects s LEFT OUTER JOIN
(
SELECT *
FROM @.Subjects
WHERE course = 'math'
) m ON s.studentId = m.studentId
AND s.[year] = m.[year] LEFT OUTER JOIN
(
SELECT *
FROM @.Subjects
WHERE course = 'french'
) f ON s.studentId = f.studentId
AND s.[year] = f.[year] LEFT OUTER JOIN
(
SELECT *
FROM @.Subjects
WHERE course = 'science'
) c ON s.studentId = c.studentId
AND s.[year] = c.[year]|||

If you use SQL Server 2000 (This query will work with 2005 also)..

Code Snippet

Create Table #score (

[studentId] Varchar(100) ,

[course] Varchar(100) ,

[score] int ,

[year] int

);

Insert Into #score Values('6.1','math','9','2007');

Insert Into #score Values('6.1','french','5','2007');

Insert Into #score Values('6.1','science','8','2007');

Insert Into #score Values('6.1','math','6','2006');

Insert Into #score Values('6.1','french','5','2006');

Insert Into #score Values('6.1','science','5','2006');

Insert Into #score Values('6.2','math','4','2007');

Insert Into #score Values('6.2','french','6','2007');

Select

studentId

, year

, Sum(Case When Course = 'math' Then score End) as [math]

, Sum(Case When Course = 'french' Then score End) as [french]

, Sum(Case When Course = 'science' Then score End) as [science]

From

#score [main]

Group By

studentId,year

|||nice post mani,|||hi,
in SQL Server 2000 you can try this query :

Code Snippet

select studentID, year
, (select score from #tbl where course = 'math' and studentID = x.studentid and year = x.year) as matchscore
, (select score from #tbl where course = 'science' and studentID = x.studentid and year = x.year) as sciencescore
, (select score from #tbl where course = 'french' and studentID = x.studentid and year = x.year) as frenchscore
from #tbl x

- clintz

Querring only non NULL row/column

Hi,
I want to query few rows from a table. I don't want to get a row, where
certian column has a NULL value.
How can I do that?select * from table
where field is not null
"mavrick101" <mavrick101@.discussions.microsoft.com> wrote in message
news:0DAA7CB9-1BF4-4091-A7B0-FCA90581060E@.microsoft.com...
> Hi,
> I want to query few rows from a table. I don't want to get a row, where
> certian column has a NULL value.
> How can I do that?|||SELECT * FROM Table
WHERE column IS NOT NULL
"mavrick101" wrote:

> Hi,
> I want to query few rows from a table. I don't want to get a row, where
> certian column has a NULL value.
> How can I do that?|||SELECT ...
FROM YourTable
WHERE col_x IS NOT NULL
David Portas
SQL Server MVP
--

Wednesday, March 7, 2012

q; loop for insert into

Hello,
I have 10 tables (T1Orj,T2Orj,…T10Orj) and I need to find modified rows fr
om
each table and insert them to T1Bak, T2Bak, …T10Bak. Although original and
bak tables have the common fields , the original tables have more fields tha
n
bak tables, and T1Orj, T2Orj, … T10Orj tables have different table structu
res.
I can go head and write a insert into query for each table, I am just
wondering Is there any way I can do this in a loop for all tables?"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:044F928B-258D-4AC3-BF5B-469B7E692C22@.microsoft.com...
> Hello,
> I have 10 tables (T1Orj,T2Orj,.T10Orj) and I need to find modified rows
> from
> each table and insert them to T1Bak, T2Bak, .T10Bak. Although original and
> bak tables have the common fields , the original tables have more fields
> than
> bak tables, and T1Orj, T2Orj, . T10Orj tables have different table
> structures.
> I can go head and write a insert into query for each table, I am just
> wondering Is there any way I can do this in a loop for all tables?
>
Do you mean the Orj tables all have the same columns? Why? It would be a lot
simpler to combine them into one. Duplicating tables is almost always a
mistake.
If they are all different then I don't understand how you expect a loop
would help you.
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
--|||Thanks for the reply, some type of loop might help me if I can get column
names and table names created dynamically from a table. Then I will perform
only one insert in my code.
"David Portas" wrote:

> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:044F928B-258D-4AC3-BF5B-469B7E692C22@.microsoft.com...
> Do you mean the Orj tables all have the same columns? Why? It would be a l
ot
> simpler to combine them into one. Duplicating tables is almost always a
> mistake.
> If they are all different then I don't understand how you expect a loop
> would help you.
> --
> 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
> --
>
>

Saturday, February 25, 2012

q; loop for insert into

Hello,
I have 10 tables (T1Orj,T2Orj,â?¦T10Orj) and I need to find modified rows from
each table and insert them to T1Bak, T2Bak, â?¦T10Bak. Although original and
bak tables have the common fields , the original tables have more fields than
bak tables, and T1Orj, T2Orj, â?¦ T10Orj tables have different table structures.
I can go head and write a insert into query for each table, I am just
wondering Is there any way I can do this in a loop for all tables?"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:044F928B-258D-4AC3-BF5B-469B7E692C22@.microsoft.com...
> Hello,
> I have 10 tables (T1Orj,T2Orj,.T10Orj) and I need to find modified rows
> from
> each table and insert them to T1Bak, T2Bak, .T10Bak. Although original and
> bak tables have the common fields , the original tables have more fields
> than
> bak tables, and T1Orj, T2Orj, . T10Orj tables have different table
> structures.
> I can go head and write a insert into query for each table, I am just
> wondering Is there any way I can do this in a loop for all tables?
>
Do you mean the Orj tables all have the same columns? Why? It would be a lot
simpler to combine them into one. Duplicating tables is almost always a
mistake.
If they are all different then I don't understand how you expect a loop
would help you.
--
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
--|||Thanks for the reply, some type of loop might help me if I can get column
names and table names created dynamically from a table. Then I will perform
only one insert in my code.
"David Portas" wrote:
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:044F928B-258D-4AC3-BF5B-469B7E692C22@.microsoft.com...
> > Hello,
> > I have 10 tables (T1Orj,T2Orj,.T10Orj) and I need to find modified rows
> > from
> > each table and insert them to T1Bak, T2Bak, .T10Bak. Although original and
> > bak tables have the common fields , the original tables have more fields
> > than
> > bak tables, and T1Orj, T2Orj, . T10Orj tables have different table
> > structures.
> > I can go head and write a insert into query for each table, I am just
> > wondering Is there any way I can do this in a loop for all tables?
> >
> Do you mean the Orj tables all have the same columns? Why? It would be a lot
> simpler to combine them into one. Duplicating tables is almost always a
> mistake.
> If they are all different then I don't understand how you expect a loop
> would help you.
> --
> 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
> --
>
>

Monday, February 20, 2012

Q: subtotal of rows in matrix

Hello,
I have a matrix report, how can I sum rows under a column. It seems add
subtotal works for summing of columns not rows.
Thanks,Nver mind. Found it.
"JIM.H." wrote:
> Hello,
> I have a matrix report, how can I sum rows under a column. It seems add
> subtotal works for summing of columns not rows.
> Thanks,
>|||Hey
I've been having the same problem, only with both rows and columns.
The subtotal for the row only grabs the first column's value and th
column subtotal only grabs the first row's value. Could you pleas
explain how you got the subtotals to work? If you can't at least pos
the solution you figured out
Thanks

Friday, March 30, 2012

Query about Ansi-Syntax

query a sorted view by rows

Wednesday, March 28, 2012

Query ... Distinct rows

Query (IMPORTANT)

Query

Query

query

Monday, March 26, 2012

query

Friday, March 23, 2012

query

query

query

querry: rows must be displayed as columns

Querring only non NULL row/column

Wednesday, March 7, 2012

q; loop for insert into

Saturday, February 25, 2012

q; loop for insert into

Monday, February 20, 2012

Q: subtotal of rows in matrix

db time conversion

Blog Archive

About Me