How can I turn this query to report back single line for different 'TEST" values. each one of the .test will have a differnt .value, but instead of writing 2 lines of data is it possible to just produce single record?
SELECT UpCastProduction.ItemID, UpCastProduction.ContainerID, UpCastProduction.GrossWeight, UpCastProduction.EquipmentID, UpCastItems.PackageCode, UpCastProduction.OnHold, UpCastProduction.Scrapped,
UpCastProduction.Uploaded,
UpCastProductionQuality.Value
FROM UpCastProduction INNER JOIN
UpCastItems ON UpCastProduction.ItemID = UpCastItems.ItemID INNER JOIN
UpCastProductionQuality ON UpCastProduction.ContainerID = UpCastProductionQuality.ContainerID
WHERE (UpCastProduction.OnHold = 0) AND (UpCastProduction.Scrapped = 0) AND (UpCastProduction.Uploaded = 0)
and
UpCastProductionQuality.Test in ('Diameter','Oxygen')
November 4, 2021 at 4:03 pm
Probably, but how can we write a solution for you if you don't provide DDL, sample data etc?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 4, 2021 at 7:52 pm
GROUP BY or DISTINCT or ROW_NUMBER() or RANK() or DENSE_RANK() - or maybe a CROSSTAB/PIVOT.
Any of those might be the right solution, but without sample data and expected results - really difficult to provide a solution.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
November 8, 2021 at 12:47 pm
CREATE TABLE [dbo].[UpCastProduction](
[ContainerID] [nvarchar](20) NOT NULL,
[WOID] [int] NULL,
[Destination] [nvarchar](20) NULL,
[GrossWeight] [float] NOT NULL,
[TareWeight] [float] NOT NULL,
[NetWeight] AS ([GrossWeight]-[TareWeight]),
[DateTime] [datetime] NULL,
[EquipmentID] [nvarchar](15) NOT NULL,
[EmployeeID] [nvarchar](15) NOT NULL,
[OnHold] [bit] NULL,
[Scrapped] [bit] NULL,
[Uploaded] [bit] NULL,
[ItemID] [nvarchar](20) NULL,
CONSTRAINT [PK_UpCastProduction] PRIMARY KEY CLUSTERED
(
[ContainerID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[UpCastProductionQuality](
[ContainerID] [nvarchar](20) NOT NULL,
[Test] [nvarchar](20) NOT NULL,
[Value] [nvarchar](50) NULL,
[PassFail] [bit] NULL,
CONSTRAINT [PK_UpCastProductionQuality] PRIMARY KEY CLUSTERED
(
[ContainerID] ASC,
[Test] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
November 10, 2021 at 1:45 pm
Data values for the tables.
Thanks.
INSERT INTO [dbo].[UpCastProduction]
([ContainerID]
,[GrossWeight]
,[TareWeight]
,[DateTime]
,[EquipmentID]
,[EmployeeID]
,[OnHold]
,[Scrapped]
,[Uploaded]
,[ItemID])
VALUES
(88883
,400
,50
,'11/10/2021'
,50
,777
,0
,0
,1
,'zzbf')
go
Insert into UpCastProductionQuality
([ContainerID]
,Test
,[Value]
,[passfail])
VALUES
(88883
,'OXYGEN'
,0.00011
,1)
go
Insert into UpCastProductionQuality
([ContainerID]
,Test
,[Value]
,[passfail])
VALUES
(88883
,'DIAMETER'
,8.004
,1)
November 10, 2021 at 3:50 pm
If this was 2017, I'd use STRING_AGG() to do this, but as we're in a 2016 forum, FOR XML PATH will have to do:
SELECT p.ContainerID
,p.GrossWeight
,p.TareWeight
,p.NetWeight
,p.DateTime
,p.EquipmentID
,p.EmployeeID
,p.OnHold
,p.Scrapped
,p.Uploaded
,p.ItemID
,Tests = COALESCE(STUFF((
SELECT ', ' + q.Test
FROM dbo.UpCastProductionQuality q
WHERE p.ContainerID = q.ContainerID
ORDER BY q.Test
FOR XML PATH('')
) ,1 ,2 ,'')
,'')
FROM dbo.UpCastProduction p;
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 10, 2021 at 6:25 pm
Can it report back the ,[Value] for each of the TESTs on the same line?
Thx.
November 10, 2021 at 6:38 pm
What is the expected result? How do you want the data returned?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
November 10, 2021 at 7:31 pm
I would like to return the ContainerID,ItemID,Grosswgt,equipID,Onhold,Scrapped,TEST and Value as a single line per
Container.
November 10, 2021 at 8:50 pm
Replace the SELECT ', ' row of my code with this.
SELECT CONCAT(', ', q.Test, ' - ', q.Value)Following the same logic, you should easily be able to get it looking the way you want.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 11, 2021 at 12:40 pm
Instead of concat could it show values in separate fields for the value of different tests?
example:
Oxygen_Value Diameter_Value
Thanks.
November 11, 2021 at 1:39 pm
Instead of concat could it show values in separate fields for the value of different tests?
example:
Oxygen_Value Diameter_Value
Thanks.
By "separate fields", do you mean in separate columns? No, that is not possible.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 11, 2021 at 2:20 pm
Instead of concat could it show values in separate fields for the value of different tests?
example:
Oxygen_Value Diameter_Value
Thanks.
Sounds like you want a dynamic pivot now. Like asked for earlier, if you show us what the expected results you actually want are, it'll make thin easier for us.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
November 11, 2021 at 5:42 pm
yes dynamic pivot...
ContainerID,ItemID,Grosswgt,equipID,Onhold,Scrapped,TEST, and TESTValue for the indiviual test
November 14, 2021 at 1:54 pm
any examples of the dynamic pivot?
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply