Distinct Total

Hi,

I hope someone can help with my problem described below.

I have a dataset with the following information....

Code1 Amount Code2

-- --

56209087 249.35 N01L1

56209087 249.35 N01L2

56209087 249.35 N02L1

56209903 437.52 N04L1

56209903 437.52 N01L1

56209903 437.52 N01L2

56209903 437.52 N03L1

I need my SSRS report to display a total field which is the sum of the distinct "Amount" values.... (249.35 + 437.52) = 686.87. I need to get the distinct amount per Code1 and then sum these distinct amounts.

Thanks

Hi Lorraine M

Create On more Dataset (Totals) with the following Query

Eg:

SELECT SUM(Amount) AS Total FROM

(SELECT DISTINCT code1, Amount FROM [Give the First data set Query here]) AS S

In the Report Give the Following Expression for the Sum field
=Sum(Fields!Total.Value, "Totals")

This will cause executing first data set two times

|||

Hi Chandra,

Thanks for your response.

I forgot to mention that the dataset is constructed not from a query but a recordset returned by a procedure. Therefore I cannot manipulate a query to add a new column. I should have mentioned in my first post.

Lorraine.

|||

Hello Lorraine,

This will guide you exactly to solve this problem. I was experiencing the same scenario and this link helped me.

Deepak -- > http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=831083&SiteID=1

bWelcker --> http://blogs.msdn.com/bwelcker/archive/2005/05/10/416306.aspx

Hope this helps..

-- Deepak

|||Thanks Deepak. I created a function similar to the example above. I have one issue to iron out but this approach should work. Thanks for your help.|||Could you please mark this as answer. Anyone with similar question can view this.

Distinct Total

Hi,

I hope someone can help with my problem described below.

I have a dataset with the following information....

Code1 Amount Code2

-- --

56209087 249.35 N01L1

56209087 249.35 N01L2

56209087 249.35 N02L1

56209903 437.52 N04L1

56209903 437.52 N01L1

56209903 437.52 N01L2

56209903 437.52 N03L1

I need my SSRS report to display a total field which is the sum of the distinct "Amount" values.... (249.35 + 437.52) = 686.87. I need to get the distinct amount per Code1 and then sum these distinct amounts.

Thanks

Hi Lorraine M

Create On more Dataset (Totals) with the following Query

Eg:

SELECT SUM(Amount) AS Total FROM

(SELECT DISTINCT code1, Amount FROM [Give the First data set Query here]) AS S

In the Report Give the Following Expression for the Sum field
=Sum(Fields!Total.Value, "Totals")

This will cause executing first data set two times

|||

Hi Chandra,

Thanks for your response.

I forgot to mention that the dataset is constructed not from a query but a recordset returned by a procedure. Therefore I cannot manipulate a query to add a new column. I should have mentioned in my first post.

Lorraine.

|||

Hello Lorraine,

This will guide you exactly to solve this problem. I was experiencing the same scenario and this link helped me.

Deepak -- > http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=831083&SiteID=1

bWelcker --> http://blogs.msdn.com/bwelcker/archive/2005/05/10/416306.aspx

Hope this helps..

-- Deepak

|||Thanks Deepak. I created a function similar to the example above. I have one issue to iron out but this approach should work. Thanks for your help.|||Could you please mark this as answer. Anyone with similar question can view this.sql

DISTINCT COUNT WITH NULL VALUES (GRAND TOTAL)

Hello,

I have a DB of professors and information related with them. I created the cube, it consist of:

Measures:

Measure group Professors:

Amount of projects (COUNT proj_id)

Amount of pulications (COUNT pub_id)

Amount of e_books (COUNT book_id)

--

Measure group Projects:

Distinct amount of projects (DISTINCT COUNT proj_id)

--

Measure group Publications:

Distinct amount of publications (DISTINCT COUNT pub_id)

--

Measure group E_books:

Distinct amount of e_books (DISTINCT COUNT book_id)

Calculated measures:

Amnt_Projects

iif ([Measures].[ Amount of projects ] = 0 OR [Measures].[ Amount of projects] = NULL,0,[Measures].[ Distinct amount of projects])

Amnt_Publications

(similar to the above one)

Amnt_E_books

(similar to the above one)

Dimensions:

dimPROFESSORS

- prof_id

-surname

-name

-gender

dimPROJECTS

- proj_id

-type name

-name

dimPUBLICATIONS

- pub_id

-type name

-name

dimE_BOOKS

- book_id

-name

Data_Projects

-data_id

-years

Data_Publications

-data_id

-years

Data_E_books

-data_id

-years

For example, when I browse the cube:

prof_id Amount of projects Distinct amount of projects Amnt_Projects

1032 30 1 1

1070 90 2 2

1111 0 1 0

1137 0 1 0

1234 1404 9 9

1721 504 7 7

2661 85 5 5

... ... ... ...

6999 20 1 1

9956 50 5 5

Uknown 0

Grand Total 2421 11 11

Grand Total “11“ is the amount of distinct projects +1 (because of the unknown member). So the last column shows the right amount of projects for the professor but I want Grand Total to sum those values and show, how many projects do the professors have (it should be ?59“ for all professors). How could I get the right value to be shown in Grand Total?

Any help would be appreciated as I'm very new to the MDX.

|||

Maybe you need to sum the project counts for each professor in the selection, like:

Amnt_Projects

Sum(existing [DimProfessors].[prof_id].[prof_id], [Measures].[ Distinct amount of projects])

|||Thank You Deepak. This calculation works perfectly, just i added my calculated measure expression instead of [Measures].[ Distinct amount of projects]. I am very happy that at last it works!!!
|||Hello again. I was working with KPI's and noticed, that this calculation needs to be improved. When I try to filter by any particular professor, the Grand Total remains the same - for the projects always "59". How could I change the calculateted member?

Thank You in advance.
|||

Were you testing the KPI with the KPI Browser in BIDS? If so, please check directly with an MDX query, since the browser may be using a subselect vs. where. The query would be like:

select

KPIValue("ProfCalc") on 0

from ProfCube

where [DimProfessors].[prof_id].&

|||I tested Your SQL query, and You are right. This query works fine. But how can I make the KPI browser in BIDS show the right values (I use the Amnt_Projects calculation for the KPIValue expression)? The same with cube browser:

prof_id Amnt_Projects
1032 1
1070 2
1111 0
Grand Total 59

Thank You again. I appreciate Your help very much!
|||

In that case, a different approach may be needed, based on a recent Forum post:

- Create a new "row count" measure called Amnt_Projects for the Professors measure group.

- Add a statement to the cube MDX script, assigning values at the DimProfessors leaf level to Amnt_Projects:

([DimProfessors].[prof_id].[prof_id], [Measures].[Amnt_Projects]) = [Measures].[ Distinct amount of projects];

|||I don't know if I'm doing something wrong, but in this case I get #VALUE! for every professor.
First, I created a row count measure Amnt_Projects in the Professors measure group. Than I created calculated measure as You wrote and named it Amnt_projects2.

What is more, I am creating reports using this cube. I tried to make the one similar to the "Teritory Sales Drilldown" example. But the strange thing is with Amnt_Projects (the calculation, that You provided earlier). I make this kind of drilldown: Professor (Name/Surname) and Amnt_Publications->Type of Publication and Amnt_Publications->Name of Publication and Amnt_Publications

I get the results:
Professor Type of Publication Name of Publication Amnt_Publication
Professor1 #Error
Type1 0
Name1 0
Name2 0
Type2 #Error
Name3 1
Name4 0

So when the value of Amnt_Publication is 0, everything is ok, but when it has to sum one's, it shows #Error.

Thank You!
|||By the way, I get this cind of warning in the reporting services when I preview the report:
"The Value expression for the textbox ‘Amnt_Publications’ uses an aggregate function on data of varying data types. Aggregate functions other than First, Last, Previous, Count, and CountDistinct can only aggregate data of a single data type."
|||I have just notices that using distinct count fits me very well in this reporting services situation. It behaves very differently than in Analysis Services browser. In Reporting Services it counts distinct values and shows null value for those professors that have no publications. And in Analysis Services it shows "1" for null value. Indeed strange.

Still would appreciate Your help with those Amnt in Analysis Services, which I use for KPI value and browse in KPI browser.

Thank You!
|||"I created calculated measure as You wrote and named it Amnt_projects2" - in the approach which I suggested, there is no calculated measure. The cube script assignment applies to the new cube measure: "Amnt_Projects".|||Thank You for answering. But could You be more specific? (about "The cube script assignment applies to the new cube measure: "Amnt_Projects". ")This is my first try with SQL Server and I have only a couple of days to finish this.

What is more, I need those KPI using not the whole Amount of projects, but something like this:

KPI for projects = Amount of Type1 projects*0,6 + Amount of Type2projects *0,3 + Amount of Type3*0,1

as I mentioned, I have such kind of dimension Projects:

dimPROJECTS

- proj_id

-type name (there are three Types of projects)

-name (the name of project itself)

Thank You very much!
|||OK, I have just assigned the KPI value by myself and it works.Of course it works only in MS SQL Server Manegement Studio..I tested it by SQL query and it should work in Reporting Services. But because I'm still using that previous Amnt_projects -->Sum(existing [DimProfessors].[prof_id].[prof_id], [Measures].[ Distinct amount of projects]), it doesn't work properly in KPI browser. The KPI Value expresion is:

SUM([Dim Projects].[Type Name].&[Type1],[Measures].[Amnt_Projects])* 0.6 + SUM([Dim Projects].[Type Name].&[Type2],[Measures].[Amnt_Projects])* 0.3 + SUM([Dim Projects].[Type Name].&[Type3],[Measures].[Amnt_Projects]) * 0.1

So I would appreciate Your explanation about "The cube script assignment applies to the new cube measure: "Amnt_Projects". "

Thank You in advance!
|||

Not sure whether you reviewed the earlier post, which I provided a link to - but the approach I suggested is similar:

- Create a new "row count" measure called Amnt_Projects for the Professors measure group (this replaces the calculated measure: Amnt_Projects)

- Add this statement to the cube MDX script, assigning values at the DimProfessors leaf level to Amnt_Projects (it doesn't create any new measures):

([DimProfessors].[prof_id].[prof_id], [Measures].[Amnt_Projects]) = [Measures].[ Distinct amount of projects];

DISTINCT COUNT WITH NULL VALUES (GRAND TOTAL)

Hello,

I have a DB of professors and information related with them. I created the cube, it consist of:

Measures:

Measure group Professors:

Amount of projects (COUNT proj_id)

Amount of pulications (COUNT pub_id)

Amount of e_books (COUNT book_id)

--

Measure group Projects:

Distinct amount of projects (DISTINCT COUNT proj_id)

--

Measure group Publications:

Distinct amount of publications (DISTINCT COUNT pub_id)

--

Measure group E_books:

Distinct amount of e_books (DISTINCT COUNT book_id)

Calculated measures:

Amnt_Projects

iif ([Measures].[ Amount of projects ] = 0 OR [Measures].[ Amount of projects] = NULL,0,[Measures].[ Distinct amount of projects])

Amnt_Publications

(similar to the above one)

Amnt_E_books

(similar to the above one)

Dimensions:

dimPROFESSORS

- prof_id

-surname

-name

-gender

dimPROJECTS

- proj_id

-type name

-name

dimPUBLICATIONS

- pub_id

-type name

-name

dimE_BOOKS

- book_id

-name

Data_Projects

-data_id

-years

Data_Publications

-data_id

-years

Data_E_books

-data_id

-years

For example, when I browse the cube:

prof_id Amount of projects Distinct amount of projects Amnt_Projects

1032 30 1 1

1070 90 2 2

1111 0 1 0

1137 0 1 0

1234 1404 9 9

1721 504 7 7

2661 85 5 5

... ... ... ...

6999 20 1 1

9956 50 5 5

Uknown 0

Grand Total 2421 11 11

Grand Total “11“ is the amount of distinct projects +1 (because of the unknown member). So the last column shows the right amount of projects for the professor but I want Grand Total to sum those values and show, how many projects do the professors have (it should be ?59“ for all professors). How could I get the right value to be shown in Grand Total?

Any help would be appreciated as I'm very new to the MDX.

|||

Maybe you need to sum the project counts for each professor in the selection, like:

Amnt_Projects

Sum(existing [DimProfessors].[prof_id].[prof_id], [Measures].[ Distinct amount of projects])

|||Thank You Deepak. This calculation works perfectly, just i added my calculated measure expression instead of [Measures].[ Distinct amount of projects]. I am very happy that at last it works!!!
|||Hello again. I was working with KPI's and noticed, that this calculation needs to be improved. When I try to filter by any particular professor, the Grand Total remains the same - for the projects always "59". How could I change the calculateted member?

Thank You in advance.
|||

Were you testing the KPI with the KPI Browser in BIDS? If so, please check directly with an MDX query, since the browser may be using a subselect vs. where. The query would be like:

select

KPIValue("ProfCalc") on 0

from ProfCube

where [DimProfessors].[prof_id].&

|||I tested Your SQL query, and You are right. This query works fine. But how can I make the KPI browser in BIDS show the right values (I use the Amnt_Projects calculation for the KPIValue expression)? The same with cube browser:

prof_id Amnt_Projects
1032 1
1070 2
1111 0
Grand Total 59

Thank You again. I appreciate Your help very much!
|||

In that case, a different approach may be needed, based on a recent Forum post:

- Create a new "row count" measure called Amnt_Projects for the Professors measure group.

- Add a statement to the cube MDX script, assigning values at the DimProfessors leaf level to Amnt_Projects:

([DimProfessors].[prof_id].[prof_id], [Measures].[Amnt_Projects]) = [Measures].[ Distinct amount of projects];

|||I don't know if I'm doing something wrong, but in this case I get #VALUE! for every professor.
First, I created a row count measure Amnt_Projects in the Professors measure group. Than I created calculated measure as You wrote and named it Amnt_projects2.

What is more, I am creating reports using this cube. I tried to make the one similar to the "Teritory Sales Drilldown" example. But the strange thing is with Amnt_Projects (the calculation, that You provided earlier). I make this kind of drilldown: Professor (Name/Surname) and Amnt_Publications->Type of Publication and Amnt_Publications->Name of Publication and Amnt_Publications

I get the results:
Professor Type of Publication Name of Publication Amnt_Publication
Professor1 #Error
Type1 0
Name1 0
Name2 0
Type2 #Error
Name3 1
Name4 0

So when the value of Amnt_Publication is 0, everything is ok, but when it has to sum one's, it shows #Error.

Thank You!
|||By the way, I get this cind of warning in the reporting services when I preview the report:
"The Value expression for the textbox ‘Amnt_Publications’ uses an aggregate function on data of varying data types. Aggregate functions other than First, Last, Previous, Count, and CountDistinct can only aggregate data of a single data type."
|||I have just notices that using distinct count fits me very well in this reporting services situation. It behaves very differently than in Analysis Services browser. In Reporting Services it counts distinct values and shows null value for those professors that have no publications. And in Analysis Services it shows "1" for null value. Indeed strange.

Still would appreciate Your help with those Amnt in Analysis Services, which I use for KPI value and browse in KPI browser.

Thank You!
|||"I created calculated measure as You wrote and named it Amnt_projects2" - in the approach which I suggested, there is no calculated measure. The cube script assignment applies to the new cube measure: "Amnt_Projects".|||Thank You for answering. But could You be more specific? (about "The cube script assignment applies to the new cube measure: "Amnt_Projects". ")This is my first try with SQL Server and I have only a couple of days to finish this.

What is more, I need those KPI using not the whole Amount of projects, but something like this:

KPI for projects = Amount of Type1 projects*0,6 + Amount of Type2projects *0,3 + Amount of Type3*0,1

as I mentioned, I have such kind of dimension Projects:

dimPROJECTS

- proj_id

-type name (there are three Types of projects)

-name (the name of project itself)

Thank You very much!
|||OK, I have just assigned the KPI value by myself and it works.Of course it works only in MS SQL Server Manegement Studio..I tested it by SQL query and it should work in Reporting Services. But because I'm still using that previous Amnt_projects -->Sum(existing [DimProfessors].[prof_id].[prof_id], [Measures].[ Distinct amount of projects]), it doesn't work properly in KPI browser. The KPI Value expresion is:

SUM([Dim Projects].[Type Name].&[Type1],[Measures].[Amnt_Projects])* 0.6 + SUM([Dim Projects].[Type Name].&[Type2],[Measures].[Amnt_Projects])* 0.3 + SUM([Dim Projects].[Type Name].&[Type3],[Measures].[Amnt_Projects]) * 0.1

So I would appreciate Your explanation about "The cube script assignment applies to the new cube measure: "Amnt_Projects". "

Thank You in advance!
|||

Not sure whether you reviewed the earlier post, which I provided a link to - but the approach I suggested is similar:

- Create a new "row count" measure called Amnt_Projects for the Professors measure group (this replaces the calculated measure: Amnt_Projects)

- Add this statement to the cube MDX script, assigning values at the DimProfessors leaf level to Amnt_Projects (it doesn't create any new measures):

([DimProfessors].[prof_id].[prof_id], [Measures].[Amnt_Projects]) = [Measures].[ Distinct amount of projects];

sql

Displaying Price in Microsoft reporting services

hai

I am using Microsoft reporting services.
I am using XSD as report datasource to bind data to the report
In the report, i want to display Amount in french belgiun format in a column eg. 123.456,00 €
While passing the value through datasource, i cant able to pass the value as decimal.
I am only passing it as string. While exporting to excel ,i cant able to make calculations on this field
because it is exported as string.
Is there is any other way to dispaly the value as 123.456,00 € in the report and exported to excel as numeric
to perform calculations
Plz give your suggestions

Regards,

Sakthi

hai

I have solved this problem by using Laguage property for the report.

You can change the report language to french -belgium

And now you can able to display decimal value in french format

Regards

Sakthi

Displaying data in Columns based on Criteria

I have data in rows that I want to display in columns based on a value in
each row.
Here's an example of the data:
Employee ID Type Code Amount
123 PAY BONUS 1,000
123 PAY SALARY 5,000
123 DED INS 500
123 DED DENTAL 100
123 DED FLEX 50
Here's how I'd like to display the data:
EMPLOYEE CODE AMOUNT CODE AMOUNT
123 BONUS 1,000 INS 500
123 SALARY 5,000 DENTAL 100
123 FLEX 50
My problem is understanding how to display the items in each column starting
at the top.
Thanks for any help.
--
Charles Allen, MVPOn Sep 30, 9:20 am, Charles Allen <cal...@.nospam-bkd.com> wrote:
> I have data in rows that I want to display in columns based on a value in
> each row.
> Here's an example of the data:
> Employee ID Type Code Amount
> 123 PAY BONUS 1,000
> 123 PAY SALARY 5,000
> 123 DED INS 500
> 123 DED DENTAL 100
> 123 DED FLEX 50
> Here's how I'd like to display the data:
> EMPLOYEE CODE AMOUNT CODE AMOUNT
> 123 BONUS 1,000 INS 500
> 123 SALARY 5,000 DENTAL 100
> 123 FLEX 50
> My problem is understanding how to display the items in each column starting
> at the top.
> Thanks for any help.
> --
> Charles Allen, MVP
The best way to produce this type of layout is to create a matrix
report where your pivot column (the column that gets split into
multiple columns based on distinct values) is Type. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||I'll give it a shot. Thanks
--
Charles Allen, MVP
"EMartinez" wrote:
> On Sep 30, 9:20 am, Charles Allen <cal...@.nospam-bkd.com> wrote:
> > I have data in rows that I want to display in columns based on a value in
> > each row.
> >
> > Here's an example of the data:
> > Employee ID Type Code Amount
> > 123 PAY BONUS 1,000
> > 123 PAY SALARY 5,000
> > 123 DED INS 500
> > 123 DED DENTAL 100
> > 123 DED FLEX 50
> >
> > Here's how I'd like to display the data:
> >
> > EMPLOYEE CODE AMOUNT CODE AMOUNT
> > 123 BONUS 1,000 INS 500
> > 123 SALARY 5,000 DENTAL 100
> > 123 FLEX 50
> >
> > My problem is understanding how to display the items in each column starting
> > at the top.
> >
> > Thanks for any help.
> > --
> > Charles Allen, MVP
>
> The best way to produce this type of layout is to create a matrix
> report where your pivot column (the column that gets split into
> multiple columns based on distinct values) is Type. Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||Please do suggest me on the below requirement in addition to what Charles
Allen has asked for:
How can I add another column that shows the difference between first CODE
column value and second CODE column value?
Thanks in advance!
"Charles Allen" <callen@.nospam-bkd.com> wrote in message
news:AD136641-F3A8-42B3-A900-92E78174D443@.microsoft.com...
> I'll give it a shot. Thanks
> --
> Charles Allen, MVP
>
> "EMartinez" wrote:
>> On Sep 30, 9:20 am, Charles Allen <cal...@.nospam-bkd.com> wrote:
>> > I have data in rows that I want to display in columns based on a value
>> > in
>> > each row.
>> >
>> > Here's an example of the data:
>> > Employee ID Type Code Amount
>> > 123 PAY BONUS 1,000
>> > 123 PAY SALARY 5,000
>> > 123 DED INS 500
>> > 123 DED DENTAL 100
>> > 123 DED FLEX 50
>> >
>> > Here's how I'd like to display the data:
>> >
>> > EMPLOYEE CODE AMOUNT CODE AMOUNT
>> > 123 BONUS 1,000 INS 500
>> > 123 SALARY 5,000 DENTAL 100
>> > 123 FLEX
>> > 50
>> >
>> > My problem is understanding how to display the items in each column
>> > starting
>> > at the top.
>> >
>> > Thanks for any help.
>> > --
>> > Charles Allen, MVP
>>
>> The best way to produce this type of layout is to create a matrix
>> report where your pivot column (the column that gets split into
>> multiple columns based on distinct values) is Type. Hope this helps.
>> Regards,
>> Enrique Martinez
>> Sr. Software Consultant
>>|||On Sep 30, 11:20 pm, Charles Allen <cal...@.nospam-bkd.com> wrote:
> I'll give it a shot. Thanks
> --
> Charles Allen, MVP
> "EMartinez" wrote:
> > On Sep 30, 9:20 am, Charles Allen <cal...@.nospam-bkd.com> wrote:
> > > I have data in rows that I want to display in columns based on a value in
> > > each row.
> > > Here's an example of the data:
> > > Employee ID Type Code Amount
> > > 123 PAY BONUS 1,000
> > > 123 PAY SALARY 5,000
> > > 123 DED INS 500
> > > 123 DED DENTAL 100
> > > 123 DED FLEX 50
> > > Here's how I'd like to display the data:
> > > EMPLOYEE CODE AMOUNT CODE AMOUNT
> > > 123 BONUS 1,000 INS 500
> > > 123 SALARY 5,000 DENTAL 100
> > > 123 FLEX 50
> > > My problem is understanding how to display the items in each column starting
> > > at the top.
> > > Thanks for any help.
> > > --
> > > Charles Allen, MVP
> > The best way to produce this type of layout is to create a matrix
> > report where your pivot column (the column that gets split into
> > multiple columns based on distinct values) is Type. Hope this helps.
> > Regards,
> > Enrique Martinez
> > Sr. Software Consultant
You're welcome. Let me know if I can be of further assistance.
Regards,
Enrique Martinez
Sr. Software Consultant

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