Display results like this using SQL

Using an SQL query I am getting results as follows:
Date Name Amount
-- -- --
1-Nov-2007 Susan 1000
1-Nov-2007 Derek 2000
1-Nov-2007 Mike J 1050
2-Nov-2007 Susan 2500
2-Nov-2007 Mike J 7289
I want to show the results like this using SQL query:
1-Nov-2007 Susan 1000
Derek 2000
Mike J 1050
2-Nov-2007 Susan 2500
Mike J 7289"RP" <rpk.general@.gmail.com> wrote in message
news:1194500368.511146.82040@.z24g2000prh.googlegroups.com...
> Using an SQL query I am getting results as follows:
> Date Name Amount
> -- -- --
> 1-Nov-2007 Susan 1000
> 1-Nov-2007 Derek 2000
> 1-Nov-2007 Mike J 1050
> 2-Nov-2007 Susan 2500
> 2-Nov-2007 Mike J 7289
>
> I want to show the results like this using SQL query:
> 1-Nov-2007 Susan 1000
> Derek 2000
> Mike J 1050
> 2-Nov-2007 Susan 2500
> Mike J 7289
>
For what purpose? This looks more like a report than a result set, ie: fine
for display and printing but useless for anything in SQL. Your reporting
tool will be able to do it using "groups" or "bands" on the page.
--
David Portas|||RP
I colmpetely agree with David. Do that on the cclient side
--SQL Server 2005
WITH cte
AS
(
SELECT CustomerID,OrderID,ROW_NUMBER()
OVER(PARTITION BY CustomerID ORDER BY CustomerID) AS rn FROM Orders
) SELECT CASE WHEN rn>1 THEN REPLACE(CustomerID,CustomerID,'') ELSE
CustomerID END AS CustomerID
,OrderID FROM cte
"RP" <rpk.general@.gmail.com> wrote in message
news:1194500368.511146.82040@.z24g2000prh.googlegroups.com...
> Using an SQL query I am getting results as follows:
> Date Name Amount
> -- -- --
> 1-Nov-2007 Susan 1000
> 1-Nov-2007 Derek 2000
> 1-Nov-2007 Mike J 1050
> 2-Nov-2007 Susan 2500
> 2-Nov-2007 Mike J 7289
>
> I want to show the results like this using SQL query:
> 1-Nov-2007 Susan 1000
> Derek 2000
> Mike J 1050
> 2-Nov-2007 Susan 2500
> Mike J 7289
>|||Oracle has SQL analytics which does this through SQL. The situation is
that I want it by SQL alone.
On Nov 8, 2:14 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> RP
> I colmpetely agree with David. Do that on the cclient side
> --SQL Server 2005
> WITH cte
> AS
> (
> SELECT CustomerID,OrderID,ROW_NUMBER()
> OVER(PARTITION BY CustomerID ORDER BY CustomerID) AS rn FROM Orders
> ) SELECT CASE WHEN rn>1 THEN REPLACE(CustomerID,CustomerID,'') ELSE
> CustomerID END AS CustomerID
> ,OrderID FROM cte
>|||SQL Server is NOT Oracle :-)))
"RP" <rpk.general@.gmail.com> wrote in message
news:1194518385.233199.140630@.v29g2000prd.googlegroups.com...
> Oracle has SQL analytics which does this through SQL. The situation is
> that I want it by SQL alone.
> On Nov 8, 2:14 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
>> RP
>> I colmpetely agree with David. Do that on the cclient side
>> --SQL Server 2005
>> WITH cte
>> AS
>> (
>> SELECT CustomerID,OrderID,ROW_NUMBER()
>> OVER(PARTITION BY CustomerID ORDER BY CustomerID) AS rn FROM Orders
>> ) SELECT CASE WHEN rn>1 THEN REPLACE(CustomerID,CustomerID,'') ELSE
>> CustomerID END AS CustomerID
>> ,OrderID FROM cte
>|||From my Googling of "SQL Analytics" it seems to be pretty much the OVER clause what we now have in
2005, which you seem to already have a suggestion for.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"RP" <rpk.general@.gmail.com> wrote in message
news:1194518385.233199.140630@.v29g2000prd.googlegroups.com...
> Oracle has SQL analytics which does this through SQL. The situation is
> that I want it by SQL alone.
> On Nov 8, 2:14 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
>> RP
>> I colmpetely agree with David. Do that on the cclient side
>> --SQL Server 2005
>> WITH cte
>> AS
>> (
>> SELECT CustomerID,OrderID,ROW_NUMBER()
>> OVER(PARTITION BY CustomerID ORDER BY CustomerID) AS rn FROM Orders
>> ) SELECT CASE WHEN rn>1 THEN REPLACE(CustomerID,CustomerID,'') ELSE
>> CustomerID END AS CustomerID
>> ,OrderID FROM cte
>|||Just because something CAN be done, does not mean it is CORRECT to do it
that way! ;-)
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"RP" <rpk.general@.gmail.com> wrote in message
news:1194518385.233199.140630@.v29g2000prd.googlegroups.com...
> Oracle has SQL analytics which does this through SQL. The situation is
> that I want it by SQL alone.
> On Nov 8, 2:14 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
>> RP
>> I colmpetely agree with David. Do that on the cclient side
>> --SQL Server 2005
>> WITH cte
>> AS
>> (
>> SELECT CustomerID,OrderID,ROW_NUMBER()
>> OVER(PARTITION BY CustomerID ORDER BY CustomerID) AS rn FROM Orders
>> ) SELECT CASE WHEN rn>1 THEN REPLACE(CustomerID,CustomerID,'') ELSE
>> CustomerID END AS CustomerID
>> ,OrderID FROM cte
>|||"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13j64g4c7sp9k1a@.corp.supernews.com...
> Just because something CAN be done, does not mean it is CORRECT to do it
> that way! ;-)
Does that thought also cover:
http://www.sommarskog.se/arrays-in-sql-2005.html
http://sqlblogcasts.com/blogs/tonyrogerson/archive/2007/08/05/passing-an-array-csv-to-a-stored-procedure-with-data-validation-no-loops-no-self-joins-just-replace.aspx
? :)
www.beyondsql.blogspot.com|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:uRlI6OdIIHA.2480@.TK2MSFTNGP05.phx.gbl...
>.
> For what purpose? This looks more like a report than a result set, ie:
> fine for display and printing but useless for anything in SQL. Your
> reporting tool will be able to do it using "groups" or "bands" on the
> page.
>
So it's the end that justify the means? That's rather expedient don't
don't you think. So it's just expediency that justifies:
http://www.sommarskog.se/arrays-in-sql-2005.html
www.beyondsql.blogspot.com