I need a stored proc to make all aliases to users, then assign these newly created users to the same db's (which the aliases were assigned to).
Are there any other ways, or just to write a stored proc?
ANY suggestions accepted, just pls help, i need this for yesterday, and have not much experience in this :(Have a look at SP_ADDALIAS under BOL.
HTH|||Originally posted by Satya
Have a look at SP_ADDALIAS under BOL.
HTH
I mean I want to change the aliases to users, not to add an alias to a user.
(eg. you have a db, and there are 50 users and 10 aliases. You don't want to use aliases. So you have to make users from them.)
I need help in this, sorry is anybody misunderstood :)
So I have to drop all aliases, and create users with the same names, and assign them to the correct db-s.
But this have to work automatically...any scripts? commands?...etc
Showing posts with label proc. Show all posts
Showing posts with label proc. Show all posts
Monday, March 26, 2012
need stored proc to append table 1 to table 2
HI just wondering if there is a simple transact statement to use to copy the
contents of table one and append it to a second table. Each table has the
same columns and datatypes, thanks.
Paul G
Software engineer.
Here's the books online example from the INSERT...SELECT topic.
USE pubs
INSERT INTO mybooks
SELECT title_id
, title
, TYPE
FROM titles
WHERE TYPE = 'mod_cook'
|||Paul wrote:
> HI just wondering if there is a simple transact statement to use to
> copy the contents of table one and append it to a second table. Each
> table has the same columns and datatypes, thanks.
In order to have SQL Server create the destination table, use
SELECT...INTO
Select
id,
type,
name
Into
dbo.MyObjects
From
dbo.sysobjects
Where
id < 100
Select * from dbo.MyObjects
Drop Table dbo.MyObjects
David Gugick - SQL Server MVP
Quest Software
|||ok thanks.
Paul G
Software engineer.
"David Gugick" wrote:
> Paul wrote:
> In order to have SQL Server create the destination table, use
> SELECT...INTO
> Select
> id,
> type,
> name
> Into
> dbo.MyObjects
> From
> dbo.sysobjects
> Where
> id < 100
> Select * from dbo.MyObjects
> Drop Table dbo.MyObjects
>
> --
> David Gugick - SQL Server MVP
> Quest Software
>
contents of table one and append it to a second table. Each table has the
same columns and datatypes, thanks.
Paul G
Software engineer.
Here's the books online example from the INSERT...SELECT topic.
USE pubs
INSERT INTO mybooks
SELECT title_id
, title
, TYPE
FROM titles
WHERE TYPE = 'mod_cook'
|||Paul wrote:
> HI just wondering if there is a simple transact statement to use to
> copy the contents of table one and append it to a second table. Each
> table has the same columns and datatypes, thanks.
In order to have SQL Server create the destination table, use
SELECT...INTO
Select
id,
type,
name
Into
dbo.MyObjects
From
dbo.sysobjects
Where
id < 100
Select * from dbo.MyObjects
Drop Table dbo.MyObjects
David Gugick - SQL Server MVP
Quest Software
|||ok thanks.
Paul G
Software engineer.
"David Gugick" wrote:
> Paul wrote:
> In order to have SQL Server create the destination table, use
> SELECT...INTO
> Select
> id,
> type,
> name
> Into
> dbo.MyObjects
> From
> dbo.sysobjects
> Where
> id < 100
> Select * from dbo.MyObjects
> Drop Table dbo.MyObjects
>
> --
> David Gugick - SQL Server MVP
> Quest Software
>
need stored proc to append table 1 to table 2
HI just wondering if there is a simple transact statement to use to copy the
contents of table one and append it to a second table. Each table has the
same columns and datatypes, thanks.
--
Paul G
Software engineer.Here's the books online example from the INSERT...SELECT topic.
USE pubs
INSERT INTO mybooks
SELECT title_id
, title
, TYPE
FROM titles
WHERE TYPE = 'mod_cook'|||Paul wrote:
> HI just wondering if there is a simple transact statement to use to
> copy the contents of table one and append it to a second table. Each
> table has the same columns and datatypes, thanks.
In order to have SQL Server create the destination table, use
SELECT...INTO
Select
id,
type,
name
Into
dbo.MyObjects
From
dbo.sysobjects
Where
id < 100
Select * from dbo.MyObjects
Drop Table dbo.MyObjects
David Gugick - SQL Server MVP
Quest Software|||ok thanks.
--
Paul G
Software engineer.
"David Gugick" wrote:
> Paul wrote:
> In order to have SQL Server create the destination table, use
> SELECT...INTO
> Select
> id,
> type,
> name
> Into
> dbo.MyObjects
> From
> dbo.sysobjects
> Where
> id < 100
> Select * from dbo.MyObjects
> Drop Table dbo.MyObjects
>
> --
> David Gugick - SQL Server MVP
> Quest Software
>
contents of table one and append it to a second table. Each table has the
same columns and datatypes, thanks.
--
Paul G
Software engineer.Here's the books online example from the INSERT...SELECT topic.
USE pubs
INSERT INTO mybooks
SELECT title_id
, title
, TYPE
FROM titles
WHERE TYPE = 'mod_cook'|||Paul wrote:
> HI just wondering if there is a simple transact statement to use to
> copy the contents of table one and append it to a second table. Each
> table has the same columns and datatypes, thanks.
In order to have SQL Server create the destination table, use
SELECT...INTO
Select
id,
type,
name
Into
dbo.MyObjects
From
dbo.sysobjects
Where
id < 100
Select * from dbo.MyObjects
Drop Table dbo.MyObjects
David Gugick - SQL Server MVP
Quest Software|||ok thanks.
--
Paul G
Software engineer.
"David Gugick" wrote:
> Paul wrote:
> In order to have SQL Server create the destination table, use
> SELECT...INTO
> Select
> id,
> type,
> name
> Into
> dbo.MyObjects
> From
> dbo.sysobjects
> Where
> id < 100
> Select * from dbo.MyObjects
> Drop Table dbo.MyObjects
>
> --
> David Gugick - SQL Server MVP
> Quest Software
>
need stored proc to append table 1 to table 2
HI just wondering if there is a simple transact statement to use to copy the
contents of table one and append it to a second table. Each table has the
same columns and datatypes, thanks.
--
Paul G
Software engineer.Here's the books online example from the INSERT...SELECT topic.
USE pubs
INSERT INTO mybooks
SELECT title_id
, title
, TYPE
FROM titles
WHERE TYPE = 'mod_cook'|||Paul wrote:
> HI just wondering if there is a simple transact statement to use to
> copy the contents of table one and append it to a second table. Each
> table has the same columns and datatypes, thanks.
In order to have SQL Server create the destination table, use
SELECT...INTO
Select
id,
type,
name
Into
dbo.MyObjects
From
dbo.sysobjects
Where
id < 100
Select * from dbo.MyObjects
Drop Table dbo.MyObjects
David Gugick - SQL Server MVP
Quest Software|||ok thanks.
--
Paul G
Software engineer.
"David Gugick" wrote:
> Paul wrote:
> > HI just wondering if there is a simple transact statement to use to
> > copy the contents of table one and append it to a second table. Each
> > table has the same columns and datatypes, thanks.
> In order to have SQL Server create the destination table, use
> SELECT...INTO
> Select
> id,
> type,
> name
> Into
> dbo.MyObjects
> From
> dbo.sysobjects
> Where
> id < 100
> Select * from dbo.MyObjects
> Drop Table dbo.MyObjects
>
> --
> David Gugick - SQL Server MVP
> Quest Software
>
contents of table one and append it to a second table. Each table has the
same columns and datatypes, thanks.
--
Paul G
Software engineer.Here's the books online example from the INSERT...SELECT topic.
USE pubs
INSERT INTO mybooks
SELECT title_id
, title
, TYPE
FROM titles
WHERE TYPE = 'mod_cook'|||Paul wrote:
> HI just wondering if there is a simple transact statement to use to
> copy the contents of table one and append it to a second table. Each
> table has the same columns and datatypes, thanks.
In order to have SQL Server create the destination table, use
SELECT...INTO
Select
id,
type,
name
Into
dbo.MyObjects
From
dbo.sysobjects
Where
id < 100
Select * from dbo.MyObjects
Drop Table dbo.MyObjects
David Gugick - SQL Server MVP
Quest Software|||ok thanks.
--
Paul G
Software engineer.
"David Gugick" wrote:
> Paul wrote:
> > HI just wondering if there is a simple transact statement to use to
> > copy the contents of table one and append it to a second table. Each
> > table has the same columns and datatypes, thanks.
> In order to have SQL Server create the destination table, use
> SELECT...INTO
> Select
> id,
> type,
> name
> Into
> dbo.MyObjects
> From
> dbo.sysobjects
> Where
> id < 100
> Select * from dbo.MyObjects
> Drop Table dbo.MyObjects
>
> --
> David Gugick - SQL Server MVP
> Quest Software
>
Saturday, February 25, 2012
Need help, error with store proc
I am trying to do an insert with a SPand I get the following error: "The name 'A2LA Website' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted."
When iParentID=30 and strTexte="A2LA Website"
xample :
and
(2) you dont need dynamic sql to insert. you can modify your insert stmt as
When iParentID=30 and strTexte="A2LA Website"
What an I doing wrong?
Table:
ID int(identity)
ParentID int
Text nvarchar(50)
Valeur nvarchar(50) Allow Null
Ordre int Allow Nulls
(1) when you add parameters, specify their type/lengths too
Public Sub addItemToDdSelection(ByVal strTable As String, ByVal iParentID As Int16, ByVal strTexte As String)
Dim connect As New SqlConnection(strConnection)
Dim cmdSelect As New SqlCommand("AddDropDownContent", connect)
Dim paramReturnValue As SqlParameter
Dim strError As StringcmdSelect.CommandType = CommandType.StoredProcedure
'PARAM
cmdSelect.Parameters.Add("@.intParentID", iParentID)
cmdSelect.Parameters.Add("@.strTexte", strTexte)
Try
connect.Open()
cmdSelect.ExecuteNonQuery()
connect.Close()
Catch ex As ExceptionstrError = ex.Message
Finally
If connect.State = ConnectionState.Open Then
connect.Close()
End If
End Try
End SubALTER PROCEDURE dbo.AddDropDownContent
(
@.intParentID int,
@.strTexte varchar(50)
)
AS
EXEC ('INSERT INTO DropDownMenus (ParentID, Texte) VALUES(' + @.intParentID + ',"' + @.strTexte + '")')
xample :
cmdSelect.Parameters.Add("@.intParentID", iParentID) should be
cmdSelect.Parameters.Add(New SqlParameter("@.intParentID",SqlDbType.int))
cmdSelect.Parameters("@.intParentID").Value = Trim(iParentID)
and
(2) you dont need dynamic sql to insert. you can modify your insert stmt as
Create Procedure ...
AS
SET NOCOUNT ON
INSERT INTO DropDownMenus (ParentID, Texte) VALUES ( @.intParentID ,@.strTexte )SET NOCOUNT OFF
hth|||Thanks for your reply this is really helping. A few questions,
-Trim(iParentID) is used in case the id is enter from a text box I guess?
-What is the role of the line "SET NOCOUNT ON" and "SET NOCOUNT OFF"?|||>>-Trim(iParentID) is used in case the id is enter from a text box I guess?
yes. you might also want to do a
Convert.ToInt16(Trim(iParentId))to make sure you are sending in an integer type value. Its a good idea to validate user input.
>>What is the role of the line "SET NOCOUNT ON" and "SET NOCOUNT OFF"?
read up BOL (Books On Line)
hth
Monday, February 20, 2012
Need help writing query/ stored proc
I need to write query or stored proc that is going to be used to gen. a
report
This is a structure of my table
Id Date ItemType OrdersPlaced
1 08/21 1 100
1 08/21 2 500
1 08/21 3 200
1 08/21 4 150
2 ... ... ...
2 ... ... ...
3 ... ... ...
4 ... ... ...
4 ... ... ...
The report is going to take Id and Date as inputs and report generated
is in following format
Id: 1
Date: 08/21
ItemType 1 ItemType 2 ItemType 3 ItemType 4
100 500 200 150
This can definetely be done creating a temp table and writing to this
temp table thru multiple selects for each itemtype.
Is there any better way'
ThanksHave a look at the PIVOT function in SQL 2005 Books online.
Or this example on SQL 2000 will get you on the way
http://www.dandyman.net/sql/samples/pivottable.txt
__________________________________________________
Dandy Weyn - Dandyman (r)
MCSE-MCSA-MCDBA-MCDST-MCT Community Leader
MCTS SQL 2005- MCITP Database Administrator
Author of Sybex Exam Study Guide MCTS SQL 2005
http://www.dandyman.net
"absoft" <arpit.00@.gmail.com> wrote in message
news:1156180618.673896.29220@.p79g2000cwp.googlegroups.com...
>I need to write query or stored proc that is going to be used to gen. a
> report
> This is a structure of my table
> Id Date ItemType OrdersPlaced
> 1 08/21 1 100
> 1 08/21 2 500
> 1 08/21 3 200
> 1 08/21 4 150
> 2 ... ... ...
> 2 ... ... ...
> 3 ... ... ...
> 4 ... ... ...
> 4 ... ... ...
> The report is going to take Id and Date as inputs and report generated
> is in following format
> Id: 1
> Date: 08/21
> ItemType 1 ItemType 2 ItemType 3 ItemType 4
> 100 500 200 150
> This can definetely be done creating a temp table and writing to this
> temp table thru multiple selects for each itemtype.
> Is there any better way'
> Thanks
>|||You can generate a summarized query using SUM(OrdersPlaced) and GROUP BY
[Id], [Date]. The sideways generation you're looking for is a pivot table
type query, which can be done with a "monster" CASE statement in SQL 2000 or
the PIVOT operator in SQL 2005. Either method would require you to know,
and hard-code, your column "headings" (Item Type 1, Item Type 2, etc.) in
advance. If you don't know them in advance you can use dynamic SQL to work
it out.
Personally I'd recommend doing the SUM()/GROUP BY and transferring the
results to a front-end app and format it there.
"absoft" <arpit.00@.gmail.com> wrote in message
news:1156180618.673896.29220@.p79g2000cwp.googlegroups.com...
>I need to write query or stored proc that is going to be used to gen. a
> report
> This is a structure of my table
> Id Date ItemType OrdersPlaced
> 1 08/21 1 100
> 1 08/21 2 500
> 1 08/21 3 200
> 1 08/21 4 150
> 2 ... ... ...
> 2 ... ... ...
> 3 ... ... ...
> 4 ... ... ...
> 4 ... ... ...
> The report is going to take Id and Date as inputs and report generated
> is in following format
> Id: 1
> Date: 08/21
> ItemType 1 ItemType 2 ItemType 3 ItemType 4
> 100 500 200 150
> This can definetely be done creating a temp table and writing to this
> temp table thru multiple selects for each itemtype.
> Is there any better way'
> Thanks
>|||Thanks y'all I am using SQL 2000 so pivot operator is not an option
open for me. I like the idea of using the "monster" CASE statement ...
and it almost returns the kind of report that I need ... just one snag
apart from the above fields there is one more calculated field that
references another table
e.g.
Table B
Id ItemType EstimatedTotal
1 1 200
1 2 800
1 3 200
1 4 200
The new field is a sum of EstimatedTotal for all ItemType and is used
for comparison to the actual total. The new report looks something like
this:
> > Id: 1
> > Date: 08/21
> > ItemType 1 ItemType 2 ItemType 3 ItemType 4 ActualTotal EstimatedTotal
> > 100 500 200 150 950 1400
going forward with the logic of using CASE and GROUP BY, can I get this
EstimatedTotal of 1400 returned by same query without using cursors or
temp tables
Any thoughts!!
Mike C# wrote:
> You can generate a summarized query using SUM(OrdersPlaced) and GROUP BY
> [Id], [Date]. The sideways generation you're looking for is a pivot table
> type query, which can be done with a "monster" CASE statement in SQL 2000 or
> the PIVOT operator in SQL 2005. Either method would require you to know,
> and hard-code, your column "headings" (Item Type 1, Item Type 2, etc.) in
> advance. If you don't know them in advance you can use dynamic SQL to work
> it out.
> Personally I'd recommend doing the SUM()/GROUP BY and transferring the
> results to a front-end app and format it there.
> "absoft" <arpit.00@.gmail.com> wrote in message
> news:1156180618.673896.29220@.p79g2000cwp.googlegroups.com...
> >I need to write query or stored proc that is going to be used to gen. a
> > report
> > This is a structure of my table
> >
> > Id Date ItemType OrdersPlaced
> > 1 08/21 1 100
> > 1 08/21 2 500
> > 1 08/21 3 200
> > 1 08/21 4 150
> > 2 ... ... ...
> > 2 ... ... ...
> > 3 ... ... ...
> > 4 ... ... ...
> > 4 ... ... ...
> >
> > The report is going to take Id and Date as inputs and report generated
> > is in following format
> >
> > Id: 1
> > Date: 08/21
> > ItemType 1 ItemType 2 ItemType 3 ItemType 4
> > 100 500 200 150
> >
> > This can definetely be done creating a temp table and writing to this
> > temp table thru multiple selects for each itemtype.
> > Is there any better way'
> >
> > Thanks
> >
report
This is a structure of my table
Id Date ItemType OrdersPlaced
1 08/21 1 100
1 08/21 2 500
1 08/21 3 200
1 08/21 4 150
2 ... ... ...
2 ... ... ...
3 ... ... ...
4 ... ... ...
4 ... ... ...
The report is going to take Id and Date as inputs and report generated
is in following format
Id: 1
Date: 08/21
ItemType 1 ItemType 2 ItemType 3 ItemType 4
100 500 200 150
This can definetely be done creating a temp table and writing to this
temp table thru multiple selects for each itemtype.
Is there any better way'
ThanksHave a look at the PIVOT function in SQL 2005 Books online.
Or this example on SQL 2000 will get you on the way
http://www.dandyman.net/sql/samples/pivottable.txt
__________________________________________________
Dandy Weyn - Dandyman (r)
MCSE-MCSA-MCDBA-MCDST-MCT Community Leader
MCTS SQL 2005- MCITP Database Administrator
Author of Sybex Exam Study Guide MCTS SQL 2005
http://www.dandyman.net
"absoft" <arpit.00@.gmail.com> wrote in message
news:1156180618.673896.29220@.p79g2000cwp.googlegroups.com...
>I need to write query or stored proc that is going to be used to gen. a
> report
> This is a structure of my table
> Id Date ItemType OrdersPlaced
> 1 08/21 1 100
> 1 08/21 2 500
> 1 08/21 3 200
> 1 08/21 4 150
> 2 ... ... ...
> 2 ... ... ...
> 3 ... ... ...
> 4 ... ... ...
> 4 ... ... ...
> The report is going to take Id and Date as inputs and report generated
> is in following format
> Id: 1
> Date: 08/21
> ItemType 1 ItemType 2 ItemType 3 ItemType 4
> 100 500 200 150
> This can definetely be done creating a temp table and writing to this
> temp table thru multiple selects for each itemtype.
> Is there any better way'
> Thanks
>|||You can generate a summarized query using SUM(OrdersPlaced) and GROUP BY
[Id], [Date]. The sideways generation you're looking for is a pivot table
type query, which can be done with a "monster" CASE statement in SQL 2000 or
the PIVOT operator in SQL 2005. Either method would require you to know,
and hard-code, your column "headings" (Item Type 1, Item Type 2, etc.) in
advance. If you don't know them in advance you can use dynamic SQL to work
it out.
Personally I'd recommend doing the SUM()/GROUP BY and transferring the
results to a front-end app and format it there.
"absoft" <arpit.00@.gmail.com> wrote in message
news:1156180618.673896.29220@.p79g2000cwp.googlegroups.com...
>I need to write query or stored proc that is going to be used to gen. a
> report
> This is a structure of my table
> Id Date ItemType OrdersPlaced
> 1 08/21 1 100
> 1 08/21 2 500
> 1 08/21 3 200
> 1 08/21 4 150
> 2 ... ... ...
> 2 ... ... ...
> 3 ... ... ...
> 4 ... ... ...
> 4 ... ... ...
> The report is going to take Id and Date as inputs and report generated
> is in following format
> Id: 1
> Date: 08/21
> ItemType 1 ItemType 2 ItemType 3 ItemType 4
> 100 500 200 150
> This can definetely be done creating a temp table and writing to this
> temp table thru multiple selects for each itemtype.
> Is there any better way'
> Thanks
>|||Thanks y'all I am using SQL 2000 so pivot operator is not an option
open for me. I like the idea of using the "monster" CASE statement ...
and it almost returns the kind of report that I need ... just one snag
apart from the above fields there is one more calculated field that
references another table
e.g.
Table B
Id ItemType EstimatedTotal
1 1 200
1 2 800
1 3 200
1 4 200
The new field is a sum of EstimatedTotal for all ItemType and is used
for comparison to the actual total. The new report looks something like
this:
> > Id: 1
> > Date: 08/21
> > ItemType 1 ItemType 2 ItemType 3 ItemType 4 ActualTotal EstimatedTotal
> > 100 500 200 150 950 1400
going forward with the logic of using CASE and GROUP BY, can I get this
EstimatedTotal of 1400 returned by same query without using cursors or
temp tables
Any thoughts!!
Mike C# wrote:
> You can generate a summarized query using SUM(OrdersPlaced) and GROUP BY
> [Id], [Date]. The sideways generation you're looking for is a pivot table
> type query, which can be done with a "monster" CASE statement in SQL 2000 or
> the PIVOT operator in SQL 2005. Either method would require you to know,
> and hard-code, your column "headings" (Item Type 1, Item Type 2, etc.) in
> advance. If you don't know them in advance you can use dynamic SQL to work
> it out.
> Personally I'd recommend doing the SUM()/GROUP BY and transferring the
> results to a front-end app and format it there.
> "absoft" <arpit.00@.gmail.com> wrote in message
> news:1156180618.673896.29220@.p79g2000cwp.googlegroups.com...
> >I need to write query or stored proc that is going to be used to gen. a
> > report
> > This is a structure of my table
> >
> > Id Date ItemType OrdersPlaced
> > 1 08/21 1 100
> > 1 08/21 2 500
> > 1 08/21 3 200
> > 1 08/21 4 150
> > 2 ... ... ...
> > 2 ... ... ...
> > 3 ... ... ...
> > 4 ... ... ...
> > 4 ... ... ...
> >
> > The report is going to take Id and Date as inputs and report generated
> > is in following format
> >
> > Id: 1
> > Date: 08/21
> > ItemType 1 ItemType 2 ItemType 3 ItemType 4
> > 100 500 200 150
> >
> > This can definetely be done creating a temp table and writing to this
> > temp table thru multiple selects for each itemtype.
> > Is there any better way'
> >
> > Thanks
> >
Need help writing query/ stored proc
I need to write query or stored proc that is going to be used to gen. a
report
This is a structure of my table
Id Date ItemType OrdersPlaced
1 08/21 1 100
1 08/21 2 500
1 08/21 3 200
1 08/21 4 150
2 ... ... ...
2 ... ... ...
3 ... ... ...
4 ... ... ...
4 ... ... ...
The report is going to take Id and Date as inputs and report generated
is in following format
Id: 1
Date: 08/21
ItemType 1 ItemType 2 ItemType 3 ItemType 4
100 500 200 150
This can definetely be done creating a temp table and writing to this
temp table thru multiple selects for each itemtype.
Is there any better way'
ThanksHave a look at the PIVOT function in SQL 2005 Books online.
Or this example on SQL 2000 will get you on the way
http://www.dandyman.net/sql/samples/pivottable.txt
________________________________________
__________
Dandy Weyn - Dandyman (r)
MCSE-MCSA-MCDBA-MCDST-MCT Community Leader
MCTS SQL 2005- MCITP Database Administrator
Author of Sybex Exam Study Guide MCTS SQL 2005
http://www.dandyman.net
"absoft" <arpit.00@.gmail.com> wrote in message
news:1156180618.673896.29220@.p79g2000cwp.googlegroups.com...
>I need to write query or stored proc that is going to be used to gen. a
> report
> This is a structure of my table
> Id Date ItemType OrdersPlaced
> 1 08/21 1 100
> 1 08/21 2 500
> 1 08/21 3 200
> 1 08/21 4 150
> 2 ... ... ...
> 2 ... ... ...
> 3 ... ... ...
> 4 ... ... ...
> 4 ... ... ...
> The report is going to take Id and Date as inputs and report generated
> is in following format
> Id: 1
> Date: 08/21
> ItemType 1 ItemType 2 ItemType 3 ItemType 4
> 100 500 200 150
> This can definetely be done creating a temp table and writing to this
> temp table thru multiple selects for each itemtype.
> Is there any better way'
> Thanks
>|||You can generate a summarized query using SUM(OrdersPlaced) and GROUP BY
[Id], [Date]. The sideways generation you're looking for is a pivot
table
type query, which can be done with a "monster" CASE statement in SQL 2000 or
the PIVOT operator in SQL 2005. Either method would require you to know,
and hard-code, your column "headings" (Item Type 1, Item Type 2, etc.) in
advance. If you don't know them in advance you can use dynamic SQL to work
it out.
Personally I'd recommend doing the SUM()/GROUP BY and transferring the
results to a front-end app and format it there.
"absoft" <arpit.00@.gmail.com> wrote in message
news:1156180618.673896.29220@.p79g2000cwp.googlegroups.com...
>I need to write query or stored proc that is going to be used to gen. a
> report
> This is a structure of my table
> Id Date ItemType OrdersPlaced
> 1 08/21 1 100
> 1 08/21 2 500
> 1 08/21 3 200
> 1 08/21 4 150
> 2 ... ... ...
> 2 ... ... ...
> 3 ... ... ...
> 4 ... ... ...
> 4 ... ... ...
> The report is going to take Id and Date as inputs and report generated
> is in following format
> Id: 1
> Date: 08/21
> ItemType 1 ItemType 2 ItemType 3 ItemType 4
> 100 500 200 150
> This can definetely be done creating a temp table and writing to this
> temp table thru multiple selects for each itemtype.
> Is there any better way'
> Thanks
>|||Thanks y'all I am using SQL 2000 so pivot operator is not an option
open for me. I like the idea of using the "monster" CASE statement ...
and it almost returns the kind of report that I need ... just one snag
apart from the above fields there is one more calculated field that
references another table
e.g.
Table B
Id ItemType EstimatedTotal
1 1 200
1 2 800
1 3 200
1 4 200
The new field is a sum of EstimatedTotal for all ItemType and is used
for comparison to the actual total. The new report looks something like
this:
[vbcol=seagreen]
going forward with the logic of using CASE and GROUP BY, can I get this
EstimatedTotal of 1400 returned by same query without using cursors or
temp tables
Any thoughts!!
Mike C# wrote:[vbcol=seagreen]
> You can generate a summarized query using SUM(OrdersPlaced) and GROUP BY
> [Id], [Date]. The sideways generation you're looking for is a piv
ot table
> type query, which can be done with a "monster" CASE statement in SQL 2000
or
> the PIVOT operator in SQL 2005. Either method would require you to know,
> and hard-code, your column "headings" (Item Type 1, Item Type 2, etc.) in
> advance. If you don't know them in advance you can use dynamic SQL to wor
k
> it out.
> Personally I'd recommend doing the SUM()/GROUP BY and transferring the
> results to a front-end app and format it there.
> "absoft" <arpit.00@.gmail.com> wrote in message
> news:1156180618.673896.29220@.p79g2000cwp.googlegroups.com...
report
This is a structure of my table
Id Date ItemType OrdersPlaced
1 08/21 1 100
1 08/21 2 500
1 08/21 3 200
1 08/21 4 150
2 ... ... ...
2 ... ... ...
3 ... ... ...
4 ... ... ...
4 ... ... ...
The report is going to take Id and Date as inputs and report generated
is in following format
Id: 1
Date: 08/21
ItemType 1 ItemType 2 ItemType 3 ItemType 4
100 500 200 150
This can definetely be done creating a temp table and writing to this
temp table thru multiple selects for each itemtype.
Is there any better way'
ThanksHave a look at the PIVOT function in SQL 2005 Books online.
Or this example on SQL 2000 will get you on the way
http://www.dandyman.net/sql/samples/pivottable.txt
________________________________________
__________
Dandy Weyn - Dandyman (r)
MCSE-MCSA-MCDBA-MCDST-MCT Community Leader
MCTS SQL 2005- MCITP Database Administrator
Author of Sybex Exam Study Guide MCTS SQL 2005
http://www.dandyman.net
"absoft" <arpit.00@.gmail.com> wrote in message
news:1156180618.673896.29220@.p79g2000cwp.googlegroups.com...
>I need to write query or stored proc that is going to be used to gen. a
> report
> This is a structure of my table
> Id Date ItemType OrdersPlaced
> 1 08/21 1 100
> 1 08/21 2 500
> 1 08/21 3 200
> 1 08/21 4 150
> 2 ... ... ...
> 2 ... ... ...
> 3 ... ... ...
> 4 ... ... ...
> 4 ... ... ...
> The report is going to take Id and Date as inputs and report generated
> is in following format
> Id: 1
> Date: 08/21
> ItemType 1 ItemType 2 ItemType 3 ItemType 4
> 100 500 200 150
> This can definetely be done creating a temp table and writing to this
> temp table thru multiple selects for each itemtype.
> Is there any better way'
> Thanks
>|||You can generate a summarized query using SUM(OrdersPlaced) and GROUP BY
[Id], [Date]. The sideways generation you're looking for is a pivot
table
type query, which can be done with a "monster" CASE statement in SQL 2000 or
the PIVOT operator in SQL 2005. Either method would require you to know,
and hard-code, your column "headings" (Item Type 1, Item Type 2, etc.) in
advance. If you don't know them in advance you can use dynamic SQL to work
it out.
Personally I'd recommend doing the SUM()/GROUP BY and transferring the
results to a front-end app and format it there.
"absoft" <arpit.00@.gmail.com> wrote in message
news:1156180618.673896.29220@.p79g2000cwp.googlegroups.com...
>I need to write query or stored proc that is going to be used to gen. a
> report
> This is a structure of my table
> Id Date ItemType OrdersPlaced
> 1 08/21 1 100
> 1 08/21 2 500
> 1 08/21 3 200
> 1 08/21 4 150
> 2 ... ... ...
> 2 ... ... ...
> 3 ... ... ...
> 4 ... ... ...
> 4 ... ... ...
> The report is going to take Id and Date as inputs and report generated
> is in following format
> Id: 1
> Date: 08/21
> ItemType 1 ItemType 2 ItemType 3 ItemType 4
> 100 500 200 150
> This can definetely be done creating a temp table and writing to this
> temp table thru multiple selects for each itemtype.
> Is there any better way'
> Thanks
>|||Thanks y'all I am using SQL 2000 so pivot operator is not an option
open for me. I like the idea of using the "monster" CASE statement ...
and it almost returns the kind of report that I need ... just one snag
apart from the above fields there is one more calculated field that
references another table
e.g.
Table B
Id ItemType EstimatedTotal
1 1 200
1 2 800
1 3 200
1 4 200
The new field is a sum of EstimatedTotal for all ItemType and is used
for comparison to the actual total. The new report looks something like
this:
[vbcol=seagreen]
going forward with the logic of using CASE and GROUP BY, can I get this
EstimatedTotal of 1400 returned by same query without using cursors or
temp tables
Any thoughts!!
Mike C# wrote:[vbcol=seagreen]
> You can generate a summarized query using SUM(OrdersPlaced) and GROUP BY
> [Id], [Date]. The sideways generation you're looking for is a piv
ot table
> type query, which can be done with a "monster" CASE statement in SQL 2000
or
> the PIVOT operator in SQL 2005. Either method would require you to know,
> and hard-code, your column "headings" (Item Type 1, Item Type 2, etc.) in
> advance. If you don't know them in advance you can use dynamic SQL to wor
k
> it out.
> Personally I'd recommend doing the SUM()/GROUP BY and transferring the
> results to a front-end app and format it there.
> "absoft" <arpit.00@.gmail.com> wrote in message
> news:1156180618.673896.29220@.p79g2000cwp.googlegroups.com...
Need help writing a SQL Server Query
Could someone please help me out? I need to write a sql stored proc to query the following table.
My SQL experience is very week. If someone can help me with this, I will be happy to pay you$40 for
your help.
I need the proc to do the following:
1.) For every Superintendent in a region, country state and county; return the state name, superintendent name,
the county name and and a string which is a comma delimited list of schools they supervise. See the sample output italicised and bold.
So the big challenge here is to also return a string that is a concatenation of school names for a particular
Superintendent in a given state and county. For example:East,Kennedy,Apolo,Morrison.
So basically the stored proc should accept input parameters of the Region, Country, State, and County
Here is the data table:
REGION COUNTRY STATE SUPER_INTENDENT PHONE_NO SCHOOL County
NA USA Texas Mike Andrews 789-3614 East Lake
NA USA Texas Mike Andrews 789-3614 Kennedy Lake
NA USA Texas Mike Andrews 789-3614 Apolo Lake
NA USA Texas Mike Andrews 789-3614 Morrison Lake
NA USA Texas Amy Markson 789-2134 Anderson Maylor
NA USA Texas Amy Markson 789-2134 Molina Maylor
NA USA Texas Amy Markson 789-2134 Polima Maylor
NA USA Ohio Terry Ellis 966-8314 Kingston Keel
NA USA Ohio Terry Ellis 966-8314 Martin Keel
NA USA Ohio Terry Ellis 966-8314 Eastmore Keel
NA USA Ohio Terry Ellis 966-8314 Canondale Keel
Here is the sample output the way it will appear on a web form:
State:Texas
County:Lake
Mike Andrews East,Kennedy,Apolo,Morrison
789-3614
County:Maylor
Amy Markson
789-2134 Anderson,Molina,Polima
State:Ohio
County:Keel
Terry Ellis Kingston,Martin,Eastomore,Keel
You can mail me the check..
You could create a stored proc that would concatenate the values based on given state and county.
Declare
@.schoolvarchar(500)select
@.school=isnull(@.school,'')+','+ schoolfrom
yourTablewhere
state= @.state and county=@.countyGiven the above hint, I hope you can figure out the rest.
|||Sorry your post has not helped me at all. I need someone to write the query for me. I will be happy to pay that person. please do not respond to this post if you are not serious about helping me.|||
ndinakar:
You can mail me the check..
That was meant to be a joke. I forgot to put a "smiley face" at the end. I was trying to show you how to write the code yourself since what you are trying to do is pretty simple. Now, I do agree that if you cannot figure out how to put the rest of the puzle together, you definetely need someone to write you the code. And perhaps you will need to spend more than the $40 you are offering..
|||I appreciate your sense of humor. No problem.
Another fellow on the forum has offered to help me. I will see take his advice and try to get it working. Thank you.
Subscribe to:
Posts (Atom)