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
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
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
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
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
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
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
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
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