Extract de-deduplicated data from a set of results with Pivot Columns

Question

Post reply

Extract de-deduplicated data from a set of results with Pivot Columns

Weegee71

SSChasing Mays

Points: 604

April 24, 2023 at 2:36 pm

#4178662

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 :

Current ResultSet

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 :

Desired ResultSet

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, 8 months ago by Weegee71.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246988 More actions · Answer 1

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

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Weegee71 SSChasing Mays Points: 604 More actions · Answer 2

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 .