Extract de-deduplicated data from a set of results with Pivot Columns
-
I have been tasked with extracting de-deuplicated data from a set of records but also to provide a unique
pivot columns of a field [Channel_Name]
My data currently looks like this :
there are 2 things I want to do here:
1) Merge data with the same ReturnsItemID into 1 row, so the first 2 reccords with ReturnItemsIDÂ =1 are in 1 row as they have the same data
But as you can see i only have 1 record for ReturnItemsID = 2, so to retain that record as well
2) Pivot out the distinct values for Channel_Name in the dataset and display them as columns
So my desired result set would look like this :
so data for ReturnsItemID =1 , has been merged into 1 row and the 2nd row refers to ReturnItemsID =2
How can i produce this distinct result set with Pivot columns for unique values of [channel_name]?
this is my code used so far:
DROP TABLE IF EXISTS [#ReturnItems]
DROP TABLE IF EXISTS [#ReturnItemsCalibration_Channels]
DROP TABLE IF EXISTS [#ReturnItemsCalibration_Points]
CREATE TABLE [#ReturnItemsCalibration_Channels](
[ReturnItemsCalibration_ChannelsID] [int] IDENTITY(1,1) NOT NULL,
[ReturnItemsID] [int] NULL,
[RowNo] [bigint] NULL,
[Channel] [char](1) NULL,
[Channel_Name] [varchar](50) NULL,
[UNIT] [varchar](10) NULL,
[MIN] [varchar](20) NULL,
[MAX] [varchar](20) NULL,
[std_point_1] [varchar](20) NULL,
[std_point_2] [varchar](20) NULL,
[std_point_3] [varchar](20) NULL,
PRIMARY KEY CLUSTERED
(
[ReturnItemsCalibration_ChannelsID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [#ReturnItemsCalibration_Points](
[ReturnItemsCalibration_PointsID] [int] IDENTITY(1,1) NOT NULL,
[ReturnItemsCalibration_ChannelsID] [int] NOT NULL,
[Points] [int] NULL,
PRIMARY KEY CLUSTERED
(
[ReturnItemsCalibration_PointsID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [#ReturnItems](
[ReturnItemsID] [int] IDENTITY(1,1) NOT NULL,
[d3r_id] [int] NULL,
[Name] [nvarchar](30) NULL,
[Part_Number] [nvarchar](30) NULL,
[Serial_Number] [nvarchar](30) NULL,
[Calibration_Type] [nvarchar](30) NULL,
[Calibration_Notes] [nvarchar](30) NULL,
[Fault] [nvarchar](30) NULL,
[Return_Reason] [nvarchar](30) NULL,
[education_number] [nvarchar](30) NULL,
PRIMARY KEY CLUSTERED
(
[ReturnItemsID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [#ReturnItemsCalibration_Channels] ON
GO
INSERT [#ReturnItemsCalibration_Channels] ([ReturnItemsCalibration_ChannelsID], [ReturnItemsID], [RowNo], [Channel], [Channel_Name], [UNIT], [MIN], [MAX], [std_point_1], [std_point_2], [std_point_3]) VALUES (1, 1, 1, N'A', N'CO2', N'ppm', NULL, NULL, NULL, NULL, NULL)
GO
INSERT [#ReturnItemsCalibration_Channels] ([ReturnItemsCalibration_ChannelsID], [ReturnItemsID], [RowNo], [Channel], [Channel_Name], [UNIT], [MIN], [MAX], [std_point_1], [std_point_2], [std_point_3]) VALUES (2, 1, 1, N'B', N'Voltage', N'V DC', NULL, NULL, NULL, NULL, NULL)
GO
INSERT [#ReturnItemsCalibration_Channels] ([ReturnItemsCalibration_ChannelsID], [ReturnItemsID], [RowNo], [Channel], [Channel_Name], [UNIT], [MIN], [MAX], [std_point_1], [std_point_2], [std_point_3]) VALUES (3, 2, 1, N'A', N'CO2', N'ppm', NULL, NULL, NULL, NULL, NULL)
GO
INSERT [#ReturnItemsCalibration_Channels] ([ReturnItemsCalibration_ChannelsID], [ReturnItemsID], [RowNo], [Channel], [Channel_Name], [UNIT], [MIN], [MAX], [std_point_1], [std_point_2], [std_point_3]) VALUES (4, 4, 1, N'B', N'Voltage', N'V DC', NULL, NULL, NULL, NULL, NULL)
GO
SET IDENTITY_INSERT [#ReturnItemsCalibration_Channels] OFF
GO
SET IDENTITY_INSERT [#ReturnItemsCalibration_Points] ON
GO
INSERT [#ReturnItemsCalibration_Points] ([ReturnItemsCalibration_PointsID], [ReturnItemsCalibration_ChannelsID], [Points]) VALUES (1, 1, 20)
GO
INSERT [#ReturnItemsCalibration_Points] ([ReturnItemsCalibration_PointsID], [ReturnItemsCalibration_ChannelsID], [Points]) VALUES (2, 1, 80)
GO
INSERT [#ReturnItemsCalibration_Points] ([ReturnItemsCalibration_PointsID], [ReturnItemsCalibration_ChannelsID], [Points]) VALUES (3, 1, 6969)
GO
INSERT [#ReturnItemsCalibration_Points] ([ReturnItemsCalibration_PointsID], [ReturnItemsCalibration_ChannelsID], [Points]) VALUES (4, 1, 45373)
GO
INSERT [#ReturnItemsCalibration_Points] ([ReturnItemsCalibration_PointsID], [ReturnItemsCalibration_ChannelsID], [Points]) VALUES (5, 1, 23232)
GO
INSERT [#ReturnItemsCalibration_Points] ([ReturnItemsCalibration_PointsID], [ReturnItemsCalibration_ChannelsID], [Points]) VALUES (6, 2, 20)
GO
INSERT [#ReturnItemsCalibration_Points] ([ReturnItemsCalibration_PointsID], [ReturnItemsCalibration_ChannelsID], [Points]) VALUES (7, 2, 80)
GO
INSERT [#ReturnItemsCalibration_Points] ([ReturnItemsCalibration_PointsID], [ReturnItemsCalibration_ChannelsID], [Points]) VALUES (8, 2, 100)
GO
INSERT [#ReturnItemsCalibration_Points] ([ReturnItemsCalibration_PointsID], [ReturnItemsCalibration_ChannelsID], [Points]) VALUES (9, 2, 150)
GO
INSERT [#ReturnItemsCalibration_Points] ([ReturnItemsCalibration_PointsID], [ReturnItemsCalibration_ChannelsID], [Points]) VALUES (10, 2, 220)
GO
INSERT [#ReturnItemsCalibration_Points] ([ReturnItemsCalibration_PointsID], [ReturnItemsCalibration_ChannelsID], [Points]) VALUES (11, 2, 37373)
GO
INSERT [#ReturnItemsCalibration_Points] ([ReturnItemsCalibration_PointsID], [ReturnItemsCalibration_ChannelsID], [Points]) VALUES (12, 1, 100)
GO
INSERT [#ReturnItemsCalibration_Points] ([ReturnItemsCalibration_PointsID], [ReturnItemsCalibration_ChannelsID], [Points]) VALUES (13, 1, 200)
GO
SET IDENTITY_INSERT [#ReturnItemsCalibration_Points] OFF
GO
SET IDENTITY_INSERT [#ReturnItems] ON
GO
INSERT [#ReturnItems] ([ReturnItemsID], [d3r_id], [Name], [Part_Number], [Serial_Number], [Calibration_Type], [Calibration_Notes], [Fault], [Return_Reason], [education_number]) VALUES (1, 4929, N'TGE-0010', N'TGE-0010', N'10', N'Standard', N'Example calibration notes ', NULL, N'', NULL)
GO
INSERT [#ReturnItems] ([ReturnItemsID], [d3r_id], [Name], [Part_Number], [Serial_Number], [Calibration_Type], [Calibration_Notes], [Fault], [Return_Reason], [education_number]) VALUES (2, 4929, N'TGP-4703', N'TGP-4703', N'890716', N'Custom', N'', NULL, N'', NULL)
GO
INSERT [#ReturnItems] ([ReturnItemsID], [d3r_id], [Name], [Part_Number], [Serial_Number], [Calibration_Type], [Calibration_Notes], [Fault], [Return_Reason], [education_number]) VALUES (3, 4925, N'TGE-0010', N'TGE-0010', N'10', N'Standard', N'Example calibration notes ', NULL, N'', NULL)
GO
INSERT [#ReturnItems] ([ReturnItemsID], [d3r_id], [Name], [Part_Number], [Serial_Number], [Calibration_Type], [Calibration_Notes], [Fault], [Return_Reason], [education_number]) VALUES (4, 4925, N'TGP-4703', N'TGP-4703', N'890716', N'Custom', N'', NULL, N'', NULL)
GO
INSERT [#ReturnItems] ([ReturnItemsID], [d3r_id], [Name], [Part_Number], [Serial_Number], [Calibration_Type], [Calibration_Notes], [Fault], [Return_Reason], [education_number]) VALUES (5, 4928, N'TGE-0010', N'TGE-0010', N'10', NULL, NULL, NULL, N'Example reason for return for ', NULL)
GO
INSERT [#ReturnItems] ([ReturnItemsID], [d3r_id], [Name], [Part_Number], [Serial_Number], [Calibration_Type], [Calibration_Notes], [Fault], [Return_Reason], [education_number]) VALUES (6, 4927, N'TGE-0010', N'TGE-0010', N'10', NULL, NULL, NULL, N'', N'Example education number')
GO
INSERT [#ReturnItems] ([ReturnItemsID], [d3r_id], [Name], [Part_Number], [Serial_Number], [Calibration_Type], [Calibration_Notes], [Fault], [Return_Reason], [education_number]) VALUES (7, 4924, N'TGE-0010', N'TGE-0010', N'10', NULL, NULL, NULL, N'Example return reason', NULL)
GO
SET IDENTITY_INSERT [#ReturnItems] OFF
GO
-- Atempted query
SELECT
ri.[d3r_id],
ri.[ReturnItemsID],
ricc.ReturnItemsCalibration_ChannelsID,
ri.[Serial_Number],
ri.[Part_Number] as [UnitType],
ri.[Calibration_Type],
ri.[Calibration_Notes],
ricc.Channel_Name
FROM
[#ReturnItems]ri
left JOIN #ReturnItemsCalibration_Channels ricc ON ri.ReturnItemsID = ricc.ReturnItemsID
WHERE
ri.d3r_id =CASE WHEN IsNull(4929,0) = 0 or 4929 = 0
Then ri.d3r_id
ELSE
4929
END
order by ri.[ReturnItemsID]- This topic was modified 1 year, 7 months ago by Weegee71.
-
This returns your desired results, but is it what you want?
DECLARE @SQL1 NVARCHAR(MAX)
= N'SELECT ri.Serial_Number
,UnitType = ri.Part_Number
,Calibration_Type = MAX (ri.Calibration_Type)
,Calibration_Notes = MAX (ri.Calibration_Notes)
,ReplaceMe
FROM #ReturnItems ri
GROUP BY ri.Serial_Number
,ri.Part_Number';
DECLARE @SQL2 NVARCHAR(MAX);
WITH items
AS (SELECT DISTINCT
Channel_Name = QUOTENAME (ricc.Channel_Name)
FROM #ReturnItemsCalibration_Channels ricc)
SELECT @SQL2 = STRING_AGG (CONCAT (items.Channel_Name, ' = '''''), ',')
FROM items;
DECLARE @SQL3 NVARCHAR(MAX) = REPLACE (@SQL1, 'ReplaceMe', @SQL2);
EXEC sys.sp_executesql @SQL3;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 - Phil Parkin wrote:
This returns your desired results, but is it what you want?
DECLARE @SQL1 NVARCHAR(MAX)
= N'SELECT ri.Serial_Number
,UnitType = ri.Part_Number
,Calibration_Type = MAX (ri.Calibration_Type)
,Calibration_Notes = MAX (ri.Calibration_Notes)
,ReplaceMe
FROM #ReturnItems ri
GROUP BY ri.Serial_Number
,ri.Part_Number';
DECLARE @SQL2 NVARCHAR(MAX);
WITH items
AS (SELECT DISTINCT
Channel_Name = QUOTENAME (ricc.Channel_Name)
FROM #ReturnItemsCalibration_Channels ricc)
SELECT @SQL2 = STRING_AGG (CONCAT (items.Channel_Name, ' = '''''), ',')
FROM items;
DECLARE @SQL3 NVARCHAR(MAX) = REPLACE (@SQL1, 'ReplaceMe', @SQL2);
EXEC sys.sp_executesql @SQL3;Hey Phil
I was thinking down the road of doing a max on the results.. but couldnt
get my head round how this works.
But thats exactly what i wanted.
Thank you very much .
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply