Duplicate data in a report - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Duplicate data in a report

Author

Topic

hallyvaux
Starting Member

12 Posts

Posted - 2011-05-03 : 09:52:55

Hi. I am stumped when trying to figure out how to make it so the name in one column is just listed once with nulls values underneath until the next column name. I'm sorry if I don't explain this right.

I have this script:

SELECT NAME, SELLPRICE "Price Sold", COSTPRICE "Product Cost", ROUND( (SUM(SELLPRICE) - SUM(COSTPRICE))*100 / SUM(SELLPRICE)) as "% Profit"
FROM GS_SALES , GS_CUSTOMER
WHERE GS_CUSTOMER.CUSTID = GS_SALES.CUSTID
GROUP BY CITYID,NAME, SELLPRICE, COSTPRICE
ORDER BY CITYID;

which seems to give me the right results except that I want null values under the name field in the name colmn after a name is listed once and until it gets to the next unique name. Also if my script above looks off, please let me know.
Thank you :)

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-05-03 : 10:22:22

select name = case when seq = 1 then name else null end, [Price Sold], [Product Cost], [% Profit]
from
(
SELECT CITYID, NAME, SELLPRICE "Price Sold", COSTPRICE "Product Cost", ROUND( (SUM(SELLPRICE) - SUM(COSTPRICE))*100 / SUM(SELLPRICE)) as "% Profit"
,seq = ROW_NUMBER() over (partition by CITYID order by [Price Sold], [Product Cost], [% Profit])
FROM GS_SALES , GS_CUSTOMER
WHERE GS_CUSTOMER.CUSTID = GS_SALES.CUSTID
GROUP BY CITYID,NAME, SELLPRICE, COSTPRICE
) a
order by name, seq

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources