February 7, 2008 at 1:09 pm
Hi there,
I'm trying to force some output from a query to come out how I want and not being overly sucessful.
I have a @tbl
DECLARE @tblReturn TABLE
(
ChangeVARCHAR(50)
,Security_Identifier VARCHAR(50)
,Portfolio_CodeVARCHAR(255)
)
The data that is stored in the table might look like:
DowngradeABC8YXZ9Totota
DowngradeABC42FAN8Fixed
DowngradeABC12EAC0Toyota
DowngradeABC322AE3Fixed
NochangeABC824AS9Fixed
NochangeABC608D61Fixed
NochangeABC816AV1Fixed
Upgrade ABC824AS9Fixed
Upgrade ABC608D61Fixed
Upgrade ABC816AV1Fixed
I'm trying to get a 'cleaner' output so that the Change column only displays once per set like:
DowngradeABC8YXZ9Totota
NULL ABC42FAN8Fixed
NULL ABC12EAC0Toyota
NULL ABC322AE3Fixed
NochangeABC824AS9Fixed
NULL ABC608D61Fixed
NULL ABC816AV1Fixed
Upgrade ABC824AS9Fixed
NULL ABC608D61Fixed
NULL ABC816AV1Fixed
Now I've tried using CASE GROUPING to display the column or null but it keeps on adding nulls to other columns.
Thoughts on it? I can't seem to find a decent example that does quite what I want and if thats the case I'll live with it but I though I would check.
thanks,
Chris
February 7, 2008 at 1:34 pm
'Er you go:
select CASE WHEN ROW_NUMBER() OVER(PARTITION BY Change ORDER BY CHANGE) = 1 then Change else NULL end
, Security_Identifier
, Portfolio_code
from @tblReturn
Hope this helps,
Chad
February 7, 2008 at 1:48 pm
Chad's solution will only work on SQL Server 2005 and this appears to be a 2000 forum. Chris, which versio of SQL Server are you using?
Also, the result set that you are after could only be useful in a reporing scenario where you only want to list the Change column for the first Change value in a gruoping. This type of task is better handled by your reporting appliction. If you are using Crystal or Reporting Services, this is a pretty simple task. If this is not for a report, can you explain why you would want to show NULLs in place of the real Change values? This approach does not seem 'cleaner' by any means.
February 7, 2008 at 2:06 pm
GROUPING() "inserts" extra lines into your query with subtotal rollups, which is why that row has some many NULLs on it. So it never touches the detail records, which is what you are wanting to touch. So - that's not going to work. If you had an ID and some way to reliable link to the previous record, you MIGHT be able to conjure up a way to tell if this is the "first" row in a grouping sequence.
Personally - I'd go with John Rowan's solution: turn it into a report and use that capability to do this for you. Even Excel will do that for you.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 7, 2008 at 2:23 pm
The end result is going to be populated directly into an PDF. The user just wants to see the data, not have to manipulate it. It's part of an archaic VB6 app that I'm hoping to get rid of.
I might see about using Excel to manipulate it. I think things like crystal and reporting services are why I know little about some of the different SQL functions... running some wizard to get a nice totaled/subtotaled report is pretty trivial compared to writing a SQL statement to accomplish the same thing.
On the bright side, we're looking to migrate that particular server over to 2005 so it looks like I'll have all sorts of options.
Thanks for the replies!
--Chris
February 7, 2008 at 3:12 pm
Of course, if you want to do it in SQL Server 2000, there's still a way. This may not be the cleanest way to get there, but it works for the sample data you've given. I just wanted to make sure you were using 2000 before going through the effort of writing the code.
DECLARE @tblReturn TABLE (
Change VARCHAR(50)
,Security_Identifier VARCHAR(50)
,Portfolio_Code VARCHAR(255)
)
INSERT INTO @tblReturn
SELECT 'Downgrade', 'ABC8YXZ9', 'Totota' UNION ALL
SELECT 'Downgrade', 'ABC42FAN8', 'Fixed' UNION ALL
SELECT 'Downgrade', 'ABC12EAC0', 'Toyota' UNION ALL
SELECT 'Downgrade', 'ABC322AE3', 'Fixed' UNION ALL
SELECT 'Nochange', 'ABC824AS9', 'Fixed' UNION ALL
SELECT 'Nochange', 'ABC608D61', 'Fixed' UNION ALL
SELECT 'Nochange', 'ABC816AV1', 'Fixed' UNION ALL
SELECT 'Upgrade', 'ABC824AS9', 'Fixed' UNION ALL
SELECT 'Upgrade', 'ABC608D61', 'Fixed' UNION ALL
SELECT 'Upgrade', 'ABC816AV1', 'Fixed'
DECLARE @tblSortingTable TABLE (
RowNumber int IDENTITY(1,1),
Change varchar(50),
Security_Identifier varchar(50),
Portfolio_Code varchar(255)
)
INSERT INTO @tblSortingTable (Change, Security_Identifier, Portfolio_Code)
SELECT *
FROM @tblReturn
ORDER BY Change
SELECT COALESCE(t2.Change, '') as Change,
Security_Identifier,
Portfolio_Code
FROM @tblSortingTable t1
LEFT JOIN (
SELECT Change, MIN(RowNumber) as MinRow
FROM @tblSortingTable
GROUP BY Change
) t2
ON t1.Change = t2.Change AND t1.RowNumber = t2.MinRow
February 7, 2008 at 3:43 pm
Whoops! I got caught! Sorry Chris.
:hehe: MUST.... REDEEM.... PRIDE.... AT ANY COST!... :hehe:
If you have (or can create) an incrementing PK on the table, you could do something like what I have below. This is way ugly, and can probably be optimized quite a bit, but I already spent too much time on it just for the sake of redeeming myself.
select CASE
WHEN MAX(c.pk) = a.pk -1
OR (COUNT(c.pk) = 0 AND MIN(d.pk) = a.pk)
THEN a.change else null end as change
, a.Security_Identifier
, a.Portfolio_code
from @tblReturn a
--Find the "end" of this set of Change
LEFT JOIN @tblReturn b
ON a.change <> b.change and a.pk < b.pk
--Find the "beginning" of this set of Change
LEFT JOIN @tblReturn c
ON a.change <> c.change and c.pk < a.pk
--Determine our location within this set of change
LEFT JOIN @tblReturn d
ON a.change = d.change and d.pk ISNULL(c.pk, 0)
group by a.pk
, a.Change
, a.Security_Identifier
, a.Portfolio_code
I agree with John too - it will be (hopefully) much easier to do this using whatever is creating the PDF.
--Chad
February 7, 2008 at 4:48 pm
Just to clarify what I did, I think the really ugly code I wrote would handle repeating groups... John's code is much more elegant than mine if the categories do not repeat (e.g. all the Downgrade's are next to each other). But again, this is all academic because it would be much better to put it all in the app code anyway.
Best 'o luck (and thanks John for keeping me straight),
--Chad
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply