January 27, 2016 at 11:45 am
I'm joining 2 tables and displaying data for InvNumber, InvAmount and JobNumber. I only need to display InvNumber and InvAmount in the first row. The Invoice has multiple Job numbers which should be displayed.
DECLARE @Date datetime;
SET @Date = GETDATE();
DECLARE @TEST_DATA TABLE
(
DT_ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED
,InvNumber VARCHAR(10) NOT NULL
,InvAmount VARCHAR(10) NOT NULL
,JobNumber VARCHAR(10) NOT NULL
);
INSERT INTO @TEST_DATA (InvNumber, InvAmount,JobNumber)
VALUES
('70001', '12056','J65448')
,('70001', '12056','J12566')
,('70001', '12056','J35222')
,('70001', '12056','J45222')
,('70001', '12056','456855')
,('70001', '12056','J55254')
;
SELECT
J.DT_ID
,InvNumber
,InvAmount
,JobNumber
FROM @TEST_DATA AS J
January 27, 2016 at 2:37 pm
That should be done in the front end, otherwise you risk the possibility of reordering the rows and losing any way of defining which job relates to which invoice.
Here's the basic code to do it, as well as an alternative that might work better in some cases.
SELECT
CASE WHEN ROW_NUMBER() OVER(PARTITION BY InvNumber, InvAmount ORDER BY JobNumber) = 1
THEN J.InvNumber ELSE '' END AS InvNumber
,CASE WHEN ROW_NUMBER() OVER(PARTITION BY InvNumber, InvAmount ORDER BY JobNumber) = 1
THEN J.InvAmount ELSE '' END AS InvAmount
,JobNumber
FROM @TEST_DATA AS J;
SELECT InvNumber,
InvAmount,
STUFF( (SELECT CHAR(13) + JobNumber
FROM @TEST_DATA i
WHERE i.InvNumber = j.InvNumber
AND i.InvAmount = j.InvAmount
FOR XML PATH(''),TYPE).value('./text()[1]','varchar(max)'), 1, 1, '')
FROM @TEST_DATA AS J
GROUP BY InvNumber,
InvAmount;
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply