Supress Repeating Name

[Chamark via SQLMonster.com]

Using SQL2008 - Between beginner & intermediate. I don't want to repeat the
same row name in my listing from a SQL statement. Column names are Category,
KPI, Actual_YTD, Target_YTD.

Select Category, KPI, Actual_YTD, Target_YTD
Where Yearmonth = '07/01/2010'

List would look like this

Category KPI Actual_YTD Target_YTD
Profitability Income $$$$$ $$$$$$$
Profitability Cost $$$$$ $$$$$$$
Profitabilty Losses $$$$$ $$$$$$$
Satisfaction CEI %%% %%%%
Satisfaction Clients %%% %%%%

Is there any way of only having Profitability & Satisfaction show once
instead of on each row? Any help is greatly appreciated

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programming/201008/1

[Erland Sommarskog]

Chamark via SQLMonster.com (u21870@uwe) writes:
> Using SQL2008 - Between beginner & intermediate. I don't want to repeat
> the same row name in my listing from a SQL statement. Column names are
> Category, KPI, Actual_YTD, Target_YTD.
>
> Select Category, KPI, Actual_YTD, Target_YTD
> Where Yearmonth = '07/01/2010'
>
> List would look like this
>
> Category KPI Actual_YTD Target_YTD
> Profitability Income $$$$$ $$$$$$$
> Profitability Cost $$$$$ $$$$$$$
> Profitabilty Losses $$$$$ $$$$$$$
> Satisfaction CEI %%% %%%%
> Satisfaction Clients %%% %%%%
>
> Is there any way of only having Profitability & Satisfaction show once
> instead of on each row? Any help is greatly appreciated

WITH yourquery AS (
SELECT yourcolumns,
rowno = row_number() OVER (PARTITION BY Category
ORDER BY yourorder)
FROM ...
)
SELECT Category = CASE rowno WHEN 1 THEN Category ELSE '' END, ...
FROM yourquery

But I would recommend handling this in the presentation layer instead.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[--CELKO--]

You have not made it to intermediate yet :) Back to basics. A query
returns a table. A table is made up of rows which represent a single
fact or entity. All rows have the same columns. All columns are drawn
from the same domain. They are not dependent on the physical position
in the printout.

What you are doing is violating First Normal Form and trying to format
a REPORT in the database. This also violates the basic idea of a
tiered architecture.

[sam]

> Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programming/2010...

Since KPI is Different for Each Row So you can not Show Profitability
and Satisfaction in a Single Row.
If You want to show them in a Sigle Line i will suggest you to show
comma separated list of KPI for Each Category.
You can Acheive Comma Separated List Either by a View or UDF.

[Chamark via SQLMonster.com]

Thanks Erland - This works - sort of - The non-repeating rows show one
instance as I wanted but when I attempt to include the rest of the columns I
get the error multi-part indentifier[column name]could not be bound - If I
use * after the " " END, * I get all my columns, as well as the repeating row
names. See code. If I just use " "END I only get the Category column. Your
thoughts?

WITH myquery AS (
SELECT TOP 10000 t_kpi_Categories.CatName, t_kpi_DataSources_AliasNames.
AliasName, t_kpi_Data.Actual_YTD, t_kpi_Data.Target_YTD, t_kpi_Data.
Actual_Month,
t_kpi_Data.Target_Month,
rowno = ROW_NUMBER() OVER (PARTITION BY CATNAME ORDER BY t_kpi_SAT_SCMeta.
order_of_appearance )

FROM t_kpi_SAT_SCMeta INNER JOIN
t_kpi_Categories ON t_kpi_SAT_SCMeta.Cat_ID =
t_kpi_Categories.CatID INNER JOIN
t_kpi_DataSources_AliasNames ON t_kpi_SAT_SCMeta.
TDAF_ID = t_kpi_DataSources_AliasNames.AliasID INNER JOIN
t_kpi_Data ON t_kpi_SAT_SCMeta.TDAF_ID = t_kpi_Data.
DataSource_ID

WHERE (t_kpi_SAT_SCMeta.SC_Name LIKE '%DB EXec%') AND YearMonth =
'06/01/2010'
ORDER BY t_kpi_SAT_SCMeta.order_of_appearance
)
SELECT Category = CASE rowno WHEN 1 THEN Catname ELSE ''END, *
FROM myquery

Erland Sommarskog wrote:
>> Using SQL2008 - Between beginner & intermediate. I don't want to repeat
>> the same row name in my listing from a SQL statement. Column names are

>[quoted text clipped - 14 lines]

>> Is there any way of only having Profitability & Satisfaction show once
>> instead of on each row? Any help is greatly appreciated
>
>WITH yourquery AS (
> SELECT yourcolumns,
> rowno = row_number() OVER (PARTITION BY Category
> ORDER BY yourorder)
> FROM ...
>)
>SELECT Category = CASE rowno WHEN 1 THEN Category ELSE '' END, ...
>FROM yourquery
>
>But I would recommend handling this in the presentation layer instead.
>

--

[Erland Sommarskog]

Chamark via SQLMonster.com (u21870@uwe) writes:

> Thanks Erland - This works - sort of - The non-repeating rows show one
> instance as I wanted but when I attempt to include the rest of the
> columns I get the error multi-part indentifier[column name]could not be
> bound - If I use * after the " " END, * I get all my columns, as well as
> the repeating row names. See code. If I just use " "END I only get the
> Category column. Your thoughts?

You should use any column prefixes when you select from the CTE (Common
Table Expression), unless you define an alias for the CTE. The is like a
view, but visible only for the query.

Also, the TOP and the ORDER BY clauses should be in the final query, not the
CTE.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Chamark via SQLMonster.com]

Again thanks for your help - Obviously I am closer to beginner than
internediate - I'll work on what you have given me and hopefully figure it
out. I know I should let the presentation layer handle this - but I am using
SSRS 2008 and don't know of a way to do this with that tool.

Erland Sommarskog wrote:
>> Thanks Erland - This works - sort of - The non-repeating rows show one
>> instance as I wanted but when I attempt to include the rest of the
>> columns I get the error multi-part indentifier[column name]could not be
>> bound - If I use * after the " " END, * I get all my columns, as well as
>> the repeating row names. See code. If I just use " "END I only get the
>> Category column. Your thoughts?
>
>You should use any column prefixes when you select from the CTE (Common
>Table Expression), unless you define an alias for the CTE. The is like a
>view, but visible only for the query.
>
>Also, the TOP and the ORDER BY clauses should be in the final query, not the
>CTE.
>

--