Returning data in required way

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Returning data in required way

Author

Topic

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2009-10-06 : 06:21:14

Hi,

I have the following data:

Product, platform, Region, Group, Units
toys, floor, uk, 1, 100
toys, floor, us, 1, 400
pens, table, us, 1, 300

The issue I am having is I have a table which generates data on a report based on the users selection.

The table structure is:

Uk, US, Total
Product Value, Value, Value

Obviously when they select a product that doesnt have data for neither UK OR US then the column in the table doesnt show and the table is shrinks.

Any ideas/instructions on how I would go about returning value 0 pens to populate the region, instead of shrinking the table.

Hope that makes sense.

thanks

sanoj_av
Posting Yak Master

118 Posts

Posted - 2009-10-06 : 06:40:53

Can you post the query you written and what will be the user input?

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2009-10-06 : 06:43:22

Select Product, [Platform], Region, [Group], 'Units' AS InfoType, CAST(ROUND(SUM(Units), 0) AS float) AS Value, '1' AS sort
FROM dashboard
WHERE (Product IN (Select value from dbo.ufn_Split(@product,','))) AND (Country in (Select value from dbo.ufn_Split(@country,',')))
GROUP BY Product, Platform, Region, [Group]
HAVING (FreePaid = 'Paid')

quote:
Originally posted by sanoj_av

Can you post the query you written and what will be the user input?

sanoj_av
Posting Yak Master

118 Posts

Posted - 2009-10-06 : 06:57:20

use GROUP BY ALL instead of GROUP BY and use a ISNULL() function to Units field to dispaly it as zero.

sanoj_av
Posting Yak Master

118 Posts

Posted - 2009-10-06 : 07:01:03

I mean,

Select
Product,
[Platform],
Region,
[Group],
'Units' AS InfoType,
CAST(ROUND(ISNULL(SUM(Units),0), 0) AS float) AS Value,
'1' AS sort
FROM
dashboard
WHERE
(Product IN (Select value from dbo.ufn_Split(@product,','))) AND
(Country in (Select value from dbo.ufn_Split(@country,',')))
GROUP BY ALL
Product, Platform, Region, [Group]

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-10-06 : 07:09:33

From BOL:

Non-ISO Compliant Syntax
ALL 
This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in
 new development work, and plan to modify applications that currently use this feature. Includes all
 groups and result sets, even those that do not have any rows that meet the search condition specified 
in the WHERE clause. When ALL is specified, null values are returned for the summary columns of groups 
that do not meet the search condition. You cannot specify ALL with the CUBE or ROLLUP operators.


GROUP BY ALL is not supported in queries that access remote tables if there is also a WHERE clause in 
the query. GROUP BY ALL will fail on columns that have the FILESTREAM attribute.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2009-10-06 : 08:33:54

Any other solutions then to using all?

quote:
Originally posted by DonAtWork

From BOL:
Non-ISO Compliant Syntax
ALL
This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in
new development work, and plan to modify applications that currently use this feature. Includes all
groups and result sets, even those that do not have any rows that meet the search condition specified
in the WHERE clause. When ALL is specified, null values are returned for the summary columns of groups
that do not meet the search condition. You cannot specify ALL with the CUBE or ROLLUP operators.

GROUP BY ALL is not supported in queries that access remote tables if there is also a WHERE clause in
the query. GROUP BY ALL will fail on columns that have the FILESTREAM attribute.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources