How can I create a view which based on a store procedure and then sp will
return a select statement only? Thanks.You can't (*). A view is one SELECT statement (which can be complex in itself), and a SELECT
statement read data from tables or views, it doesn't execute stored procedures. Consider re-writing
the procedure to a table-valued function.
(*) Well, you can by using OPENROWSET or OPENQUERY, but that is a hack and I wouldn't use it in
production.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"00KobeBrian" <a@.b.com> wrote in message news:OWbx40TXGHA.196@.TK2MSFTNGP04.phx.gbl...
> How can I create a view which based on a store procedure and then sp will return a select
> statement only? Thanks.
>|||Hi,
I'd like to know your concern clearly:
You want to create a store procedure which return a data set and you want
to create a view based on the data set.
I think you should use User Defined Function(UDF), it Returns a table Data
Type which can be used after As in Create View statement.
You can refer to following link for sample of UDF.
<http://msdn.microsoft.com/library/default.asp?url=/library/en-us/samples/sa
mples_1far.asp>
Hope it helps.
Best regards,
Vincent Xu
Microsoft Online Partner Support
======================================================Get Secure! - www.microsoft.com/security
======================================================When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
======================================================This posting is provided "AS IS" with no warranties,and confers no rights.
======================================================>>From: "00KobeBrian" <a@.b.com>
>>Subject: create a view
>>Date: Tue, 11 Apr 2006 15:54:51 +0800
>>Lines: 4
>>X-Priority: 3
>>X-MSMail-Priority: Normal
>>X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
>>X-RFC2646: Format=Flowed; Original
>>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
>>Message-ID: <OWbx40TXGHA.196@.TK2MSFTNGP04.phx.gbl>
>>Newsgroups: microsoft.public.sqlserver.server
>>NNTP-Posting-Host: 210.177.248.66
>>Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP01.phx.gbl!TK2MSFTNGP04.phx.gbl
>>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:427533
>>X-Tomcat-NG: microsoft.public.sqlserver.server
>>How can I create a view which based on a store procedure and then sp will
>>return a select statement only? Thanks.
>>|||or, maybe you'd be happier skipping the whole view thing entirely, and
using the stored procedures to return the table and or data you are
after.
Showing posts with label statement. Show all posts
Showing posts with label statement. Show all posts
Tuesday, March 27, 2012
create a view
How can I create a view which based on a store procedure and then sp will
return a select statement only? Thanks.You can't (*). A view is one SELECT statement (which can be complex in itsel
f), and a SELECT
statement read data from tables or views, it doesn't execute stored procedur
es. Consider re-writing
the procedure to a table-valued function.
(*) Well, you can by using OPENROWSET or OPENQUERY, but that is a hack and I
wouldn't use it in
production.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"00KobeBrian" <a@.b.com> wrote in message news:OWbx40TXGHA.196@.TK2MSFTNGP04.phx.gbl...seagreen">
> How can I create a view which based on a store procedure and then sp will
return a select
> statement only? Thanks.
>|||Hi,
I'd like to know your concern clearly:
You want to create a store procedure which return a data set and you want
to create a view based on the data set.
I think you should use User Defined Function(UDF), it Returns a table Data
Type which can be used after As in Create View statement.
You can refer to following link for sample of UDF.
<http://msdn.microsoft.com/library/d...n-us/samples/sa
mples_1far.asp>
Hope it helps.
Best regards,
Vincent Xu
Microsoft Online Partner Support
========================================
==============
Get Secure! - www.microsoft.com/security
========================================
==============
When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties,and confers no rights.
========================================
==============
--[vbcol=seagreen]|||or, maybe you'd be happier skipping the whole view thing entirely, and
using the stored procedures to return the table and or data you are
after.
return a select statement only? Thanks.You can't (*). A view is one SELECT statement (which can be complex in itsel
f), and a SELECT
statement read data from tables or views, it doesn't execute stored procedur
es. Consider re-writing
the procedure to a table-valued function.
(*) Well, you can by using OPENROWSET or OPENQUERY, but that is a hack and I
wouldn't use it in
production.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"00KobeBrian" <a@.b.com> wrote in message news:OWbx40TXGHA.196@.TK2MSFTNGP04.phx.gbl...seagreen">
> How can I create a view which based on a store procedure and then sp will
return a select
> statement only? Thanks.
>|||Hi,
I'd like to know your concern clearly:
You want to create a store procedure which return a data set and you want
to create a view based on the data set.
I think you should use User Defined Function(UDF), it Returns a table Data
Type which can be used after As in Create View statement.
You can refer to following link for sample of UDF.
<http://msdn.microsoft.com/library/d...n-us/samples/sa
mples_1far.asp>
Hope it helps.
Best regards,
Vincent Xu
Microsoft Online Partner Support
========================================
==============
Get Secure! - www.microsoft.com/security
========================================
==============
When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties,and confers no rights.
========================================
==============
--[vbcol=seagreen]|||or, maybe you'd be happier skipping the whole view thing entirely, and
using the stored procedures to return the table and or data you are
after.
Create a table with a Union and specify Primary Key
I want to create a table with a union. Which I have already accomplished. I want to specify the Primary Key in the statement.
Or would I have to use another statement. How would I do that? With an update and what would the syntax be?
Thanks before hand,
itarinLike this?
USE Northwind
GO
DECLARE @.OrderID int
SELECT @.OrderId = OrderId FROM Orders
SELECT @.OrderId
GO
DECLARE @.OrderID int
SET @.OrderId = (SELECT OrderId FROM Orders)
SELECT @.OrderId
GO
USE Northwind
GO
CREATE TABLE myTable99(Col1 int)
GO
INSERT INTO myTable99(Col1) SELECT 1
GO
SELECT * FROM myTable99
GO
DECLARE @.Col1 int
SELECT @.Col1 = 0
UPDATE myTable99 SET @.Col1 = Col1 = @.Col1+1
SELECT * FROM myTable99
SELECT @.Col1
GO
DROP TABLE myTable99
GO
SELECT Char(160)
USE Northwind
CREATE TABLE myTable99(Col1 int)
GO
INSERT INTO myTable99(Col1)
SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
GO
DECLARE myCursor99 CURSOR FOR SELECT Col1 FROM myTable99
DECLARE @.Col1 int
OPEN myCursor99
FETCH NEXT FROM myCursor99 INTO @.Col1
SELECT '@.@.ERROR = ' + CONVERT(varchar(5),@.@.ERROR) + ' @.@.FETCH_STATUS = ' + CONVERT(varchar(5), @.@.FETCH_STATUS)
SELECT @.Col1
WHILE @.@.FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM myCursor99 INTO @.Col1
SELECT '@.@.ERROR = ' + CONVERT(varchar(5),@.@.ERROR) + ' @.@.FETCH_STATUS = ' + CONVERT(varchar(5), @.@.FETCH_STATUS)
SELECT @.Col1
FETCH NEXT FROM myCursor99 INTO @.Col1
SELECT '@.@.ERROR = ' + CONVERT(varchar(5),@.@.ERROR) + ' @.@.FETCH_STATUS = ' + CONVERT(varchar(5), @.@.FETCH_STATUS)
SELECT @.Col1
END
CLOSE myCursor99
DEALLOCATE myCursor99
GO
DROP Table myTable99
GO
CREATE TRIGGER myTrigger99 ON EmployeeGamingLicense
FOR UPDATE
AS
INSERT INTO TERMINATION(Status, [TM #], LastName, FirstName, SocialSecurityNumber, DateHired, Title)
SELECT STATUS, [TM#], LASTNAME, FIRSTNAME, [SSN#], HIREDATE, JOBTITLE
FROM inserted
WHERE STATUS = 'TERMINATED'
--Assuming you want them removed as well, and assumin SSN# is the key
DELETE FROM EmployeeGamingLicense o
WHERE EXISTS (SELECT * FROM inserted i WHERE STATUS = 'TERMINATED' AND o.[SSN#] = i.[SSN#])
USE Northwind
GO
CREATE TABLE myTable00(Col1 int NOT NULL)
CREATE TABLE myTable01(Col1 int NOT NULL)
GO
INSERT INTO myTable00(Col1) SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
INSERT INTO myTable01(Col1) SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
GO
SELECT * INTO myTable99 FROM myTable00 UNION ALL SELECT * FROM myTable01
GO
sp_Help myTable99
GO
ALTER TABLE myTable99 ADD CONSTRAINT [PK_Col1] PRIMARY KEY CLUSTERED
(
[Col1]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
sp_Help myTable99
GO
SELECT * FROM myTable99
GO
DROP TABLE myTable00
DROP TABLE myTable01
DROP TABLE myTable99
GO
Or would I have to use another statement. How would I do that? With an update and what would the syntax be?
Thanks before hand,
itarinLike this?
USE Northwind
GO
DECLARE @.OrderID int
SELECT @.OrderId = OrderId FROM Orders
SELECT @.OrderId
GO
DECLARE @.OrderID int
SET @.OrderId = (SELECT OrderId FROM Orders)
SELECT @.OrderId
GO
USE Northwind
GO
CREATE TABLE myTable99(Col1 int)
GO
INSERT INTO myTable99(Col1) SELECT 1
GO
SELECT * FROM myTable99
GO
DECLARE @.Col1 int
SELECT @.Col1 = 0
UPDATE myTable99 SET @.Col1 = Col1 = @.Col1+1
SELECT * FROM myTable99
SELECT @.Col1
GO
DROP TABLE myTable99
GO
SELECT Char(160)
USE Northwind
CREATE TABLE myTable99(Col1 int)
GO
INSERT INTO myTable99(Col1)
SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
GO
DECLARE myCursor99 CURSOR FOR SELECT Col1 FROM myTable99
DECLARE @.Col1 int
OPEN myCursor99
FETCH NEXT FROM myCursor99 INTO @.Col1
SELECT '@.@.ERROR = ' + CONVERT(varchar(5),@.@.ERROR) + ' @.@.FETCH_STATUS = ' + CONVERT(varchar(5), @.@.FETCH_STATUS)
SELECT @.Col1
WHILE @.@.FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM myCursor99 INTO @.Col1
SELECT '@.@.ERROR = ' + CONVERT(varchar(5),@.@.ERROR) + ' @.@.FETCH_STATUS = ' + CONVERT(varchar(5), @.@.FETCH_STATUS)
SELECT @.Col1
FETCH NEXT FROM myCursor99 INTO @.Col1
SELECT '@.@.ERROR = ' + CONVERT(varchar(5),@.@.ERROR) + ' @.@.FETCH_STATUS = ' + CONVERT(varchar(5), @.@.FETCH_STATUS)
SELECT @.Col1
END
CLOSE myCursor99
DEALLOCATE myCursor99
GO
DROP Table myTable99
GO
CREATE TRIGGER myTrigger99 ON EmployeeGamingLicense
FOR UPDATE
AS
INSERT INTO TERMINATION(Status, [TM #], LastName, FirstName, SocialSecurityNumber, DateHired, Title)
SELECT STATUS, [TM#], LASTNAME, FIRSTNAME, [SSN#], HIREDATE, JOBTITLE
FROM inserted
WHERE STATUS = 'TERMINATED'
--Assuming you want them removed as well, and assumin SSN# is the key
DELETE FROM EmployeeGamingLicense o
WHERE EXISTS (SELECT * FROM inserted i WHERE STATUS = 'TERMINATED' AND o.[SSN#] = i.[SSN#])
USE Northwind
GO
CREATE TABLE myTable00(Col1 int NOT NULL)
CREATE TABLE myTable01(Col1 int NOT NULL)
GO
INSERT INTO myTable00(Col1) SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
INSERT INTO myTable01(Col1) SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
GO
SELECT * INTO myTable99 FROM myTable00 UNION ALL SELECT * FROM myTable01
GO
sp_Help myTable99
GO
ALTER TABLE myTable99 ADD CONSTRAINT [PK_Col1] PRIMARY KEY CLUSTERED
(
[Col1]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
sp_Help myTable99
GO
SELECT * FROM myTable99
GO
DROP TABLE myTable00
DROP TABLE myTable01
DROP TABLE myTable99
GO
create a table naming it from a variable
How can I leave the table name in a create statement as a variable?
DECLARE @.tname varchar(32)
SET @.tname = 'ralph'
CREATE TABLE @.tname...
I need to create tables/temp tables for multiple ado.net users and I
hope to gain some performance improvements over dynamic sql.
thx
md
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!You do this via dynamic SQL, like so:
DECLARE @.tname varchar(32)
SET @.tname = 'ralph'
DECLARE @.CMD varchar(1000)
set @.CMD = 'CREATE TABLE ' + rtrim(@.tname) + ' (Id int, code int)'
exec (@.cmd)
insert into ralph values (1, 1)
select * from ralph
drop table ralph
--
----
----
-
Need SQL Server Examples check out my website
http://www.geocities.com/sqlserverexamples
"M D" <mardukes@.aol.com> wrote in message
news:ery$E95DFHA.3536@.TK2MSFTNGP15.phx.gbl...
> How can I leave the table name in a create statement as a variable?
> DECLARE @.tname varchar(32)
> SET @.tname = 'ralph'
> CREATE TABLE @.tname...
> I need to create tables/temp tables for multiple ado.net users and I
> hope to gain some performance improvements over dynamic sql.
> thx
> md
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!
DECLARE @.tname varchar(32)
SET @.tname = 'ralph'
CREATE TABLE @.tname...
I need to create tables/temp tables for multiple ado.net users and I
hope to gain some performance improvements over dynamic sql.
thx
md
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!You do this via dynamic SQL, like so:
DECLARE @.tname varchar(32)
SET @.tname = 'ralph'
DECLARE @.CMD varchar(1000)
set @.CMD = 'CREATE TABLE ' + rtrim(@.tname) + ' (Id int, code int)'
exec (@.cmd)
insert into ralph values (1, 1)
select * from ralph
drop table ralph
--
----
----
-
Need SQL Server Examples check out my website
http://www.geocities.com/sqlserverexamples
"M D" <mardukes@.aol.com> wrote in message
news:ery$E95DFHA.3536@.TK2MSFTNGP15.phx.gbl...
> How can I leave the table name in a create statement as a variable?
> DECLARE @.tname varchar(32)
> SET @.tname = 'ralph'
> CREATE TABLE @.tname...
> I need to create tables/temp tables for multiple ado.net users and I
> hope to gain some performance improvements over dynamic sql.
> thx
> md
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!
Create a table from a select statement
Good afternoon.
Who knows how I can easily create a table where the column names come from another table and are created dynamically?
That is to say. I need to create a new table where the columns, an unknown quantity, are created and given their names from an existing table.
Theory would say:
Create table Bin
as select xyz from ABC
group by xyz
Unfortunately, MS Query Analyzer complains about the AS !!
SQL 2000 server.
Look forward to hearing about the correct way of doing this :-)Yep, SQL Server doesn't like the Oracle format for doing this.
Try
Select XYZ
Into Bin
From ABC
Group by xyz
This will create the correct column data types & lengths but will not create any of their corresponding dependencies. If this is to be part of a process you will also need some sort of logic to determine if that table already exists.
Hope this helps.|||Thanks, good but this only works into a #temp, otherwise get "run sp_dboption" error.
This enters the data as rows. I need columns so that I can add the "real" data afterwards.
Any more ideas ?
Thanks very much|||Select into used to be discouraged because it was a nonlogged transaction that would invalidate your backup sequence. It may still be, though I couldn't find anything specific on this in Books Online.
Run this statement:
select DATABASEPROPERTY('YOURDBNAME', 'IsBulkCopy')
If the result is 0, your database is not set to allow non-logged transactions. You may need to change the setting to use SELECT INSERT.
blindman|||http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_reslsyserr_1_1r94.asp
Is there a specific reason the SELECT INTO option is not permitted in the DB you are working on? You may want to look into it. As for creating just the columns, add the clause WHERE 1 = -1.|||Interesting, very interesting.
I shall have to look into this and find out why that DB has been set up in such a manner on Monday.
I will use a work round, export to .csv gives me a nice long, 1500 comma sperated names, and then just do a normal create and let it all work for a while :-)
What a nightmare, I hate work arounds, but it is a once off DB create.
Thanks for your help, I shall stay tuned and hope that I can be of help to you in the near future.
Take care and enjoy
HandyMac|||Option No 2
Open Fox 7, work with data, create the required table, import into SQL server.
Life is great :-)
Take care and have a great weekend,
HandyMacsql
Who knows how I can easily create a table where the column names come from another table and are created dynamically?
That is to say. I need to create a new table where the columns, an unknown quantity, are created and given their names from an existing table.
Theory would say:
Create table Bin
as select xyz from ABC
group by xyz
Unfortunately, MS Query Analyzer complains about the AS !!
SQL 2000 server.
Look forward to hearing about the correct way of doing this :-)Yep, SQL Server doesn't like the Oracle format for doing this.
Try
Select XYZ
Into Bin
From ABC
Group by xyz
This will create the correct column data types & lengths but will not create any of their corresponding dependencies. If this is to be part of a process you will also need some sort of logic to determine if that table already exists.
Hope this helps.|||Thanks, good but this only works into a #temp, otherwise get "run sp_dboption" error.
This enters the data as rows. I need columns so that I can add the "real" data afterwards.
Any more ideas ?
Thanks very much|||Select into used to be discouraged because it was a nonlogged transaction that would invalidate your backup sequence. It may still be, though I couldn't find anything specific on this in Books Online.
Run this statement:
select DATABASEPROPERTY('YOURDBNAME', 'IsBulkCopy')
If the result is 0, your database is not set to allow non-logged transactions. You may need to change the setting to use SELECT INSERT.
blindman|||http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_reslsyserr_1_1r94.asp
Is there a specific reason the SELECT INTO option is not permitted in the DB you are working on? You may want to look into it. As for creating just the columns, add the clause WHERE 1 = -1.|||Interesting, very interesting.
I shall have to look into this and find out why that DB has been set up in such a manner on Monday.
I will use a work round, export to .csv gives me a nice long, 1500 comma sperated names, and then just do a normal create and let it all work for a while :-)
What a nightmare, I hate work arounds, but it is a once off DB create.
Thanks for your help, I shall stay tuned and hope that I can be of help to you in the near future.
Take care and enjoy
HandyMac|||Option No 2
Open Fox 7, work with data, create the required table, import into SQL server.
Life is great :-)
Take care and have a great weekend,
HandyMacsql
Sunday, March 25, 2012
Create a sql stement from existing tables
Is there any way to automatically create a "CREATE TABLE" statment using an
existing table?
I would simply want to execute this statement and it would create, in
another database, a table with the same structure (no data).
Is there a way to do this?
cheers
nathan
hi Nathan,
Nathan wrote:
> Is there any way to automatically create a "CREATE TABLE" statment
> using an existing table?
> I would simply want to execute this statement and it would create, in
> another database, a table with the same structure (no data).
> Is there a way to do this?
not directly... you can use tools like Enterlirse Manager (if you are
licensed to) or even free tools like QALite
(http://www.rac4sql.net/qalite_main.asp) to generate thos kind of DDL...
but with some efforts you can dig into the INFORMATION_SCHEMA views to
output the desired result...
start with
http://msdn.microsoft.com/library/de...a-iz_87w3.asp,
INFORMATION_SCHEMA.COLUMNS..
this is a 5 minutes sample... if you like more deep details such as
constraints, keys, index and the like you have to expand your search on the
other INFORMATION_SCHEMA views..
SET NOCOUNT ON
USE tempdb
GO
CREATE TABLE dbo.table1 (
Id int NOT NULL PRIMARY KEY ,
vc varchar(10) NULL ,
c char(1) NOT NULL DEFAULT ('a') ,
i int ,
d decimal(18,4) NOT NULL DEFAULT 0
)
GO
DECLARE @.COLUMN_NAME nvarchar(128),
@.COLUMN_DEFAULT nvarchar(4000),
@.IS_NULLABLE varchar(3),
@.DATA_TYPE nvarchar(128),
@.NUMERIC_PRECISION tinyint,
@.NUMERIC_SCALE int,
@.COLLATION_NAME nvarchar(128),
@.CHARACTER_MAXIMUM_LENGTH smallint
DECLARE @.cmd varchar(8000)
SET @.cmd = ''
DECLARE t CURSOR FOR
SELECT c.COLUMN_NAME, c.COLUMN_DEFAULT, c.IS_NULLABLE, c.DATA_TYPE,
c.NUMERIC_PRECISION, c.NUMERIC_SCALE, c.COLLATION_NAME,
c.CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE c.TABLE_SCHEMA = 'dbo'
AND c.TABLE_NAME = 'table1'
ORDER BY c.ORDINAL_POSITION
OPEN t
FETCH NEXT FROM t
INTO @.COLUMN_NAME, @.COLUMN_DEFAULT, @.IS_NULLABLE, @.DATA_TYPE,
@.NUMERIC_PRECISION, @.NUMERIC_SCALE, @.COLLATION_NAME,
@.CHARACTER_MAXIMUM_LENGTH
WHILE @.@.FETCH_STATUS = 0 BEGIN
IF DATALENGTH(@.cmd) <> 0 SET @.cmd = @.cmd + ' ,' + CHAR(10)
SET @.cmd = @.cmd + CHAR(9) + QUOTENAME( @.COLUMN_NAME )+ ' ' + @.DATA_TYPE + '
'
IF @.DATA_TYPE IN ('varchar', 'nvarchar', 'char', 'nchar')
SET @.cmd = @.cmd + '(' + CONVERT(varchar, @.CHARACTER_MAXIMUM_LENGTH) + ') '
IF @.DATA_TYPE = 'decimal' OR @.DATA_TYPE = 'numeric'
SET @.cmd = @.cmd + '(' + CONVERT(varchar, @.NUMERIC_PRECISION) + ', ' +
CONVERT(varchar, @.NUMERIC_SCALE) + ') '
IF NOT @.COLUMN_DEFAULT IS NULL
SET @.cmd = @.cmd + 'DEFAULT ' + @.COLUMN_DEFAULT + ' '
IF NOT @.COLLATION_NAME IS NULL
SET @.cmd = @.cmd + 'COLLATE ' + @.COLLATION_NAME + ' '
IF @.IS_NULLABLE = 'YES'
SET @.cmd = @.cmd + 'NULL '
ELSE
SET @.cmd = @.cmd + 'NOT NULL '
FETCH NEXT FROM t
INTO @.COLUMN_NAME, @.COLUMN_DEFAULT, @.IS_NULLABLE, @.DATA_TYPE,
@.NUMERIC_PRECISION, @.NUMERIC_SCALE, @.COLLATION_NAME,
@.CHARACTER_MAXIMUM_LENGTH
END
CLOSE t
DEALLOCATE t
SET @.cmd = 'CREATE TABLE ' + QUOTENAME('dbo') + '.' + QUOTENAME('table1') +
' (' + CHAR(10)
+ @.cmd + CHAR(10)
+ CHAR(9) + ')'
SELECT @.cmd
GO
DROP TABLE dbo.table1
--<--
CREATE TABLE [dbo].[table1] (
[Id] int NOT NULL ,
[vc] varchar (10) COLLATE Latin1_General_CI_AS NULL ,
[c] char (1) DEFAULT ('a') COLLATE Latin1_General_CI_AS NOT NULL ,
[i] int NULL ,
[d] decimal (18, 4) DEFAULT (0) NOT NULL
)
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
existing table?
I would simply want to execute this statement and it would create, in
another database, a table with the same structure (no data).
Is there a way to do this?
cheers
nathan
hi Nathan,
Nathan wrote:
> Is there any way to automatically create a "CREATE TABLE" statment
> using an existing table?
> I would simply want to execute this statement and it would create, in
> another database, a table with the same structure (no data).
> Is there a way to do this?
not directly... you can use tools like Enterlirse Manager (if you are
licensed to) or even free tools like QALite
(http://www.rac4sql.net/qalite_main.asp) to generate thos kind of DDL...
but with some efforts you can dig into the INFORMATION_SCHEMA views to
output the desired result...
start with
http://msdn.microsoft.com/library/de...a-iz_87w3.asp,
INFORMATION_SCHEMA.COLUMNS..
this is a 5 minutes sample... if you like more deep details such as
constraints, keys, index and the like you have to expand your search on the
other INFORMATION_SCHEMA views..
SET NOCOUNT ON
USE tempdb
GO
CREATE TABLE dbo.table1 (
Id int NOT NULL PRIMARY KEY ,
vc varchar(10) NULL ,
c char(1) NOT NULL DEFAULT ('a') ,
i int ,
d decimal(18,4) NOT NULL DEFAULT 0
)
GO
DECLARE @.COLUMN_NAME nvarchar(128),
@.COLUMN_DEFAULT nvarchar(4000),
@.IS_NULLABLE varchar(3),
@.DATA_TYPE nvarchar(128),
@.NUMERIC_PRECISION tinyint,
@.NUMERIC_SCALE int,
@.COLLATION_NAME nvarchar(128),
@.CHARACTER_MAXIMUM_LENGTH smallint
DECLARE @.cmd varchar(8000)
SET @.cmd = ''
DECLARE t CURSOR FOR
SELECT c.COLUMN_NAME, c.COLUMN_DEFAULT, c.IS_NULLABLE, c.DATA_TYPE,
c.NUMERIC_PRECISION, c.NUMERIC_SCALE, c.COLLATION_NAME,
c.CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE c.TABLE_SCHEMA = 'dbo'
AND c.TABLE_NAME = 'table1'
ORDER BY c.ORDINAL_POSITION
OPEN t
FETCH NEXT FROM t
INTO @.COLUMN_NAME, @.COLUMN_DEFAULT, @.IS_NULLABLE, @.DATA_TYPE,
@.NUMERIC_PRECISION, @.NUMERIC_SCALE, @.COLLATION_NAME,
@.CHARACTER_MAXIMUM_LENGTH
WHILE @.@.FETCH_STATUS = 0 BEGIN
IF DATALENGTH(@.cmd) <> 0 SET @.cmd = @.cmd + ' ,' + CHAR(10)
SET @.cmd = @.cmd + CHAR(9) + QUOTENAME( @.COLUMN_NAME )+ ' ' + @.DATA_TYPE + '
'
IF @.DATA_TYPE IN ('varchar', 'nvarchar', 'char', 'nchar')
SET @.cmd = @.cmd + '(' + CONVERT(varchar, @.CHARACTER_MAXIMUM_LENGTH) + ') '
IF @.DATA_TYPE = 'decimal' OR @.DATA_TYPE = 'numeric'
SET @.cmd = @.cmd + '(' + CONVERT(varchar, @.NUMERIC_PRECISION) + ', ' +
CONVERT(varchar, @.NUMERIC_SCALE) + ') '
IF NOT @.COLUMN_DEFAULT IS NULL
SET @.cmd = @.cmd + 'DEFAULT ' + @.COLUMN_DEFAULT + ' '
IF NOT @.COLLATION_NAME IS NULL
SET @.cmd = @.cmd + 'COLLATE ' + @.COLLATION_NAME + ' '
IF @.IS_NULLABLE = 'YES'
SET @.cmd = @.cmd + 'NULL '
ELSE
SET @.cmd = @.cmd + 'NOT NULL '
FETCH NEXT FROM t
INTO @.COLUMN_NAME, @.COLUMN_DEFAULT, @.IS_NULLABLE, @.DATA_TYPE,
@.NUMERIC_PRECISION, @.NUMERIC_SCALE, @.COLLATION_NAME,
@.CHARACTER_MAXIMUM_LENGTH
END
CLOSE t
DEALLOCATE t
SET @.cmd = 'CREATE TABLE ' + QUOTENAME('dbo') + '.' + QUOTENAME('table1') +
' (' + CHAR(10)
+ @.cmd + CHAR(10)
+ CHAR(9) + ')'
SELECT @.cmd
GO
DROP TABLE dbo.table1
--<--
CREATE TABLE [dbo].[table1] (
[Id] int NOT NULL ,
[vc] varchar (10) COLLATE Latin1_General_CI_AS NULL ,
[c] char (1) DEFAULT ('a') COLLATE Latin1_General_CI_AS NOT NULL ,
[i] int NULL ,
[d] decimal (18, 4) DEFAULT (0) NOT NULL
)
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
Create a new User
Hello,
How can I create a new user with password for the MS SQL - Server 2000
(and the MSDE) with a SQL - Statement? I use Borland Delphi 5 with the
ADO - Components!
Thanks.
best regards.
Stephan JahrlingSince you referred to password, I assume you are talking about Server logins
instead of database users. To add a new login you can use: sp_addlogin
For an existing login to add a new user to a database, you can use:
sp_adduser
Please refer to SQL Server Books Online for more details on sp_addlogin &
sp_adduser system procedures along with examples
--
- Anith
( Please reply to newsgroups only )sql
Monday, March 19, 2012
Crazy SQL statement help needed... converting the rows to columns (sort of)...
This one isn't so simple.I have a list of training modules, training complete dates and a list of employees in separate tables. I'll give an good example in a second. The problem I am having is that I need to generate a select statement that will generate a kind of 'spreadsheet' that will list the employees in the rows, and columns containing the results in the fields (the training module may or may not have been completed, and thus may or may not be in the result box. I think the example explains it fairly well (note, I did not design the database structure but have to work with it).
Employees table:
empName
Jane Doe
Alton Brown
John Doe
TrainingCourse table:
courseName
Welding
Brain Surgery
Scuba Diving
Results table:
empName: courseName: completeDate:
Jane Doe Welding 2/2/2002
Jane Doe Brain Surgery 3/7/2005
Alton Brown Scuba Diving 9/23/2004
Alton Brown Welding 11/4/2004
John Doe Brain Surgery 6/14/2003
End result of select statement:
Welding Brain Surgery Scuba Diving
Jane Doe 2/2/2002 3/7/2005
Alton Brown 11/4/2004 9/23/2004
John Doe 6/14/2003
Thanks a million to anyone with insight into this. I'm still trying to figure out a way to do this, but after a few days haven't come up with or found anything. Most things I've found online are too simplistic.
--For SQL Server 2005, there is a pivot function to do this
select pt.empName, pt.[Welding]as [Welding], pt.[Brain Surgery]as [Brain Surgery],pt.[Scuba Diving]as [Scuba Diving]
from dbo.coursePivot$as t
pivot(min(t.completeDate)for t.courseNamein([Welding], [Brain Surgery],[Scuba Diving]))as pt
--For SQL Server 2000 use case
SELECT pvt.empName
,MAX(CASE pvt.courseNameWHEN'Welding'THEN completeDateEND)AS [Welding]
,MAX(CASE pvt.courseNameWHEN'Brain Surgery'THEN completeDateEND)AS [Brain Surgery]
,MAX(CASE pvt.courseNameWHEN'Scuba Diving'THEN completeDateEND)AS [Scuba Diving]
FROM dbo.coursePivot$AS pvt
GROUPBY pvt.empName
--Either way, you need hardcode your course names to get the pivot result table.
|||Thank you very much for your reply. I will give this a shot.|||Okay, I just got a chance to try this, but how do I get this to work with multiple tables (I'm using 2000)? All examples I find don't really make that distinction. For example how does it determine where to get the pvt.empName from? Should it be:
SELECT dbo.Employees.empName
,MAX(CASE pvt.courseNameWHEN'Welding'THEN dbo.Results.completeDateEND)AS [Welding]
,MAX(CASE pvt.courseNameWHEN'Brain Surgery'THEN dbo.Results.completeDateEND)AS [Brain Surgery]
,MAX(CASE pvt.courseNameWHEN'Scuba Diving'THEN dbo.Results.completeDateEND)AS [Scuba Diving]
FROM dbo.coursePivot$AS pvt
GROUPBY pvt.empName
Thanks Again.
|||"how do I get this to work with multiple tables (I'm using 2000)? "
Could you post the tables with a set of data along with your expect result? That would be easy to solve and explain. Thanks.
|||One other thing: I get this error "The Query Designer does not support the CASE SQL construct."
|||Yes. But it will run.|||Thanks limno for all your help. Because of the amount of work that has to be done in order to get this to work on the real tables, doing this using a pivot would still require us to run about 5000 queries, and it would remove the dynamic needs of the system, such as having to add new columns manually. My team and I have thought about it and are trying to come up with a way to query the database and create an multidimensional array of the course names and IDs, query the employee database and get all of the employee IDs and names, then finally, query the results table and get the employee ID, the course ID, and date (again, stored in a multidimensional array. I can then come up with a way to compare the values within a function that draws the table (I'll have to figure that out as well). The idea is that memory is cheaper than server processing from an efficiency standpoint.
So I now have two new questions:
1. I know how to store a dataset as an array, but how do I store a dataset as a multidimensional array?
2. (non-Sql): How can I compare, say the second values of multidimensional arrays, meaning if I have two multidimensional arrays, like [1,3] and [7,3], how can I check to see if the two 3's are equal?
Thanks again for all the help so far and to anyone willing to share more insight.
|||Take a look at this article on my site:http://www.theabstractionpoint.com/dynamiccolumns.asp
This is one approach I have used. It is built for SQL Server 2005 (using dynamic SQL and the PIVOT statement available in 2005), but you could adjust it to dynamically generate the CASE statements you've already been shown. It might give you some ideas, at least.
Sunday, March 11, 2012
CR & LF Problems
I'm trying to concatenate three address fields into one for a SELECT
statement. Below is that part:
CASE WHEN ADDR1 IS NULL
THEN ''
ELSE ADDR1 + CHAR(13) + CHAR(10)
END +
CASE WHEN ADDR2 IS NULL
THEN ''
ELSE ADDR2 + CHAR(13) + CHAR(10)
END +
CASE WHEN ADDR3 IS NULL
THEN ''
ELSE ADDR3
END AS Address,
But the results do not have the CR and LF in it. What am I doing
wrong?I don't see a problem here:
create table #tmp (addr1 varchar(20), addr2 varchar(20), addr3 varchar(20))
insert #tmp values ('line 1 field 1', 'line 1 field 2', 'line 1 field 3')
insert #tmp values ('line 2 field 1', 'line 2 field 2', NULL)
insert #tmp values (NULL, 'line 3 field 2', NULL)
SELECT
CASE WHEN ADDR1 IS NULL
THEN ''
ELSE ADDR1 + CHAR(13) + CHAR(10)
END +
CASE WHEN ADDR2 IS NULL
THEN ''
ELSE ADDR2 + CHAR(13) + CHAR(10)
END +
CASE WHEN ADDR3 IS NULL
THEN ''
ELSE ADDR3
END AS Address
FROM #tmp
output:
Address
---
line 1 field 1
line 1 field 2
line 1 field 3
line 2 field 1
line 2 field 2
line 3 field 2
P.S. It sure woulda been nice if you had taken the time to construct the
demo code I did! :-))
--
TheSQLGuru
President
Indicium Resources, Inc.
"Paul" <pwh777@.hotmail.com> wrote in message
news:1178224810.009904.225630@.h2g2000hsg.googlegroups.com...
> I'm trying to concatenate three address fields into one for a SELECT
> statement. Below is that part:
> CASE WHEN ADDR1 IS NULL
> THEN ''
> ELSE ADDR1 + CHAR(13) + CHAR(10)
> END +
> CASE WHEN ADDR2 IS NULL
> THEN ''
> ELSE ADDR2 + CHAR(13) + CHAR(10)
> END +
> CASE WHEN ADDR3 IS NULL
> THEN ''
> ELSE ADDR3
> END AS Address,
> But the results do not have the CR and LF in it. What am I doing
> wrong?
>|||Paul,
If you run this query to return results in a grid in Query Analyzer or the
Management Studio Query editor (depending on version of SQL) the results
appear in a single cell. But if your results return as text you will see
the line breaks.
RLF
"Paul" <pwh777@.hotmail.com> wrote in message
news:1178224810.009904.225630@.h2g2000hsg.googlegroups.com...
> I'm trying to concatenate three address fields into one for a SELECT
> statement. Below is that part:
> CASE WHEN ADDR1 IS NULL
> THEN ''
> ELSE ADDR1 + CHAR(13) + CHAR(10)
> END +
> CASE WHEN ADDR2 IS NULL
> THEN ''
> ELSE ADDR2 + CHAR(13) + CHAR(10)
> END +
> CASE WHEN ADDR3 IS NULL
> THEN ''
> ELSE ADDR3
> END AS Address,
> But the results do not have the CR and LF in it. What am I doing
> wrong?
>
statement. Below is that part:
CASE WHEN ADDR1 IS NULL
THEN ''
ELSE ADDR1 + CHAR(13) + CHAR(10)
END +
CASE WHEN ADDR2 IS NULL
THEN ''
ELSE ADDR2 + CHAR(13) + CHAR(10)
END +
CASE WHEN ADDR3 IS NULL
THEN ''
ELSE ADDR3
END AS Address,
But the results do not have the CR and LF in it. What am I doing
wrong?I don't see a problem here:
create table #tmp (addr1 varchar(20), addr2 varchar(20), addr3 varchar(20))
insert #tmp values ('line 1 field 1', 'line 1 field 2', 'line 1 field 3')
insert #tmp values ('line 2 field 1', 'line 2 field 2', NULL)
insert #tmp values (NULL, 'line 3 field 2', NULL)
SELECT
CASE WHEN ADDR1 IS NULL
THEN ''
ELSE ADDR1 + CHAR(13) + CHAR(10)
END +
CASE WHEN ADDR2 IS NULL
THEN ''
ELSE ADDR2 + CHAR(13) + CHAR(10)
END +
CASE WHEN ADDR3 IS NULL
THEN ''
ELSE ADDR3
END AS Address
FROM #tmp
output:
Address
---
line 1 field 1
line 1 field 2
line 1 field 3
line 2 field 1
line 2 field 2
line 3 field 2
P.S. It sure woulda been nice if you had taken the time to construct the
demo code I did! :-))
--
TheSQLGuru
President
Indicium Resources, Inc.
"Paul" <pwh777@.hotmail.com> wrote in message
news:1178224810.009904.225630@.h2g2000hsg.googlegroups.com...
> I'm trying to concatenate three address fields into one for a SELECT
> statement. Below is that part:
> CASE WHEN ADDR1 IS NULL
> THEN ''
> ELSE ADDR1 + CHAR(13) + CHAR(10)
> END +
> CASE WHEN ADDR2 IS NULL
> THEN ''
> ELSE ADDR2 + CHAR(13) + CHAR(10)
> END +
> CASE WHEN ADDR3 IS NULL
> THEN ''
> ELSE ADDR3
> END AS Address,
> But the results do not have the CR and LF in it. What am I doing
> wrong?
>|||Paul,
If you run this query to return results in a grid in Query Analyzer or the
Management Studio Query editor (depending on version of SQL) the results
appear in a single cell. But if your results return as text you will see
the line breaks.
RLF
"Paul" <pwh777@.hotmail.com> wrote in message
news:1178224810.009904.225630@.h2g2000hsg.googlegroups.com...
> I'm trying to concatenate three address fields into one for a SELECT
> statement. Below is that part:
> CASE WHEN ADDR1 IS NULL
> THEN ''
> ELSE ADDR1 + CHAR(13) + CHAR(10)
> END +
> CASE WHEN ADDR2 IS NULL
> THEN ''
> ELSE ADDR2 + CHAR(13) + CHAR(10)
> END +
> CASE WHEN ADDR3 IS NULL
> THEN ''
> ELSE ADDR3
> END AS Address,
> But the results do not have the CR and LF in it. What am I doing
> wrong?
>
Friday, February 24, 2012
coverting conditional statements to sql
hi all
I am building an application where the user enters a conditional statement and then i want to perform some action.
Eg
user input > if customer.name = sandro then send email
My program will read the user input and then constructs an sql statement to retrieve the required info... if the sql returns data then a separate method is invoked.
My problem is to transform this statement into the appropriate SQL...since this need to be constructed at run time and for any type of user input..
The language i am using is java, however i dont expect any code...I was only wondering if someone has any ideas of how this can be done..
Thank you in anticipation.What language are you writing the application in ?|||Originally posted by rnealejr
What language are you writing the application in ?
I am using JAVA...|||What type of user input are you expecting ? - give some examples. What input from the user will the sql statements be based on ?|||Originally posted by rnealejr
What type of user input are you expecting ? - give some examples. What input from the user will the sql statements be based on ?
The users are expected to choose from a form which will display the available table attributes and the conjunctives AND OR and NOT.
eg of the typical input
If customer.name = "sandro" and department.name = "maintence" then send email.
what i need to do is to dynamically generate an sql that extracts the required info (based on the user input)......... if the dataset returned from the generated query contains data then this implies that the conditional part succeeds... and in turn i can invoke a method which sends the email...
Hope this is clear.........
Thank you for your interest
Sandro|||After the user makes the selections just do an executeQuery within java with the string that you create. So you might separate the sql statement into a select, from, where ... strings and combine these and pass to the executeQuery using the parameter selection by the user. By separating the parts of the sql statement you can create reusable components. Do you need to know the contents of the returned result set or just that something exists ? If it is the latter, just do a select count(*) to validate. I am a little unclear as to what your problem is, so I hope this answers your question.|||Originally posted by rnealejr
After the user makes the selections just do an executeQuery within java with the string that you create. So you might separate the sql statement into a select, from, where ... strings and combine these and pass to the executeQuery using the parameter selection by the user. By separating the parts of the sql statement you can create reusable components. Do you need to know the contents of the returned result set or just that something exists ? If it is the latter, just do a select count(*) to validate. I am a little unclear as to what your problem is, so I hope this answers your question.
Thank you very much for your advice...... it s pretty much similar to what i had in mind...
By any chance do u know how i can calculate the genereted query computational complexity? i.e. its order O()
I am building an application where the user enters a conditional statement and then i want to perform some action.
Eg
user input > if customer.name = sandro then send email
My program will read the user input and then constructs an sql statement to retrieve the required info... if the sql returns data then a separate method is invoked.
My problem is to transform this statement into the appropriate SQL...since this need to be constructed at run time and for any type of user input..
The language i am using is java, however i dont expect any code...I was only wondering if someone has any ideas of how this can be done..
Thank you in anticipation.What language are you writing the application in ?|||Originally posted by rnealejr
What language are you writing the application in ?
I am using JAVA...|||What type of user input are you expecting ? - give some examples. What input from the user will the sql statements be based on ?|||Originally posted by rnealejr
What type of user input are you expecting ? - give some examples. What input from the user will the sql statements be based on ?
The users are expected to choose from a form which will display the available table attributes and the conjunctives AND OR and NOT.
eg of the typical input
If customer.name = "sandro" and department.name = "maintence" then send email.
what i need to do is to dynamically generate an sql that extracts the required info (based on the user input)......... if the dataset returned from the generated query contains data then this implies that the conditional part succeeds... and in turn i can invoke a method which sends the email...
Hope this is clear.........
Thank you for your interest
Sandro|||After the user makes the selections just do an executeQuery within java with the string that you create. So you might separate the sql statement into a select, from, where ... strings and combine these and pass to the executeQuery using the parameter selection by the user. By separating the parts of the sql statement you can create reusable components. Do you need to know the contents of the returned result set or just that something exists ? If it is the latter, just do a select count(*) to validate. I am a little unclear as to what your problem is, so I hope this answers your question.|||Originally posted by rnealejr
After the user makes the selections just do an executeQuery within java with the string that you create. So you might separate the sql statement into a select, from, where ... strings and combine these and pass to the executeQuery using the parameter selection by the user. By separating the parts of the sql statement you can create reusable components. Do you need to know the contents of the returned result set or just that something exists ? If it is the latter, just do a select count(*) to validate. I am a little unclear as to what your problem is, so I hope this answers your question.
Thank you very much for your advice...... it s pretty much similar to what i had in mind...
By any chance do u know how i can calculate the genereted query computational complexity? i.e. its order O()
Sunday, February 19, 2012
Counting unique entries in a SQL Statement
I have a complicated problem, and I'm new to SQL so any help would be greatly appreciated.
I am creating an export file (fixed width) that contains a breakdown of items on an invoice, and each "export file" can contain many invoices. The problem is that I need to apply an incremental "invoice" count on each line. This isn't as simple as doing a running sum of "1" on each record, because the first 5 rows may all be on the same invoice, and all rows need to be identified as being associated with "invoice 1". The next invoice will be known as "invoice 2" and again may contain many rows, all requiring "invoice 2".
Does this make sense?
EG.: I am shipping products, and the breakdown is: Vessel, Voyage, Invoice No, Product, Mark.....
SAGMIR 025 001 HEM/FIR HLF550...
SAGMIR 025 001 HEM/FIR KILN-D HLF505...
SAGMIR 025 002 HEM/FIR HLF660....
The SQL statement that produces the above is a Select query with a grouping on VES/VOY/BL_ID/PRO/MARK where the "BL_ID" indicates they are on the same invoice, but is not the incremental number I require. Complicated, i know...
Thanks in advance for anyone who can help.....if this explanation isn't clear please tell me!
Michael
Yes, this is not clear to me yet. Could you explain this a bit in detail ? There is a function in SQL Server which can produce a rank based on several columns. The example for procuding such number would be to get new running number for Order Details per order number. Is is this what you are trying to do ?HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de|||Your best bet is probably a temporary table... and then join back to it.something like the following :
create table #tempinv
(
invid int identity(1,1),
bl_id int not null
)
insert into #tempinv
(
bl_id
)
select distinct BL_ID from invoices
order by BL_ID
select i.VES, i.VOY, t.invid, i.PRO, i.MARK
from invoices i
inner join #tempinv t on t.bl_id = i.BL_ID
order by i.BL_ID
drop table #tempinv
I think this is what you want....
Counting rows by a dynamic SQL statements
I need to count number of record return by a SQL statement. Any idea to do
this?
This is the sample SQL. You can try in Northwind database.
The subquery inside COUNT() expression actually is a dynamic sql which I may
change it anytime. My primary purpose is to get number of record after I
executed a dynamic SQL statement. The query below defintely cannot work
because COUNT() doesn;t take subquery.
At first, I try to use @.@.rowcount which return the number of record. Besides
using @.@.rowcount, any other T-SQL can be used?
DECLARE @.cnt int
SELECT @.cnt=COUNT(
SELECT Customers.ContactName
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID
WHERE Customers.Country <> 'USA' AND Customers.Country <> 'Mexico'
GROUP BY Customers.CustomerID, Customers.ContactName, Customers.Address,
Customers.City, Customers.Country
HAVING (SUM([Order Details].UnitPrice*[Order Details].Quantity))>1000
)You can specify your SQL query as a derived table and use sp_executesql to
return the count variable as an output parameter. This will return the
count without the accompanying data:
USE Northwind
DECLARE @.cnt int
EXEC sp_executesql
N'
SELECT @.cnt = COUNT(*)
FROM (
SELECT Customers.ContactName
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID
WHERE Customers.Country <> ''USA'' AND Customers.Country <> ''Mexico''
GROUP BY Customers.CustomerID, Customers.ContactName, Customers.Address,
Customers.City, Customers.Country
HAVING (SUM([Order Details].UnitPrice*[Order Details].Quantity))>1000
) AS t
',
N'@.cnt int OUT',
@.cnt OUT
SELECT @.cnt
Use a similar technique to get both the resultset and count:
USE Northwind
DECLARE @.cnt int
EXEC sp_executesql
N'
SELECT Customers.ContactName
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID
WHERE Customers.Country <> ''USA'' AND Customers.Country <> ''Mexico''
GROUP BY Customers.CustomerID, Customers.ContactName, Customers.Address,
Customers.City, Customers.Country
HAVING (SUM([Order Details].UnitPrice*[Order Details].Quantity))>1000
SET @.cnt = @.@.ROWCOUNT
',
N'@.cnt int OUT',
@.cnt OUT
SELECT @.cnt
Hope this helps.
Dan Guzman
SQL Server MVP
"Joel Leong" <ch_leong@.hotmail.com> wrote in message
news:ehYf%23NOHFHA.3944@.TK2MSFTNGP10.phx.gbl...
>I need to count number of record return by a SQL statement. Any idea to do
>this?
> This is the sample SQL. You can try in Northwind database.
> The subquery inside COUNT() expression actually is a dynamic sql which I
> may change it anytime. My primary purpose is to get number of record after
> I executed a dynamic SQL statement. The query below defintely cannot work
> because COUNT() doesn;t take subquery.
> At first, I try to use @.@.rowcount which return the number of record.
> Besides using @.@.rowcount, any other T-SQL can be used?
>
> DECLARE @.cnt int
> SELECT @.cnt=COUNT(
> SELECT Customers.ContactName
> FROM Customers
> INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
> INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID
> WHERE Customers.Country <> 'USA' AND Customers.Country <> 'Mexico'
> GROUP BY Customers.CustomerID, Customers.ContactName, Customers.Address,
> Customers.City, Customers.Country
> HAVING (SUM([Order Details].UnitPrice*[Order Details].Quantity))>1000
> )
>
this?
This is the sample SQL. You can try in Northwind database.
The subquery inside COUNT() expression actually is a dynamic sql which I may
change it anytime. My primary purpose is to get number of record after I
executed a dynamic SQL statement. The query below defintely cannot work
because COUNT() doesn;t take subquery.
At first, I try to use @.@.rowcount which return the number of record. Besides
using @.@.rowcount, any other T-SQL can be used?
DECLARE @.cnt int
SELECT @.cnt=COUNT(
SELECT Customers.ContactName
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID
WHERE Customers.Country <> 'USA' AND Customers.Country <> 'Mexico'
GROUP BY Customers.CustomerID, Customers.ContactName, Customers.Address,
Customers.City, Customers.Country
HAVING (SUM([Order Details].UnitPrice*[Order Details].Quantity))>1000
)You can specify your SQL query as a derived table and use sp_executesql to
return the count variable as an output parameter. This will return the
count without the accompanying data:
USE Northwind
DECLARE @.cnt int
EXEC sp_executesql
N'
SELECT @.cnt = COUNT(*)
FROM (
SELECT Customers.ContactName
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID
WHERE Customers.Country <> ''USA'' AND Customers.Country <> ''Mexico''
GROUP BY Customers.CustomerID, Customers.ContactName, Customers.Address,
Customers.City, Customers.Country
HAVING (SUM([Order Details].UnitPrice*[Order Details].Quantity))>1000
) AS t
',
N'@.cnt int OUT',
@.cnt OUT
SELECT @.cnt
Use a similar technique to get both the resultset and count:
USE Northwind
DECLARE @.cnt int
EXEC sp_executesql
N'
SELECT Customers.ContactName
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID
WHERE Customers.Country <> ''USA'' AND Customers.Country <> ''Mexico''
GROUP BY Customers.CustomerID, Customers.ContactName, Customers.Address,
Customers.City, Customers.Country
HAVING (SUM([Order Details].UnitPrice*[Order Details].Quantity))>1000
SET @.cnt = @.@.ROWCOUNT
',
N'@.cnt int OUT',
@.cnt OUT
SELECT @.cnt
Hope this helps.
Dan Guzman
SQL Server MVP
"Joel Leong" <ch_leong@.hotmail.com> wrote in message
news:ehYf%23NOHFHA.3944@.TK2MSFTNGP10.phx.gbl...
>I need to count number of record return by a SQL statement. Any idea to do
>this?
> This is the sample SQL. You can try in Northwind database.
> The subquery inside COUNT() expression actually is a dynamic sql which I
> may change it anytime. My primary purpose is to get number of record after
> I executed a dynamic SQL statement. The query below defintely cannot work
> because COUNT() doesn;t take subquery.
> At first, I try to use @.@.rowcount which return the number of record.
> Besides using @.@.rowcount, any other T-SQL can be used?
>
> DECLARE @.cnt int
> SELECT @.cnt=COUNT(
> SELECT Customers.ContactName
> FROM Customers
> INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
> INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID
> WHERE Customers.Country <> 'USA' AND Customers.Country <> 'Mexico'
> GROUP BY Customers.CustomerID, Customers.ContactName, Customers.Address,
> Customers.City, Customers.Country
> HAVING (SUM([Order Details].UnitPrice*[Order Details].Quantity))>1000
> )
>
Friday, February 17, 2012
Counting result
Hi Champs!
I have a tricky sql problem, and I would apreciate some help from you.
In my SELECT statement my result looks like this:
ID--name--city--number
1 -- lfkjdfl--djdjjd--1212
2-- mdsf-- safta--1212
3--jsert--agjyt--1212
4--wqfh--jfgyiu--3434
5--aghj--jqqre--3434
But now I want a added column, in my SELECT result, so it would look like:
ID--name--city--number--newColumn
1 -- lfkjdfl--djdjjd--1212--1
2-- mdsf-- safta--1212--2
3--jsert--agjyt--1212--3
4--wqfh--jfgyiu--3434--1
5--aghj--jqqre--3434--2
How could I produce the "newColumn" ?
Thanks
KurlanSELECT A.id, A.name, A.city, A.number, COUNT(*)
FROM YourTable AS A
JOIN YourTable AS B
ON A.number = B.number
AND A.id >= B.id
GROUP BY A.id, A.name, A.city, A.number
David Portas
SQL Server MVP
--|||There are several ways.. One which uses a correlated subquery is as follows
:
Select Id, Name,City, Number,
(Select Count(*) From Table
Where number = T.Number
And Id <= T.Id)
From Table T
Order By Id
Other approaches use either a Temp Table, or a table variable, or a Created
View to hold the Sount values, and then join that back to the main table to
output the resutls...
"Kutlan" wrote:
> Hi Champs!
> I have a tricky sql problem, and I would apreciate some help from you.
> In my SELECT statement my result looks like this:
> ID--name--city--number
> 1 -- lfkjdfl--djdjjd--1212
> 2-- mdsf-- safta--1212
> 3--jsert--agjyt--1212
> 4--wqfh--jfgyiu--3434
> 5--aghj--jqqre--3434
>
> But now I want a added column, in my SELECT result, so it would look like:
> ID--name--city--number--newColumn
> 1 -- lfkjdfl--djdjjd--1212--1
> 2-- mdsf-- safta--1212--2
> 3--jsert--agjyt--1212--3
> 4--wqfh--jfgyiu--3434--1
> 5--aghj--jqqre--3434--2
> How could I produce the "newColumn" ?
> Thanks
> Kurlan|||Thank You!!! this really saved my day
"CBretana" wrote:
> There are several ways.. One which uses a correlated subquery is as follo
ws:
> Select Id, Name,City, Number,
> (Select Count(*) From Table
> Where number = T.Number
> And Id <= T.Id)
> From Table T
> Order By Id
>
> Other approaches use either a Temp Table, or a table variable, or a Create
d
> View to hold the Sount values, and then join that back to the main table t
o
> output the resutls...
>
>
> "Kutlan" wrote:
>|||Thank You!!! this really saved my day
"David Portas" wrote:
> SELECT A.id, A.name, A.city, A.number, COUNT(*)
> FROM YourTable AS A
> JOIN YourTable AS B
> ON A.number = B.number
> AND A.id >= B.id
> GROUP BY A.id, A.name, A.city, A.number
> --
> David Portas
> SQL Server MVP
> --
>
>|||Yr welcome !
"Kutlan" wrote:
> Thank You!!! this really saved my day
>
> "CBretana" wrote:
>|||Not that it helps you now, but this would be so much simpler and more
efficient in SQL Server 2005:
SELECT *, ROW_NUMBER() OVER(PARTITION BY number ORDER BY ID) AS rn
FROM T1
:-)
BG, SQL Server MVP
www.SolidQualityLearning.com
"Kutlan" <Kutlan@.discussions.microsoft.com> wrote in message
news:C86612FB-17E9-4B54-BD03-CCCC113FE8A0@.microsoft.com...
> Hi Champs!
> I have a tricky sql problem, and I would apreciate some help from you.
> In my SELECT statement my result looks like this:
> ID--name--city--number
> 1 -- lfkjdfl--djdjjd--1212
> 2-- mdsf-- safta--1212
> 3--jsert--agjyt--1212
> 4--wqfh--jfgyiu--3434
> 5--aghj--jqqre--3434
>
> But now I want a added column, in my SELECT result, so it would look like:
> ID--name--city--number--newColumn
> 1 -- lfkjdfl--djdjjd--1212--1
> 2-- mdsf-- safta--1212--2
> 3--jsert--agjyt--1212--3
> 4--wqfh--jfgyiu--3434--1
> 5--aghj--jqqre--3434--2
> How could I produce the "newColumn" ?
> Thanks
> Kurlan|||It would be much simpler with the RAC utility for S2k NOW:).
Sql99 ranking functions row_number,rank and dense_rank with
partitioning/ordering
have been in available in RAC for years!...and some additional functions MS
obviously
hasen't thought about :)...scary:)
www.rac4sql.net
"Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in message
news:%23G6iw5RWFHA.3188@.TK2MSFTNGP09.phx.gbl...
> Not that it helps you now, but this would be so much simpler and more
> efficient in SQL Server 2005:
> SELECT *, ROW_NUMBER() OVER(PARTITION BY number ORDER BY ID) AS rn
> FROM T1
> :-)
I have a tricky sql problem, and I would apreciate some help from you.
In my SELECT statement my result looks like this:
ID--name--city--number
1 -- lfkjdfl--djdjjd--1212
2-- mdsf-- safta--1212
3--jsert--agjyt--1212
4--wqfh--jfgyiu--3434
5--aghj--jqqre--3434
But now I want a added column, in my SELECT result, so it would look like:
ID--name--city--number--newColumn
1 -- lfkjdfl--djdjjd--1212--1
2-- mdsf-- safta--1212--2
3--jsert--agjyt--1212--3
4--wqfh--jfgyiu--3434--1
5--aghj--jqqre--3434--2
How could I produce the "newColumn" ?
Thanks
KurlanSELECT A.id, A.name, A.city, A.number, COUNT(*)
FROM YourTable AS A
JOIN YourTable AS B
ON A.number = B.number
AND A.id >= B.id
GROUP BY A.id, A.name, A.city, A.number
David Portas
SQL Server MVP
--|||There are several ways.. One which uses a correlated subquery is as follows
:
Select Id, Name,City, Number,
(Select Count(*) From Table
Where number = T.Number
And Id <= T.Id)
From Table T
Order By Id
Other approaches use either a Temp Table, or a table variable, or a Created
View to hold the Sount values, and then join that back to the main table to
output the resutls...
"Kutlan" wrote:
> Hi Champs!
> I have a tricky sql problem, and I would apreciate some help from you.
> In my SELECT statement my result looks like this:
> ID--name--city--number
> 1 -- lfkjdfl--djdjjd--1212
> 2-- mdsf-- safta--1212
> 3--jsert--agjyt--1212
> 4--wqfh--jfgyiu--3434
> 5--aghj--jqqre--3434
>
> But now I want a added column, in my SELECT result, so it would look like:
> ID--name--city--number--newColumn
> 1 -- lfkjdfl--djdjjd--1212--1
> 2-- mdsf-- safta--1212--2
> 3--jsert--agjyt--1212--3
> 4--wqfh--jfgyiu--3434--1
> 5--aghj--jqqre--3434--2
> How could I produce the "newColumn" ?
> Thanks
> Kurlan|||Thank You!!! this really saved my day
"CBretana" wrote:
> There are several ways.. One which uses a correlated subquery is as follo
ws:
> Select Id, Name,City, Number,
> (Select Count(*) From Table
> Where number = T.Number
> And Id <= T.Id)
> From Table T
> Order By Id
>
> Other approaches use either a Temp Table, or a table variable, or a Create
d
> View to hold the Sount values, and then join that back to the main table t
o
> output the resutls...
>
>
> "Kutlan" wrote:
>|||Thank You!!! this really saved my day
"David Portas" wrote:
> SELECT A.id, A.name, A.city, A.number, COUNT(*)
> FROM YourTable AS A
> JOIN YourTable AS B
> ON A.number = B.number
> AND A.id >= B.id
> GROUP BY A.id, A.name, A.city, A.number
> --
> David Portas
> SQL Server MVP
> --
>
>|||Yr welcome !
"Kutlan" wrote:
> Thank You!!! this really saved my day
>
> "CBretana" wrote:
>|||Not that it helps you now, but this would be so much simpler and more
efficient in SQL Server 2005:
SELECT *, ROW_NUMBER() OVER(PARTITION BY number ORDER BY ID) AS rn
FROM T1
:-)
BG, SQL Server MVP
www.SolidQualityLearning.com
"Kutlan" <Kutlan@.discussions.microsoft.com> wrote in message
news:C86612FB-17E9-4B54-BD03-CCCC113FE8A0@.microsoft.com...
> Hi Champs!
> I have a tricky sql problem, and I would apreciate some help from you.
> In my SELECT statement my result looks like this:
> ID--name--city--number
> 1 -- lfkjdfl--djdjjd--1212
> 2-- mdsf-- safta--1212
> 3--jsert--agjyt--1212
> 4--wqfh--jfgyiu--3434
> 5--aghj--jqqre--3434
>
> But now I want a added column, in my SELECT result, so it would look like:
> ID--name--city--number--newColumn
> 1 -- lfkjdfl--djdjjd--1212--1
> 2-- mdsf-- safta--1212--2
> 3--jsert--agjyt--1212--3
> 4--wqfh--jfgyiu--3434--1
> 5--aghj--jqqre--3434--2
> How could I produce the "newColumn" ?
> Thanks
> Kurlan|||It would be much simpler with the RAC utility for S2k NOW:).
Sql99 ranking functions row_number,rank and dense_rank with
partitioning/ordering
have been in available in RAC for years!...and some additional functions MS
obviously
hasen't thought about :)...scary:)
www.rac4sql.net
"Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in message
news:%23G6iw5RWFHA.3188@.TK2MSFTNGP09.phx.gbl...
> Not that it helps you now, but this would be so much simpler and more
> efficient in SQL Server 2005:
> SELECT *, ROW_NUMBER() OVER(PARTITION BY number ORDER BY ID) AS rn
> FROM T1
> :-)
Tuesday, February 14, 2012
counting distint records
i have an sql statement like this:
SELECT distinct store, dept, sku
FROM some_table
WHERE some_condition
i simply want to modify it to give me the count, i tried this, but it doesn't work:
SELECT count (distinct store, dept, sku)
FROM some_table
WHERE some_condition
what am i doing wrong? thank you very much for your help.This is a pure guess, but do you want something like:SELECT Count(*), store, dept, sku
FROM some_table
WHERE 1 = 1 -- or some other condition of your choosing
GROUP BY store, dept, sku-PatP|||thanks for the help, yeah i think that will work, but i ended up doing something like this:
SELECT count(1)
FROM ( SELECT distinct store, dept, sku
FROM some_table
WHERE some_condition
)
SELECT distinct store, dept, sku
FROM some_table
WHERE some_condition
i simply want to modify it to give me the count, i tried this, but it doesn't work:
SELECT count (distinct store, dept, sku)
FROM some_table
WHERE some_condition
what am i doing wrong? thank you very much for your help.This is a pure guess, but do you want something like:SELECT Count(*), store, dept, sku
FROM some_table
WHERE 1 = 1 -- or some other condition of your choosing
GROUP BY store, dept, sku-PatP|||thanks for the help, yeah i think that will work, but i ended up doing something like this:
SELECT count(1)
FROM ( SELECT distinct store, dept, sku
FROM some_table
WHERE some_condition
)
Counting all the characters in a column
I'm a SQL newbie...'nuf said.
How do I use a SELECT statement to count all the characters in a given column? I would like to calcuate the total number of characters, the average length, etc.
I can use the LEN function to get a single row like this:
SELECTLEN(NAME1)
FROM INDEXINFO
WHERE NDX = 101
I played around with the COUNT function but that seems to be for counting rows. In VB I would use a FOR EACH clause and keep a running total for the result of the LEN function. Is this a problem better handled using a T-SQL script or can it be done with SELECT statement?
Thanks!
DeBug
Umm...I changed my key words in my Google search and figured out the following:
SELECTSUM(LEN(NAME1))FROM INDEXINFO
WHERE NDX < 100
Ok, this got me the total and I can of course change the WHERE clause but can I also get the AVE at the same time?
|||SELECT SUM(...), AVG(...) FROM INDEXINFOWHERE NDX < 100|||
There are problems with using LEN(). See this thread for a recent discussion.
You could do the following:
Code Snippet
SELECT
ColumnLength = max( datalength( Name1 )),
ColumnAvg = avg( datalength( Name1 ))
FROM IndexInfo
WHERE NDX = 101|||Thank you for your reply!|||
Hi Arnie,
You have to take in mind if the column is a unicode one, because for those, every character is 2 bytes.
AMB
|||Arnie,
Thank you and Phil for helping me with this. Here is the final version:
SELECT
Name1 =sum(datalength( Name1 )),
Name2 =sum(datalength( name2 )),
Name3 =sum(datalength( name3 )),
Name4 =sum(datalength( name4 )),
Date =sum(datalength( date ))
FROM IndexInfo
SELECT
Name1 =avg(datalength( Name1 )),
Name2 =avg(datalength( name2 )),
Name3 =avg(datalength( name3 )),
Name4 =avg(datalength( name4 )),
Date =avg(datalength( date ))
FROM IndexInfo
btw, how do you do that code snippet?|||
I must have trailing spaces somewhere....I used both the len and datalength for the results to text:
Name1 Name2 Name3 Name4 Date
-- -- -- -- --
644653 1047334 59933 69820 888709
(1 row(s) affected)
Name1 Name2 Name3 Name4 Date
-- -- -- -- --
644597 1047332 59933 69820 888709
|||Look for the rows where the LEN() and DATALENGTH() don't match.|||Great Reminder Alejandro,
DATALENGTH() reports the number of bytes used to store the field value, so when using DATALENGTH() with unicode (nchar(), nvarchar()) datatypes, it is necessary to divide by two if you are handling single byte characters.
|||I wrapped the blanks in a delimiter to make it show a little better. I now need to review the data entry code to see why it let someone key all those blank spaces 
Thanks again to all that helped!
DeBug
SELECT NDX, MY_BLANKS = ('-->' + NAME1 + '<--') FROM INDEXINFO
WHERE DATALENGTH(NAME1) > LEN(NAME1);
NDX MY_BLANKS
-- --
263 --> <--
427 --> <--
(2 row(s) affected)
counter inside select statement?
Hi, can you add a counter inside a select statement to get a unique id line of the rows?
In my forum i have a page that displays a users past posts in the forum, these are first sorted according to their topicID and then they are sorted after creation date. What i want is in the select statement is to create a counter to get a new numeric order.
This is the normal way:
SELECT id, post, comment, created FROM forum_posts WHERE (topicID = @.topicID) ... some more where/order by statements
This is what i want:
DECLARE @.tempCounter bigint
SET @.tempCounter = 0
SELECT @.tempCounter, id, post, comment, created FROM forum_posts WHERE (topicID = @.topicID)... some more where/order by statements and at the end.. (SELECT @.tempCounter = @.tempCounter + 1)
Anyone know if this can be done?
Use Row Num ( if you are using SQL SErver 2005 ) as
Select ROW_NUMBER() OVER(ORDER BY Some_Field ) AS rownum,
* from Table_Nane
yes i am using MS SQL 2005, it worked for half of the problem, ill display the SP below, what the SP does is to select the posts from a single user, by ordering them first by topicID then by date created.
In order to bring back just what the client need, i incorperated a custom paging system.
DECLARE @.first_idint, @.startRowint
SET @.startRowIndex=(@.startRowIndex- 1)* @.maximumRows+ 1
IF @.startRowIndex= 0
SET @.startRowIndex= 1
SETROWCOUNT @.startRowIndex
SELECT @.first_id=ROW_NUMBER()OVER(ORDERBY forum_answer.topicidASC, forum_answer.idDESC)FROM forum_answerINNERJOIN forum_topicsON forum_topics.id= forum_answer.topicidINNERJOIN forum_boardON forum_board.id= forum_topics.boardidAND forum_board.hidden= 0INNERJOIN forumON forum.id= forum_board.forumidAND forum.hidden= 0RIGHTOUTERJOIN profile_publicinfoON profile_publicinfo.username= forum_answer.usernameWHERE(forum_answer.username= @.UserName)ORDERBY forum_answer.topicidASC, forum_answer.idDESC
SETROWCOUNT @.maximumRows
DECLARE @.tempVarint
SELECT @.tempVar=ROW_NUMBER()OVER(ORDERBY forum_answer.topicidASC, forum_answer.idDESC), forum_answer.topicid, forum_answer.id, forum_answer.username, forum_answer.answer, forum_answer.created, profile_publicinfo.signatureFROM forum_answerINNERJOIN forum_topicsON forum_topics.id= forum_answer.topicidINNERJOIN forum_boardON forum_board.id= forum_topics.boardidAND forum_board.hidden= 0INNERJOIN forumON forum.id= forum_board.forumidAND forum.hidden= 0RIGHTOUTERJOIN profile_publicinfoON profile_publicinfo.username= forum_answer.usernameWHERE(forum_answer.username= @.UserName)AND(@.first_id<= @.tempVar)ORDERBY forum_answer.topicidASC, forum_answer.idDESC
SETROWCOUNT 0
The first thing we do, is to get the id for the first post that should be returned. This works with the ROW_NUMBER method, the second select statement, takes xx number of rows with start from the position it recieves from the first select statement. So if @.first_id is 5 and maximumRows is 5, then the second select statement will only take rows 5->10 from the table.
The problem is the ROW_NUMBER inside the second select statement, sql screams if i put it at the end like (@.first_id <= ROW_NUMBER...Msg 4108, Level 15, State 1, Procedure Forum_GetUserAnswers, Line 32
Windowed functions can only appear in the SELECT or ORDER BY clauses.
SQL also screams if i put it in the beginning (as shown above), then i get the following error:
Msg 141, Level 15, State 1, Procedure Forum_GetUserAnswers, Line 33
A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.
Counter in a select statement
How do I get a counter in a select statement output
Say if I do
Select counter, col1, col2 from table1
The output should be
Counter Col1 Col2
1 val11 val21
2 val12 val22
3 val13 val23
4 val14 val24See this thread from yesterday
http://groups.google.com/group/micr...1eb03a01b9a15af
<balacr@.gmail.com> wrote in message
news:1127282527.952182.230990@.g14g2000cwa.googlegroups.com...
> How do I get a counter in a select statement output
>
> Say if I do
> Select counter, col1, col2 from table1
>
> The output should be
>
> Counter Col1 Col2
> 1 val11 val21
> 2 val12 val22
> 3 val13 val23
> 4 val14 val24
>|||Hi,
this is quite easy, can also be found under:
http://support.microsoft.com/defaul...b;EN-US;q186133
CREATE TABLE cols
(
col1 varchar(20),
col2 varchar(20)
)
INSERt INTO cols (col1,col2)
SELECT 'val11','val21'
INSERt INTO cols (col1,col2)
SELECT 'val12','val22'
INSERt INTO cols (col1,col2)
SELECT 'val13','val23'
INSERt INTO cols (col1,col2)
SELECT 'val14','val24'
select rank=count(*), c1.col1, c1.col2
from cols c1, cols c2
where c1.col1 + c1.col2 >= c2.col1 + c2.col2
group by c1.col1, c1.col2
order by 1
DROP Table cols
HTH, Jens Suessmeyer.|||Jens,
This will not give the expected result if there are 2 rows with the
same values for all columns :(
Thanks for you help|||Hi
I assumed that col1 is a PRIMARY KEY
CREATE TABLE cols
(
col1 varchar(20) NOT NULL PRIMARY KEY,
col2 varchar(20)
)
INSERt INTO cols (col1,col2)
SELECT 'val11','val21'
INSERt INTO cols (col1,col2)
SELECT 'val12','val22'
INSERt INTO cols (col1,col2)
SELECT 'val13','val23'
INSERt INTO cols (col1,col2)
SELECT 'val14','val24'
SELECT *,(SELECT COUNT(*) FROM cols C WHERE C.col1<=cols.col1)rank
FROM cols ORDER BY rank
<balacr@.gmail.com> wrote in message
news:1127285371.485317.274560@.g43g2000cwa.googlegroups.com...
> Jens,
> This will not give the expected result if there are 2 rows with the
> same values for all columns :(
> Thanks for you help
>|||You need a unique combination of columns for that, otherwise you could
pump the data in a temp table with an identity column which will insert
a increasing number on its own.
HTH, Jens Suessmeyer.|||select FirstName,LastName,RowNo=(SELECT count(*) from employeesa1 t2
where t1.empid<=t2.empid)from employeesa1 t1
order by rowno asc|||Create a temp table or declare a table variable with an identity column &
insert ur data into that. Use this table...
This is the only solution for cases where u hv no unique keys
Rakesh
"balacr@.gmail.com" wrote:
> How do I get a counter in a select statement output
>
> Say if I do
> Select counter, col1, col2 from table1
>
> The output should be
>
> Counter Col1 Col2
> 1 val11 val21
> 2 val12 val22
> 3 val13 val23
> 4 val14 val24
>
Say if I do
Select counter, col1, col2 from table1
The output should be
Counter Col1 Col2
1 val11 val21
2 val12 val22
3 val13 val23
4 val14 val24See this thread from yesterday
http://groups.google.com/group/micr...1eb03a01b9a15af
<balacr@.gmail.com> wrote in message
news:1127282527.952182.230990@.g14g2000cwa.googlegroups.com...
> How do I get a counter in a select statement output
>
> Say if I do
> Select counter, col1, col2 from table1
>
> The output should be
>
> Counter Col1 Col2
> 1 val11 val21
> 2 val12 val22
> 3 val13 val23
> 4 val14 val24
>|||Hi,
this is quite easy, can also be found under:
http://support.microsoft.com/defaul...b;EN-US;q186133
CREATE TABLE cols
(
col1 varchar(20),
col2 varchar(20)
)
INSERt INTO cols (col1,col2)
SELECT 'val11','val21'
INSERt INTO cols (col1,col2)
SELECT 'val12','val22'
INSERt INTO cols (col1,col2)
SELECT 'val13','val23'
INSERt INTO cols (col1,col2)
SELECT 'val14','val24'
select rank=count(*), c1.col1, c1.col2
from cols c1, cols c2
where c1.col1 + c1.col2 >= c2.col1 + c2.col2
group by c1.col1, c1.col2
order by 1
DROP Table cols
HTH, Jens Suessmeyer.|||Jens,
This will not give the expected result if there are 2 rows with the
same values for all columns :(
Thanks for you help|||Hi
I assumed that col1 is a PRIMARY KEY
CREATE TABLE cols
(
col1 varchar(20) NOT NULL PRIMARY KEY,
col2 varchar(20)
)
INSERt INTO cols (col1,col2)
SELECT 'val11','val21'
INSERt INTO cols (col1,col2)
SELECT 'val12','val22'
INSERt INTO cols (col1,col2)
SELECT 'val13','val23'
INSERt INTO cols (col1,col2)
SELECT 'val14','val24'
SELECT *,(SELECT COUNT(*) FROM cols C WHERE C.col1<=cols.col1)rank
FROM cols ORDER BY rank
<balacr@.gmail.com> wrote in message
news:1127285371.485317.274560@.g43g2000cwa.googlegroups.com...
> Jens,
> This will not give the expected result if there are 2 rows with the
> same values for all columns :(
> Thanks for you help
>|||You need a unique combination of columns for that, otherwise you could
pump the data in a temp table with an identity column which will insert
a increasing number on its own.
HTH, Jens Suessmeyer.|||select FirstName,LastName,RowNo=(SELECT count(*) from employeesa1 t2
where t1.empid<=t2.empid)from employeesa1 t1
order by rowno asc|||Create a temp table or declare a table variable with an identity column &
insert ur data into that. Use this table...
This is the only solution for cases where u hv no unique keys
Rakesh
"balacr@.gmail.com" wrote:
> How do I get a counter in a select statement output
>
> Say if I do
> Select counter, col1, col2 from table1
>
> The output should be
>
> Counter Col1 Col2
> 1 val11 val21
> 2 val12 val22
> 3 val13 val23
> 4 val14 val24
>
Counter column in the result of SELECT statement
Hi,
I need to have an extra column in my SELECT's result that counts from 1 to
the end of result.
I think I can do it in reporting softwares such as crystal report, but I
want to do this in query analyzer with the SELECT statement, something like
this:
Counter CustomerID Country
--
1 ALFKI Germany
2 ANATR Mexico
3 ANTON Mexico
4 AROUT UK
5 BERGS Sweden
...
Thanks,
AminSee if this helps:
http://support.microsoft.com/defaul...B;EN-US;q186133
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Amin Sobati" <amins@.morva.net> wrote in message
news:%23x4ocTDOEHA.3016@.tk2msftngp13.phx.gbl...
Hi,
I need to have an extra column in my SELECT's result that counts from 1 to
the end of result.
I think I can do it in reporting softwares such as crystal report, but I
want to do this in query analyzer with the SELECT statement, something like
this:
Counter CustomerID Country
--
1 ALFKI Germany
2 ANATR Mexico
3 ANTON Mexico
4 AROUT UK
5 BERGS Sweden
...
Thanks,
Amin|||Nice article! Thanks!
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:#SkADeDOEHA.2876@.TK2MSFTNGP09.phx.gbl...
> See if this helps:
> http://support.microsoft.com/defaul...B;EN-US;q186133
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
> "Amin Sobati" <amins@.morva.net> wrote in message
> news:%23x4ocTDOEHA.3016@.tk2msftngp13.phx.gbl...
> Hi,
> I need to have an extra column in my SELECT's result that counts from 1 to
> the end of result.
> I think I can do it in reporting softwares such as crystal report, but I
> want to do this in query analyzer with the SELECT statement, something
like
> this:
> Counter CustomerID Country
> --
> 1 ALFKI Germany
> 2 ANATR Mexico
> 3 ANTON Mexico
> 4 AROUT UK
> 5 BERGS Sweden
> ...
>
> Thanks,
> Amin
>
>
I need to have an extra column in my SELECT's result that counts from 1 to
the end of result.
I think I can do it in reporting softwares such as crystal report, but I
want to do this in query analyzer with the SELECT statement, something like
this:
Counter CustomerID Country
--
1 ALFKI Germany
2 ANATR Mexico
3 ANTON Mexico
4 AROUT UK
5 BERGS Sweden
...
Thanks,
AminSee if this helps:
http://support.microsoft.com/defaul...B;EN-US;q186133
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Amin Sobati" <amins@.morva.net> wrote in message
news:%23x4ocTDOEHA.3016@.tk2msftngp13.phx.gbl...
Hi,
I need to have an extra column in my SELECT's result that counts from 1 to
the end of result.
I think I can do it in reporting softwares such as crystal report, but I
want to do this in query analyzer with the SELECT statement, something like
this:
Counter CustomerID Country
--
1 ALFKI Germany
2 ANATR Mexico
3 ANTON Mexico
4 AROUT UK
5 BERGS Sweden
...
Thanks,
Amin|||Nice article! Thanks!
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:#SkADeDOEHA.2876@.TK2MSFTNGP09.phx.gbl...
> See if this helps:
> http://support.microsoft.com/defaul...B;EN-US;q186133
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
> "Amin Sobati" <amins@.morva.net> wrote in message
> news:%23x4ocTDOEHA.3016@.tk2msftngp13.phx.gbl...
> Hi,
> I need to have an extra column in my SELECT's result that counts from 1 to
> the end of result.
> I think I can do it in reporting softwares such as crystal report, but I
> want to do this in query analyzer with the SELECT statement, something
like
> this:
> Counter CustomerID Country
> --
> 1 ALFKI Germany
> 2 ANATR Mexico
> 3 ANTON Mexico
> 4 AROUT UK
> 5 BERGS Sweden
> ...
>
> Thanks,
> Amin
>
>
Counter column in the result of SELECT statement
Hi,
I need to have an extra column in my SELECT's result that counts from 1 to
the end of result.
I think I can do it in reporting softwares such as crystal report, but I
want to do this in query analyzer with the SELECT statement, something like
this:
Counter CustomerID Country
1 ALFKI Germany
2 ANATR Mexico
3 ANTON Mexico
4 AROUT UK
5 BERGS Sweden
...
Thanks,
Amin
See if this helps:
http://support.microsoft.com/default...;EN-US;q186133
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Amin Sobati" <amins@.morva.net> wrote in message
news:%23x4ocTDOEHA.3016@.tk2msftngp13.phx.gbl...
Hi,
I need to have an extra column in my SELECT's result that counts from 1 to
the end of result.
I think I can do it in reporting softwares such as crystal report, but I
want to do this in query analyzer with the SELECT statement, something like
this:
Counter CustomerID Country
1 ALFKI Germany
2 ANATR Mexico
3 ANTON Mexico
4 AROUT UK
5 BERGS Sweden
...
Thanks,
Amin
|||Nice article! Thanks!
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:#SkADeDOEHA.2876@.TK2MSFTNGP09.phx.gbl...
> See if this helps:
> http://support.microsoft.com/default...;EN-US;q186133
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
> "Amin Sobati" <amins@.morva.net> wrote in message
> news:%23x4ocTDOEHA.3016@.tk2msftngp13.phx.gbl...
> Hi,
> I need to have an extra column in my SELECT's result that counts from 1 to
> the end of result.
> I think I can do it in reporting softwares such as crystal report, but I
> want to do this in query analyzer with the SELECT statement, something
like
> this:
> Counter CustomerID Country
> --
> 1 ALFKI Germany
> 2 ANATR Mexico
> 3 ANTON Mexico
> 4 AROUT UK
> 5 BERGS Sweden
> ...
>
> Thanks,
> Amin
>
>
I need to have an extra column in my SELECT's result that counts from 1 to
the end of result.
I think I can do it in reporting softwares such as crystal report, but I
want to do this in query analyzer with the SELECT statement, something like
this:
Counter CustomerID Country
1 ALFKI Germany
2 ANATR Mexico
3 ANTON Mexico
4 AROUT UK
5 BERGS Sweden
...
Thanks,
Amin
See if this helps:
http://support.microsoft.com/default...;EN-US;q186133
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Amin Sobati" <amins@.morva.net> wrote in message
news:%23x4ocTDOEHA.3016@.tk2msftngp13.phx.gbl...
Hi,
I need to have an extra column in my SELECT's result that counts from 1 to
the end of result.
I think I can do it in reporting softwares such as crystal report, but I
want to do this in query analyzer with the SELECT statement, something like
this:
Counter CustomerID Country
1 ALFKI Germany
2 ANATR Mexico
3 ANTON Mexico
4 AROUT UK
5 BERGS Sweden
...
Thanks,
Amin
|||Nice article! Thanks!
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:#SkADeDOEHA.2876@.TK2MSFTNGP09.phx.gbl...
> See if this helps:
> http://support.microsoft.com/default...;EN-US;q186133
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
> "Amin Sobati" <amins@.morva.net> wrote in message
> news:%23x4ocTDOEHA.3016@.tk2msftngp13.phx.gbl...
> Hi,
> I need to have an extra column in my SELECT's result that counts from 1 to
> the end of result.
> I think I can do it in reporting softwares such as crystal report, but I
> want to do this in query analyzer with the SELECT statement, something
like
> this:
> Counter CustomerID Country
> --
> 1 ALFKI Germany
> 2 ANATR Mexico
> 3 ANTON Mexico
> 4 AROUT UK
> 5 BERGS Sweden
> ...
>
> Thanks,
> Amin
>
>
Counter column in the result of SELECT statement
Hi,
I need to have an extra column in my SELECT's result that counts from 1 to
the end of result.
I think I can do it in reporting softwares such as crystal report, but I
want to do this in query analyzer with the SELECT statement, something like
this:
Counter CustomerID Country
--
1 ALFKI Germany
2 ANATR Mexico
3 ANTON Mexico
4 AROUT UK
5 BERGS Sweden
...
Thanks,
AminSee if this helps:
http://support.microsoft.com/default.aspx?scid=KB;EN-US;q186133
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Amin Sobati" <amins@.morva.net> wrote in message
news:%23x4ocTDOEHA.3016@.tk2msftngp13.phx.gbl...
Hi,
I need to have an extra column in my SELECT's result that counts from 1 to
the end of result.
I think I can do it in reporting softwares such as crystal report, but I
want to do this in query analyzer with the SELECT statement, something like
this:
Counter CustomerID Country
--
1 ALFKI Germany
2 ANATR Mexico
3 ANTON Mexico
4 AROUT UK
5 BERGS Sweden
...
Thanks,
Amin|||Nice article! Thanks!
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:#SkADeDOEHA.2876@.TK2MSFTNGP09.phx.gbl...
> See if this helps:
> http://support.microsoft.com/default.aspx?scid=KB;EN-US;q186133
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
> "Amin Sobati" <amins@.morva.net> wrote in message
> news:%23x4ocTDOEHA.3016@.tk2msftngp13.phx.gbl...
> Hi,
> I need to have an extra column in my SELECT's result that counts from 1 to
> the end of result.
> I think I can do it in reporting softwares such as crystal report, but I
> want to do this in query analyzer with the SELECT statement, something
like
> this:
> Counter CustomerID Country
> --
> 1 ALFKI Germany
> 2 ANATR Mexico
> 3 ANTON Mexico
> 4 AROUT UK
> 5 BERGS Sweden
> ...
>
> Thanks,
> Amin
>
>
I need to have an extra column in my SELECT's result that counts from 1 to
the end of result.
I think I can do it in reporting softwares such as crystal report, but I
want to do this in query analyzer with the SELECT statement, something like
this:
Counter CustomerID Country
--
1 ALFKI Germany
2 ANATR Mexico
3 ANTON Mexico
4 AROUT UK
5 BERGS Sweden
...
Thanks,
AminSee if this helps:
http://support.microsoft.com/default.aspx?scid=KB;EN-US;q186133
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Amin Sobati" <amins@.morva.net> wrote in message
news:%23x4ocTDOEHA.3016@.tk2msftngp13.phx.gbl...
Hi,
I need to have an extra column in my SELECT's result that counts from 1 to
the end of result.
I think I can do it in reporting softwares such as crystal report, but I
want to do this in query analyzer with the SELECT statement, something like
this:
Counter CustomerID Country
--
1 ALFKI Germany
2 ANATR Mexico
3 ANTON Mexico
4 AROUT UK
5 BERGS Sweden
...
Thanks,
Amin|||Nice article! Thanks!
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:#SkADeDOEHA.2876@.TK2MSFTNGP09.phx.gbl...
> See if this helps:
> http://support.microsoft.com/default.aspx?scid=KB;EN-US;q186133
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
> "Amin Sobati" <amins@.morva.net> wrote in message
> news:%23x4ocTDOEHA.3016@.tk2msftngp13.phx.gbl...
> Hi,
> I need to have an extra column in my SELECT's result that counts from 1 to
> the end of result.
> I think I can do it in reporting softwares such as crystal report, but I
> want to do this in query analyzer with the SELECT statement, something
like
> this:
> Counter CustomerID Country
> --
> 1 ALFKI Germany
> 2 ANATR Mexico
> 3 ANTON Mexico
> 4 AROUT UK
> 5 BERGS Sweden
> ...
>
> Thanks,
> Amin
>
>
Subscribe to:
Posts (Atom)