Dear Reader and Posters,
I have a quick question about the use of a select statement.
I have this big db tbl and I would like to be able to select rows of
data using (i think) distinct. Here is the tricky part... I would only
like to apply the distinct to one column in the table not the all the
columns. I have been reading around and people have been reluctant to
elaborate on other peoples questions that are alike mine because they
say that it makes no sense to do what I want to do because if there
are two rows of data and everything except one column is the same then
what says which one gets selected. My answer is that I don't care. Is
this possible and if so can any one point me in the right direction?
Thank,
dp
Hi
"C" wrote:
> Dear Reader and Posters,
> I have a quick question about the use of a select statement.
>
> I have this big db tbl and I would like to be able to select rows of
> data using (i think) distinct. Here is the tricky part... I would only
> like to apply the distinct to one column in the table not the all the
> columns. I have been reading around and people have been reluctant to
> elaborate on other peoples questions that are alike mine because they
> say that it makes no sense to do what I want to do because if there
> are two rows of data and everything except one column is the same then
> what says which one gets selected. My answer is that I don't care. Is
> this possible and if so can any one point me in the right direction?
>
> Thank,
> dp
If you don't care what the other values are then just take then why don't
you just take the maximum of the other values and group by the column you
want to be distinct?
If this has to be a specific row then use the maximum of the Primary Key and
get the row that corresponds to that e.g.
SELECT m.PK, m.Col1, m.COl2
FROM ( SELECT MAX(PK) AS PK, Col1 FROM MyTable GROUP BY Col1 ) s
JOIN MyTable m ON m.PK = s.PK
John
|||Hi dp,
how should that look like, onyl distincing to one column ? If you want
one row per Distinct value, you will have to aggregate the other
columns with aggregate expressions like:
SELECT A,MON(B),MIN(C)
FROM SoneTable
Group BY A --This is the Distinct Column
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
Showing posts with label dear. Show all posts
Showing posts with label dear. Show all posts
Tuesday, March 27, 2012
Easy select distinct question.....
Dear Reader and Posters,
I have a quick question about the use of a select statement.
I have this big db tbl and I would like to be able to select rows of
data using (i think) distinct. Here is the tricky part... I would only
like to apply the distinct to one column in the table not the all the
columns. I have been reading around and people have been reluctant to
elaborate on other peoples questions that are alike mine because they
say that it makes no sense to do what I want to do because if there
are two rows of data and everything except one column is the same then
what says which one gets selected. My answer is that I don't care. Is
this possible and if so can any one point me in the right direction?
Thank,
dp"C" <devprog@.newfound.biz> wrote in message
news:1171701419.221382.33560@.k78g2000cwa.googlegroups.com...
> Dear Reader and Posters,
> I have a quick question about the use of a select statement.
>
> I have this big db tbl and I would like to be able to select rows of
> data using (i think) distinct. Here is the tricky part... I would only
> like to apply the distinct to one column in the table not the all the
> columns. I have been reading around and people have been reluctant to
> elaborate on other peoples questions that are alike mine because they
> say that it makes no sense to do what I want to do because if there
> are two rows of data and everything except one column is the same then
> what says which one gets selected. My answer is that I don't care. Is
> this possible and if so can any one point me in the right direction?
>
If your table t has a single-column key, say 'id', and you want one row for
each distinct value of a column c, then something like:
select *
from t
where t.id
(select min(id) from t group by c)
David|||Hi
"C" wrote:
> Dear Reader and Posters,
> I have a quick question about the use of a select statement.
>
> I have this big db tbl and I would like to be able to select rows of
> data using (i think) distinct. Here is the tricky part... I would only
> like to apply the distinct to one column in the table not the all the
> columns. I have been reading around and people have been reluctant to
> elaborate on other peoples questions that are alike mine because they
> say that it makes no sense to do what I want to do because if there
> are two rows of data and everything except one column is the same then
> what says which one gets selected. My answer is that I don't care. Is
> this possible and if so can any one point me in the right direction?
>
> Thank,
> dp
If you don't care what the other values are then just take then why don't
you just take the maximum of the other values and group by the column you
want to be distinct?
If this has to be a specific row then use the maximum of the Primary Key and
get the row that corresponds to that e.g.
SELECT m.PK, m.Col1, m.COl2
FROM ( SELECT MAX(PK) AS PK, Col1 FROM MyTable GROUP BY Col1 ) s
JOIN MyTable m ON m.PK = s.PK
John|||Hi dp,
how should that look like, onyl distincing to one column ? If you want
one row per Distinct value, you will have to aggregate the other
columns with aggregate expressions like:
SELECT A,MON(B),MIN(C)
FROM SoneTable
Group BY A --This is the Distinct Column
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
--|||"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1171733568.343342.33660@.q2g2000cwa.googlegroups.com...
> Hi dp,
> how should that look like, onyl distincing to one column ? If you want
> one row per Distinct value, you will have to aggregate the other
> columns with aggregate expressions like:
> SELECT A,MON(B),MIN(C)
> FROM SoneTable
> Group BY A --This is the Distinct Column
>
But this won't give you any particular row. It will be a "frankenstein" row
made up of bits of many rows.
David|||On Feb 17, 1:09 pm, "David Browne" <davidbaxterbrowne no potted
m...@.hotmail.com> wrote:
> "Jens" <J...@.sqlserver2005.de> wrote in message
> news:1171733568.343342.33660@.q2g2000cwa.googlegroups.com...
>
>
>
> But this won't give you any particular row. It will be a "frankenstein" r
ow
> made up of bits of many rows.
> David
Thanks all I will try things out... cdp|||"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:uBvFpcsUHHA.5100@.TK2MSFTNGP06.phx.gbl...
>.
> It will be a "frankenstein" row made up of bits of many rows.
Every once in a while there is real insight in this forum!
Now is it a 'Young Frankenstein', the mature 'Frankenstein' or
perhaps just the 'Ghost of Frankenstein'?
-
I have a quick question about the use of a select statement.
I have this big db tbl and I would like to be able to select rows of
data using (i think) distinct. Here is the tricky part... I would only
like to apply the distinct to one column in the table not the all the
columns. I have been reading around and people have been reluctant to
elaborate on other peoples questions that are alike mine because they
say that it makes no sense to do what I want to do because if there
are two rows of data and everything except one column is the same then
what says which one gets selected. My answer is that I don't care. Is
this possible and if so can any one point me in the right direction?
Thank,
dp"C" <devprog@.newfound.biz> wrote in message
news:1171701419.221382.33560@.k78g2000cwa.googlegroups.com...
> Dear Reader and Posters,
> I have a quick question about the use of a select statement.
>
> I have this big db tbl and I would like to be able to select rows of
> data using (i think) distinct. Here is the tricky part... I would only
> like to apply the distinct to one column in the table not the all the
> columns. I have been reading around and people have been reluctant to
> elaborate on other peoples questions that are alike mine because they
> say that it makes no sense to do what I want to do because if there
> are two rows of data and everything except one column is the same then
> what says which one gets selected. My answer is that I don't care. Is
> this possible and if so can any one point me in the right direction?
>
If your table t has a single-column key, say 'id', and you want one row for
each distinct value of a column c, then something like:
select *
from t
where t.id
(select min(id) from t group by c)
David|||Hi
"C" wrote:
> Dear Reader and Posters,
> I have a quick question about the use of a select statement.
>
> I have this big db tbl and I would like to be able to select rows of
> data using (i think) distinct. Here is the tricky part... I would only
> like to apply the distinct to one column in the table not the all the
> columns. I have been reading around and people have been reluctant to
> elaborate on other peoples questions that are alike mine because they
> say that it makes no sense to do what I want to do because if there
> are two rows of data and everything except one column is the same then
> what says which one gets selected. My answer is that I don't care. Is
> this possible and if so can any one point me in the right direction?
>
> Thank,
> dp
If you don't care what the other values are then just take then why don't
you just take the maximum of the other values and group by the column you
want to be distinct?
If this has to be a specific row then use the maximum of the Primary Key and
get the row that corresponds to that e.g.
SELECT m.PK, m.Col1, m.COl2
FROM ( SELECT MAX(PK) AS PK, Col1 FROM MyTable GROUP BY Col1 ) s
JOIN MyTable m ON m.PK = s.PK
John|||Hi dp,
how should that look like, onyl distincing to one column ? If you want
one row per Distinct value, you will have to aggregate the other
columns with aggregate expressions like:
SELECT A,MON(B),MIN(C)
FROM SoneTable
Group BY A --This is the Distinct Column
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
--|||"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1171733568.343342.33660@.q2g2000cwa.googlegroups.com...
> Hi dp,
> how should that look like, onyl distincing to one column ? If you want
> one row per Distinct value, you will have to aggregate the other
> columns with aggregate expressions like:
> SELECT A,MON(B),MIN(C)
> FROM SoneTable
> Group BY A --This is the Distinct Column
>
But this won't give you any particular row. It will be a "frankenstein" row
made up of bits of many rows.
David|||On Feb 17, 1:09 pm, "David Browne" <davidbaxterbrowne no potted
m...@.hotmail.com> wrote:
> "Jens" <J...@.sqlserver2005.de> wrote in message
> news:1171733568.343342.33660@.q2g2000cwa.googlegroups.com...
>
>
>
> But this won't give you any particular row. It will be a "frankenstein" r
ow
> made up of bits of many rows.
> David
Thanks all I will try things out... cdp|||"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:uBvFpcsUHHA.5100@.TK2MSFTNGP06.phx.gbl...
>.
> It will be a "frankenstein" row made up of bits of many rows.
Every once in a while there is real insight in this forum!
Now is it a 'Young Frankenstein', the mature 'Frankenstein' or
perhaps just the 'Ghost of Frankenstein'?
-
Easy select distinct question.....
Dear Reader and Posters,
I have a quick question about the use of a select statement.
I have this big db tbl and I would like to be able to select rows of
data using (i think) distinct. Here is the tricky part... I would only
like to apply the distinct to one column in the table not the all the
columns. I have been reading around and people have been reluctant to
elaborate on other peoples questions that are alike mine because they
say that it makes no sense to do what I want to do because if there
are two rows of data and everything except one column is the same then
what says which one gets selected. My answer is that I don't care. Is
this possible and if so can any one point me in the right direction?
Thank,
dpHi
"C" wrote:
> Dear Reader and Posters,
> I have a quick question about the use of a select statement.
>
> I have this big db tbl and I would like to be able to select rows of
> data using (i think) distinct. Here is the tricky part... I would only
> like to apply the distinct to one column in the table not the all the
> columns. I have been reading around and people have been reluctant to
> elaborate on other peoples questions that are alike mine because they
> say that it makes no sense to do what I want to do because if there
> are two rows of data and everything except one column is the same then
> what says which one gets selected. My answer is that I don't care. Is
> this possible and if so can any one point me in the right direction?
>
> Thank,
> dp
If you don't care what the other values are then just take then why don't
you just take the maximum of the other values and group by the column you
want to be distinct?
If this has to be a specific row then use the maximum of the Primary Key and
get the row that corresponds to that e.g.
SELECT m.PK, m.Col1, m.COl2
FROM ( SELECT MAX(PK) AS PK, Col1 FROM MyTable GROUP BY Col1 ) s
JOIN MyTable m ON m.PK = s.PK
John|||"C" <devprog@.newfound.biz> wrote in message
news:1171701419.221382.33560@.k78g2000cwa.googlegroups.com...
> Dear Reader and Posters,
> I have a quick question about the use of a select statement.
>
> I have this big db tbl and I would like to be able to select rows of
> data using (i think) distinct. Here is the tricky part... I would only
> like to apply the distinct to one column in the table not the all the
> columns. I have been reading around and people have been reluctant to
> elaborate on other peoples questions that are alike mine because they
> say that it makes no sense to do what I want to do because if there
> are two rows of data and everything except one column is the same then
> what says which one gets selected. My answer is that I don't care. Is
> this possible and if so can any one point me in the right direction?
>
If your table t has a single-column key, say 'id', and you want one row for
each distinct value of a column c, then something like:
select *
from t
where t.id
(select min(id) from t group by c)
David|||Hi dp,
how should that look like, onyl distincing to one column ? If you want
one row per Distinct value, you will have to aggregate the other
columns with aggregate expressions like:
SELECT A,MON(B),MIN(C)
FROM SoneTable
Group BY A --This is the Distinct Column
HTH, Jens K. Suessmeyer.
--
http://www.sqlserver2005.de
--|||"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1171733568.343342.33660@.q2g2000cwa.googlegroups.com...
> Hi dp,
> how should that look like, onyl distincing to one column ? If you want
> one row per Distinct value, you will have to aggregate the other
> columns with aggregate expressions like:
> SELECT A,MON(B),MIN(C)
> FROM SoneTable
> Group BY A --This is the Distinct Column
>
But this won't give you any particular row. It will be a "frankenstein" row
made up of bits of many rows.
David|||On Feb 17, 1:09 pm, "David Browne" <davidbaxterbrowne no potted
m...@.hotmail.com> wrote:
> "Jens" <J...@.sqlserver2005.de> wrote in message
> news:1171733568.343342.33660@.q2g2000cwa.googlegroups.com...
> > Hi dp,
> > how should that look like, onyl distincing to one column ? If you want
> > one row per Distinct value, you will have to aggregate the other
> > columns with aggregate expressions like:
> > SELECT A,MON(B),MIN(C)
> > FROM SoneTable
> > Group BY A --This is the Distinct Column
> But this won't give you any particular row. It will be a "frankenstein" row
> made up of bits of many rows.
> David
Thanks all I will try things out... cdp|||"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:uBvFpcsUHHA.5100@.TK2MSFTNGP06.phx.gbl...
>.
> It will be a "frankenstein" row made up of bits of many rows.
Every once in a while there is real insight in this forum!
Now is it a 'Young Frankenstein', the mature 'Frankenstein' or
perhaps just the 'Ghost of Frankenstein'?
-:)
I have a quick question about the use of a select statement.
I have this big db tbl and I would like to be able to select rows of
data using (i think) distinct. Here is the tricky part... I would only
like to apply the distinct to one column in the table not the all the
columns. I have been reading around and people have been reluctant to
elaborate on other peoples questions that are alike mine because they
say that it makes no sense to do what I want to do because if there
are two rows of data and everything except one column is the same then
what says which one gets selected. My answer is that I don't care. Is
this possible and if so can any one point me in the right direction?
Thank,
dpHi
"C" wrote:
> Dear Reader and Posters,
> I have a quick question about the use of a select statement.
>
> I have this big db tbl and I would like to be able to select rows of
> data using (i think) distinct. Here is the tricky part... I would only
> like to apply the distinct to one column in the table not the all the
> columns. I have been reading around and people have been reluctant to
> elaborate on other peoples questions that are alike mine because they
> say that it makes no sense to do what I want to do because if there
> are two rows of data and everything except one column is the same then
> what says which one gets selected. My answer is that I don't care. Is
> this possible and if so can any one point me in the right direction?
>
> Thank,
> dp
If you don't care what the other values are then just take then why don't
you just take the maximum of the other values and group by the column you
want to be distinct?
If this has to be a specific row then use the maximum of the Primary Key and
get the row that corresponds to that e.g.
SELECT m.PK, m.Col1, m.COl2
FROM ( SELECT MAX(PK) AS PK, Col1 FROM MyTable GROUP BY Col1 ) s
JOIN MyTable m ON m.PK = s.PK
John|||"C" <devprog@.newfound.biz> wrote in message
news:1171701419.221382.33560@.k78g2000cwa.googlegroups.com...
> Dear Reader and Posters,
> I have a quick question about the use of a select statement.
>
> I have this big db tbl and I would like to be able to select rows of
> data using (i think) distinct. Here is the tricky part... I would only
> like to apply the distinct to one column in the table not the all the
> columns. I have been reading around and people have been reluctant to
> elaborate on other peoples questions that are alike mine because they
> say that it makes no sense to do what I want to do because if there
> are two rows of data and everything except one column is the same then
> what says which one gets selected. My answer is that I don't care. Is
> this possible and if so can any one point me in the right direction?
>
If your table t has a single-column key, say 'id', and you want one row for
each distinct value of a column c, then something like:
select *
from t
where t.id
(select min(id) from t group by c)
David|||Hi dp,
how should that look like, onyl distincing to one column ? If you want
one row per Distinct value, you will have to aggregate the other
columns with aggregate expressions like:
SELECT A,MON(B),MIN(C)
FROM SoneTable
Group BY A --This is the Distinct Column
HTH, Jens K. Suessmeyer.
--
http://www.sqlserver2005.de
--|||"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1171733568.343342.33660@.q2g2000cwa.googlegroups.com...
> Hi dp,
> how should that look like, onyl distincing to one column ? If you want
> one row per Distinct value, you will have to aggregate the other
> columns with aggregate expressions like:
> SELECT A,MON(B),MIN(C)
> FROM SoneTable
> Group BY A --This is the Distinct Column
>
But this won't give you any particular row. It will be a "frankenstein" row
made up of bits of many rows.
David|||On Feb 17, 1:09 pm, "David Browne" <davidbaxterbrowne no potted
m...@.hotmail.com> wrote:
> "Jens" <J...@.sqlserver2005.de> wrote in message
> news:1171733568.343342.33660@.q2g2000cwa.googlegroups.com...
> > Hi dp,
> > how should that look like, onyl distincing to one column ? If you want
> > one row per Distinct value, you will have to aggregate the other
> > columns with aggregate expressions like:
> > SELECT A,MON(B),MIN(C)
> > FROM SoneTable
> > Group BY A --This is the Distinct Column
> But this won't give you any particular row. It will be a "frankenstein" row
> made up of bits of many rows.
> David
Thanks all I will try things out... cdp|||"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:uBvFpcsUHHA.5100@.TK2MSFTNGP06.phx.gbl...
>.
> It will be a "frankenstein" row made up of bits of many rows.
Every once in a while there is real insight in this forum!
Now is it a 'Young Frankenstein', the mature 'Frankenstein' or
perhaps just the 'Ghost of Frankenstein'?
-:)
Thursday, March 22, 2012
Easier way of building pivot tables in MS SQL Server
Dear All
--
Hope this helps.
I am very new to MS SQL Server and I am wondering is there some tool
which would allow me to build pivot tables in SQL more easily. At the
moment writing a query can be quite challenging and difficult.
Is there any software which allows you to do it more intuitively and
gives you some visual feedback about query you are building?
I would be very grateful for any help with this.
wujtehacjuszWhat version of SQL Server?
SQL Server 2005 has the PIVOT command.
On Jul 10, 5:19 am, wujtehacjusz <wujtehacj...@.gmail.comwrote:
Quote:
Originally Posted by
Dear All
>
I am very new to MS SQL Server and I am wondering is there some tool
which would allow me to build pivot tables in SQL more easily. At the
moment writing a query can be quite challenging and difficult.
>
Is there any software which allows you to do it more intuitively and
gives you some visual feedback about query you are building?
>
I would be very grateful for any help with this.
>
wujtehacjusz
--
Hope this helps.
Dan Guzman
SQL Server MVP
"wujtehacjusz" <wujtehacjusz@.gmail.comwrote in message
news:1184059198.208520.109590@.q75g2000hsh.googlegr oups.com...
Quote:
Originally Posted by
Dear All
>
I am very new to MS SQL Server and I am wondering is there some tool
which would allow me to build pivot tables in SQL more easily. At the
moment writing a query can be quite challenging and difficult.
>
Is there any software which allows you to do it more intuitively and
gives you some visual feedback about query you are building?
>
I would be very grateful for any help with this.
>
wujtehacjusz
>
Wednesday, March 21, 2012
EA - Managment - Process Info
Dear All,
Within this screen I am getting the same person in the
same databases duplicated.
Is this
1. The fault of the application thats creating the
connection
2. SQL Server doing something
3. Something that I shouldn't be worried about.
Thanks
PeterHi,
First one will happen if you are not closing the connection made. This you
need to
really worry and need to rectify inside your code by closing the connection
as soon as the task is completed.
2. SQL Server will create mutiple threads, those you do not want to worry.
It will be cleared automatically
Thanks
Hari
MCDBA
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:16e1401c4487d$676fab60$a301280a@.phx
.gbl...
> Dear All,
> Within this screen I am getting the same person in the
> same databases duplicated.
> Is this
> 1. The fault of the application thats creating the
> connection
> 2. SQL Server doing something
> 3. Something that I shouldn't be worried about.
> Thanks
> Peter|||Thanks Hari
Peter
>--Original Message--
>Hi,
>First one will happen if you are not closing the
connection made. This you
>need to
>really worry and need to rectify inside your code by
closing the connection
>as soon as the task is completed.
>
>2. SQL Server will create mutiple threads, those you do
not want to worry.
>It will be cleared automatically
>Thanks
>Hari
>MCDBA
>
>"Peter" <anonymous@.discussions.microsoft.com> wrote in
message
> news:16e1401c4487d$676fab60$a301280a@.phx
.gbl...
>
>.
>
Within this screen I am getting the same person in the
same databases duplicated.
Is this
1. The fault of the application thats creating the
connection
2. SQL Server doing something
3. Something that I shouldn't be worried about.
Thanks
PeterHi,
First one will happen if you are not closing the connection made. This you
need to
really worry and need to rectify inside your code by closing the connection
as soon as the task is completed.
2. SQL Server will create mutiple threads, those you do not want to worry.
It will be cleared automatically
Thanks
Hari
MCDBA
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:16e1401c4487d$676fab60$a301280a@.phx
.gbl...
> Dear All,
> Within this screen I am getting the same person in the
> same databases duplicated.
> Is this
> 1. The fault of the application thats creating the
> connection
> 2. SQL Server doing something
> 3. Something that I shouldn't be worried about.
> Thanks
> Peter|||Thanks Hari
Peter
>--Original Message--
>Hi,
>First one will happen if you are not closing the
connection made. This you
>need to
>really worry and need to rectify inside your code by
closing the connection
>as soon as the task is completed.
>
>2. SQL Server will create mutiple threads, those you do
not want to worry.
>It will be cleared automatically
>Thanks
>Hari
>MCDBA
>
>"Peter" <anonymous@.discussions.microsoft.com> wrote in
message
> news:16e1401c4487d$676fab60$a301280a@.phx
.gbl...
>
>.
>
Sunday, February 26, 2012
Dynamic View
Dear All,
Hoping you might be able to help me out with a SQL issue.
Want to have a view that contains a join of two tables:
SELECT dbo.VEC_CASE.*, dbo.VEC_MI.*
FROM dbo.VEC_MI INNER JOIN
dbo.VEC_CASE ON dbo.VEC_MI.ID = dbo.VEC_CASE.ID
Problem is that the ID column exists in both tables (and contains the
same value in both) so it wont work as a view, even though it runs fine
as a query.
We keep on adding columns to the tables, and am sick of having to
remember to redefine the view each time and specify all the columns we
want (real one is much more complex than this one).
Can you suggest a way of creating a dynamic view which returns all the
columns, but only one instance of the ID column?
Have tried -
select column_name + ', '
from information_schema.columns where table_name = 'vec_mi'
and column_name <> 'id'
Which will give me a list of all the columns except ID, but the results
are in the form of a recordset. When I try to use this in the view:
SELECT
dbo.VEC_CASE.*,
(select column_name + ', '
from information_schema.columns where table_name = 'vec_mi'
and column_name <> 'id' )
FROM dbo.VEC_MI INNER JOIN
dbo.VEC_CASE ON dbo.VEC_MI.ID = dbo.VEC_CASE.ID
It complains that the subquery returns more than one value.
Is there a way to convert the contents of a recordset into a single
string?
Also tried to create a Stored Procedure / Function to return the
results of the subquery, but cant get the view to recognise the name of
the stored procedure - thinks it is a column.
Also, this whole approach would mean that the design of the view might
change upon execution and so the view might not allow me to do this in
any event.
All assistance gratefully accepted.
Thanks,
Martinjumpa (martin@.jumpa.co.uk) writes:
> Hoping you might be able to help me out with a SQL issue.
> Want to have a view that contains a join of two tables:
> SELECT dbo.VEC_CASE.*, dbo.VEC_MI.*
> FROM dbo.VEC_MI INNER JOIN
> dbo.VEC_CASE ON dbo.VEC_MI.ID = dbo.VEC_CASE.ID
> Problem is that the ID column exists in both tables (and contains the
> same value in both) so it wont work as a view, even though it runs fine
> as a query.
> We keep on adding columns to the tables, and am sick of having to
> remember to redefine the view each time and specify all the columns we
> want (real one is much more complex than this one).
> Can you suggest a way of creating a dynamic view which returns all the
> columns, but only one instance of the ID column?
Keep on adding the columns *that you need* to the view. SELECT * is
generally frowned upon in production code. Say that in five years from
now, someone is looking at the tables and says "hm, I wonder if that
column foo is really used for something real". Well, if SELECT statements
and views only lists columns that are actually used for something, it
can be quite easy to find out, at least if all access is through stored
procedure. But with the SELECT statement like the above, you need to
dive into the client code.
No big deal? There may be a cost for maintaining the value in foo,
and one may consider a redesign that would be a lot easier if we
got forget about foo. But if it's impossible to tell whether foo is
in use, it will have to stay.
And so the system grows, acquiring a bigger and bigger backpack of
legacy, making the system difficult to maintain and evolve.
So keep on adding the columns that are really needed in the view, and
no others.
And, no, there is no "SELECT * - thatcolmn".
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|||On 6 Jun 2006 06:37:44 -0700, jumpa wrote:
>Dear All,
>Hoping you might be able to help me out with a SQL issue.
(snip)
>All assistance gratefully accepted.
Hi Martin,
In addiition to Erland's reply - both Querty Analyzer and SQL Server
Management Studio allow you to quickly copy alll column names of a table
to a query window by using drag & drop from the object explorer. After
that, you'll just have to remove the duplicates and the unneeded
columns, add prefixes, and you're done.
Hugo Kornelis, SQL Server MVP|||Thanks for the responses guys. This is the first time i've ever used a
discussion group for help with development, definitely not the last.
Agreed, will just have to continue with the manual route.
Incidentally, have since written a stored procedure which drops and
recreates the view with all columns.
Many thanks,
Martin
Hoping you might be able to help me out with a SQL issue.
Want to have a view that contains a join of two tables:
SELECT dbo.VEC_CASE.*, dbo.VEC_MI.*
FROM dbo.VEC_MI INNER JOIN
dbo.VEC_CASE ON dbo.VEC_MI.ID = dbo.VEC_CASE.ID
Problem is that the ID column exists in both tables (and contains the
same value in both) so it wont work as a view, even though it runs fine
as a query.
We keep on adding columns to the tables, and am sick of having to
remember to redefine the view each time and specify all the columns we
want (real one is much more complex than this one).
Can you suggest a way of creating a dynamic view which returns all the
columns, but only one instance of the ID column?
Have tried -
select column_name + ', '
from information_schema.columns where table_name = 'vec_mi'
and column_name <> 'id'
Which will give me a list of all the columns except ID, but the results
are in the form of a recordset. When I try to use this in the view:
SELECT
dbo.VEC_CASE.*,
(select column_name + ', '
from information_schema.columns where table_name = 'vec_mi'
and column_name <> 'id' )
FROM dbo.VEC_MI INNER JOIN
dbo.VEC_CASE ON dbo.VEC_MI.ID = dbo.VEC_CASE.ID
It complains that the subquery returns more than one value.
Is there a way to convert the contents of a recordset into a single
string?
Also tried to create a Stored Procedure / Function to return the
results of the subquery, but cant get the view to recognise the name of
the stored procedure - thinks it is a column.
Also, this whole approach would mean that the design of the view might
change upon execution and so the view might not allow me to do this in
any event.
All assistance gratefully accepted.
Thanks,
Martinjumpa (martin@.jumpa.co.uk) writes:
> Hoping you might be able to help me out with a SQL issue.
> Want to have a view that contains a join of two tables:
> SELECT dbo.VEC_CASE.*, dbo.VEC_MI.*
> FROM dbo.VEC_MI INNER JOIN
> dbo.VEC_CASE ON dbo.VEC_MI.ID = dbo.VEC_CASE.ID
> Problem is that the ID column exists in both tables (and contains the
> same value in both) so it wont work as a view, even though it runs fine
> as a query.
> We keep on adding columns to the tables, and am sick of having to
> remember to redefine the view each time and specify all the columns we
> want (real one is much more complex than this one).
> Can you suggest a way of creating a dynamic view which returns all the
> columns, but only one instance of the ID column?
Keep on adding the columns *that you need* to the view. SELECT * is
generally frowned upon in production code. Say that in five years from
now, someone is looking at the tables and says "hm, I wonder if that
column foo is really used for something real". Well, if SELECT statements
and views only lists columns that are actually used for something, it
can be quite easy to find out, at least if all access is through stored
procedure. But with the SELECT statement like the above, you need to
dive into the client code.
No big deal? There may be a cost for maintaining the value in foo,
and one may consider a redesign that would be a lot easier if we
got forget about foo. But if it's impossible to tell whether foo is
in use, it will have to stay.
And so the system grows, acquiring a bigger and bigger backpack of
legacy, making the system difficult to maintain and evolve.
So keep on adding the columns that are really needed in the view, and
no others.
And, no, there is no "SELECT * - thatcolmn".
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|||On 6 Jun 2006 06:37:44 -0700, jumpa wrote:
>Dear All,
>Hoping you might be able to help me out with a SQL issue.
(snip)
>All assistance gratefully accepted.
Hi Martin,
In addiition to Erland's reply - both Querty Analyzer and SQL Server
Management Studio allow you to quickly copy alll column names of a table
to a query window by using drag & drop from the object explorer. After
that, you'll just have to remove the duplicates and the unneeded
columns, add prefixes, and you're done.
Hugo Kornelis, SQL Server MVP|||Thanks for the responses guys. This is the first time i've ever used a
discussion group for help with development, definitely not the last.
Agreed, will just have to continue with the manual route.
Incidentally, have since written a stored procedure which drops and
recreates the view with all columns.
Many thanks,
Martin
Friday, February 24, 2012
dynamic tablename issue
Dear all:
I have a storedprocedure for db loader form some temp tables.
my storedprocedure as the following:
declare c cursor
for
select * from @.tablename
open c
fetch c into ...
but I get an error for declare cursor for dynamic tablename,
did sql server has some BIF to evaluate the variable for table name?
thanks for your kindly assistance.
regards,
Stanley Huanguse the dynamic sql statments EXEC(@.sql_statement) or sp_executesql see the Holy BOL|||Originally posted by yoavmaimon
use the dynamic sql statments EXEC(@.sql_statement) or sp_executesql see the Holy BOL
do you mean I can write the stored procedure as:
create proc sp_dynamicTableName
(
declare @.tablename varchar(20)
)
as
declare c cursor
for
exec('select * from ' & @.tablename)
open c
fetch c into ...
regards for your reply
thanks
Stanley Huang|||what u wrote wont work,
1. u must build the sql statement out of th exec
2. all the sql statements that uses the @.tablename variable must be dynamic.
3. why cursors?|||Originally posted by yoavmaimon
what u wrote wont work,
1. u must build the sql statement out of th exec
2. all the sql statements that uses the @.tablename variable must be dynamic.
3. why cursors?
Dear Sir:
Why cursor?
Because I have to parse the records and and by the columns to do some extra action.
After actions, and I will use Waitfor the pause the cursor engine to limit the resource of this stored procedure.
Do you know how to set the priority of specified account in sql server,
then I can avoid to use cursor.
regards,
Stanley Huang
I have a storedprocedure for db loader form some temp tables.
my storedprocedure as the following:
declare c cursor
for
select * from @.tablename
open c
fetch c into ...
but I get an error for declare cursor for dynamic tablename,
did sql server has some BIF to evaluate the variable for table name?
thanks for your kindly assistance.
regards,
Stanley Huanguse the dynamic sql statments EXEC(@.sql_statement) or sp_executesql see the Holy BOL|||Originally posted by yoavmaimon
use the dynamic sql statments EXEC(@.sql_statement) or sp_executesql see the Holy BOL
do you mean I can write the stored procedure as:
create proc sp_dynamicTableName
(
declare @.tablename varchar(20)
)
as
declare c cursor
for
exec('select * from ' & @.tablename)
open c
fetch c into ...
regards for your reply
thanks
Stanley Huang|||what u wrote wont work,
1. u must build the sql statement out of th exec
2. all the sql statements that uses the @.tablename variable must be dynamic.
3. why cursors?|||Originally posted by yoavmaimon
what u wrote wont work,
1. u must build the sql statement out of th exec
2. all the sql statements that uses the @.tablename variable must be dynamic.
3. why cursors?
Dear Sir:
Why cursor?
Because I have to parse the records and and by the columns to do some extra action.
After actions, and I will use Waitfor the pause the cursor engine to limit the resource of this stored procedure.
Do you know how to set the priority of specified account in sql server,
then I can avoid to use cursor.
regards,
Stanley Huang
Subscribe to:
Posts (Atom)