By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,934 Members | 1,366 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,934 IT Pros & Developers. It's quick & easy.

SQL Conversion Assistance Needed

P: n/a
Came across this code.

SELECT Y,
Q1 = COUNT(CASE WHEN Q=1 THEN Sales END),
Q2 = COUNT(CASE WHEN Q=2 THEN Sales END),
Q3 = COUNT(CASE WHEN Q=3 THEN Sales END),
Q4 = COUNT(CASE WHEN Q=4 THEN Sales END)
FROM
dbo.SalesByQuarter
GROUP BY Y
ORDER BY Y

It does a pivot table type deal.

How would I convert this to a one line SQL statement? Is this possible.

Jul 22 '05 #1
Share this Question
Share on Google+
9 Replies


P: n/a
This is a single statement. What do you mean by "one line"?

"DanVDM" <dv****@yahoo.com> wrote in message
news:eq**************@tk2msftngp13.phx.gbl...
Came across this code.

SELECT Y,
Q1 = COUNT(CASE WHEN Q=1 THEN Sales END),
Q2 = COUNT(CASE WHEN Q=2 THEN Sales END),
Q3 = COUNT(CASE WHEN Q=3 THEN Sales END),
Q4 = COUNT(CASE WHEN Q=4 THEN Sales END)
FROM
dbo.SalesByQuarter
GROUP BY Y
ORDER BY Y

It does a pivot table type deal.

How would I convert this to a one line SQL statement? Is this possible.

Jul 22 '05 #2

P: n/a
Would this work the way it is in ASP?
"Aaron Bertrand [SQL Server MVP]" <te*****@dnartreb.noraa> wrote in message
news:Ok**************@TK2MSFTNGP15.phx.gbl...
This is a single statement. What do you mean by "one line"?

"DanVDM" <dv****@yahoo.com> wrote in message
news:eq**************@tk2msftngp13.phx.gbl...
Came across this code.

SELECT Y,
Q1 = COUNT(CASE WHEN Q=1 THEN Sales END),
Q2 = COUNT(CASE WHEN Q=2 THEN Sales END),
Q3 = COUNT(CASE WHEN Q=3 THEN Sales END),
Q4 = COUNT(CASE WHEN Q=4 THEN Sales END)
FROM
dbo.SalesByQuarter
GROUP BY Y
ORDER BY Y

It does a pivot table type deal.

How would I convert this to a one line SQL statement? Is this possible.


Jul 22 '05 #3

P: n/a
As in dynamic sql?

Sure. Why not? Just remove the line breaks and assign the string to a
variable, just as you would normally do with any sql statement.

Of course, I would rather encapsulate it in a stored procedure which can
easily be called from ASP.

http://tinyurl.com/jyy0

Bob Barrows

DanVDM wrote:
Would this work the way it is in ASP?
"Aaron Bertrand [SQL Server MVP]" <te*****@dnartreb.noraa> wrote in
message news:Ok**************@TK2MSFTNGP15.phx.gbl...
This is a single statement. What do you mean by "one line"?

"DanVDM" <dv****@yahoo.com> wrote in message
news:eq**************@tk2msftngp13.phx.gbl...
Came across this code.

SELECT Y,
Q1 = COUNT(CASE WHEN Q=1 THEN Sales END),
Q2 = COUNT(CASE WHEN Q=2 THEN Sales END),
Q3 = COUNT(CASE WHEN Q=3 THEN Sales END),
Q4 = COUNT(CASE WHEN Q=4 THEN Sales END)
FROM
dbo.SalesByQuarter
GROUP BY Y
ORDER BY Y

It does a pivot table type deal.

How would I convert this to a one line SQL statement? Is this
possible.


--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
Jul 22 '05 #4

P: n/a
> Would this work the way it is in ASP?

Yes. Did you try it?

If you are worried that the query actually wraps lines, you can bypass
thatusing line concatenation, e.g.

sql = "SELECT Y, " & _
"01 = COUNT(...)" & _
"..." & _
" ORDER BY Y"

set rs = conn.execute(sql)

Better yet, create a stored procedure, and call that. This kind of data
logic belongs in the database anyway, not in your ASP code.
Jul 22 '05 #5

P: n/a
Got it. Thanks, it worked like a charm.
"Aaron Bertrand [SQL Server MVP]" <te*****@dnartreb.noraa> wrote in message
news:eZ**************@TK2MSFTNGP09.phx.gbl...
Would this work the way it is in ASP?


Yes. Did you try it?

If you are worried that the query actually wraps lines, you can bypass
thatusing line concatenation, e.g.

sql = "SELECT Y, " & _
"01 = COUNT(...)" & _
"..." & _
" ORDER BY Y"

set rs = conn.execute(sql)

Better yet, create a stored procedure, and call that. This kind of data
logic belongs in the database anyway, not in your ASP code.

Jul 22 '05 #6

P: n/a
Hi Bob, I got this to work via the webpage, but I am new to stored
procedures, this seems like a much better way to go. Read the info that you
gave me. I am receiving an error (Arguments are of the wrong type, are out
of acceptable range, or are in conflict with one another.). It is probably
simple.

Here is my ASP code

<connection string info>
strSQL = "EXEC dbo.byProblemType" & _
" @bdate = '" & bDate & "', " & _
" @edate = '" & eDate & "'"
Conn.Execute strSQL, 3, 3

<stored procedure code>
CREATE PROCEDURE dbo.byProblemType
@bDate VARCHAR(40),
@eDate VARCHAR(40)
AS
DECLARE @begdate DATETIME
DECLARE @enddate DATETIME
SELECT @begDate = CONVERT(DATETIME, @bdate)
SELECT @endDate = CONVERT(DATETIME, @edate)
BEGIN
SELECT ProblemType, Count(ProblemType) as ProbType
FROM Problems
WHERE RptDate >= @begDate and RptDate <= @endDate
GROUP BY ProblemType
END

RptDate is a DATETIME data type

Thanks


"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:u2**************@TK2MSFTNGP12.phx.gbl...
As in dynamic sql?

Sure. Why not? Just remove the line breaks and assign the string to a
variable, just as you would normally do with any sql statement.

Of course, I would rather encapsulate it in a stored procedure which can
easily be called from ASP.

http://tinyurl.com/jyy0

Bob Barrows

DanVDM wrote:
Would this work the way it is in ASP?
"Aaron Bertrand [SQL Server MVP]" <te*****@dnartreb.noraa> wrote in
message news:Ok**************@TK2MSFTNGP15.phx.gbl...
This is a single statement. What do you mean by "one line"?

"DanVDM" <dv****@yahoo.com> wrote in message
news:eq**************@tk2msftngp13.phx.gbl...
Came across this code.

SELECT Y,
Q1 = COUNT(CASE WHEN Q=1 THEN Sales END),
Q2 = COUNT(CASE WHEN Q=2 THEN Sales END),
Q3 = COUNT(CASE WHEN Q=3 THEN Sales END),
Q4 = COUNT(CASE WHEN Q=4 THEN Sales END)
FROM
dbo.SalesByQuarter
GROUP BY Y
ORDER BY Y

It does a pivot table type deal.

How would I convert this to a one line SQL statement? Is this
possible.


--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Jul 22 '05 #7

P: n/a
Figured it out on my own. Sorry
"DanVDM" <dv****@yahoo.com> wrote in message
news:ep**************@TK2MSFTNGP12.phx.gbl...
Hi Bob, I got this to work via the webpage, but I am new to stored
procedures, this seems like a much better way to go. Read the info that you gave me. I am receiving an error (Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.). It is probably simple.

Here is my ASP code

<connection string info>
strSQL = "EXEC dbo.byProblemType" & _
" @bdate = '" & bDate & "', " & _
" @edate = '" & eDate & "'"
Conn.Execute strSQL, 3, 3

<stored procedure code>
CREATE PROCEDURE dbo.byProblemType
@bDate VARCHAR(40),
@eDate VARCHAR(40)
AS
DECLARE @begdate DATETIME
DECLARE @enddate DATETIME
SELECT @begDate = CONVERT(DATETIME, @bdate)
SELECT @endDate = CONVERT(DATETIME, @edate)
BEGIN
SELECT ProblemType, Count(ProblemType) as ProbType
FROM Problems
WHERE RptDate >= @begDate and RptDate <= @endDate
GROUP BY ProblemType
END

RptDate is a DATETIME data type

Thanks


"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:u2**************@TK2MSFTNGP12.phx.gbl...
As in dynamic sql?

Sure. Why not? Just remove the line breaks and assign the string to a
variable, just as you would normally do with any sql statement.

Of course, I would rather encapsulate it in a stored procedure which can easily be called from ASP.

http://tinyurl.com/jyy0

Bob Barrows

DanVDM wrote:
Would this work the way it is in ASP?
"Aaron Bertrand [SQL Server MVP]" <te*****@dnartreb.noraa> wrote in
message news:Ok**************@TK2MSFTNGP15.phx.gbl...
> This is a single statement. What do you mean by "one line"?
>
>
>
>
>
> "DanVDM" <dv****@yahoo.com> wrote in message
> news:eq**************@tk2msftngp13.phx.gbl...
>> Came across this code.
>>
>> SELECT Y,
>> Q1 = COUNT(CASE WHEN Q=1 THEN Sales END),
>> Q2 = COUNT(CASE WHEN Q=2 THEN Sales END),
>> Q3 = COUNT(CASE WHEN Q=3 THEN Sales END),
>> Q4 = COUNT(CASE WHEN Q=4 THEN Sales END)
>> FROM
>> dbo.SalesByQuarter
>> GROUP BY Y
>> ORDER BY Y
>>
>> It does a pivot table type deal.
>>
>> How would I convert this to a one line SQL statement? Is this
>> possible.


--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.


Jul 22 '05 #8

P: n/a
DanVDM wrote:
Hi Bob, I got this to work via the webpage, but I am new to stored
procedures, this seems like a much better way to go. Read the info
that you gave me. I am receiving an error (Arguments are of the
wrong type, are out of acceptable range, or are in conflict with one
another.). It is probably simple.

Here is my ASP code

<connection string info>
strSQL = "EXEC dbo.byProblemType" & _
" @bdate = '" & bDate & "', " & _
" @edate = '" & eDate & "'"
Conn.Execute strSQL, 3, 3
You are executing this procedure as if it returned no records, in which case
those extra arguments on your Execute statement are the problem. If your
procedure actually did not return records, you would need to change the
statement to:
Conn.Execute strSQL, ,129

However, this procedure returns records so you need a recordset. Change the
statement to:

Set rs=Conn.Execute (strSQL, ,1)
This will return a serverside, forward-only cursor, which is exactly what
you need.

I find this way of running the procedure to be easier:

'first validate that the two variables contain dates:
on error resume next
bDate=cdate(bDate)
eDate=cdate(eDate)
'catch any errors that occur which will indicate a problem with the dates
'then

Set rs=createobject("adodb.recordset")
Conn.byProblemType bDate, eDate, rs

<stored procedure code>
CREATE PROCEDURE dbo.byProblemType
@bDate VARCHAR(40),
@eDate VARCHAR(40)


HTH,
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
Jul 22 '05 #9

P: n/a
DanVDM wrote:
Figured it out on my own. Sorry

Read my reply anyways. You may find it helpful.

Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
Jul 22 '05 #10

This discussion thread is closed

Replies have been disabled for this discussion.